More Custom Reports in KoalaBrain Using Excel and VBA

Following my previous article on creating custom reports for KoalaBrain EPOS in Excel using VBA script, a client requested some additional reports. I’m including them here as additional examples of what can be done with KoalaBrain VBA reporting.

This report pulls sale details for any uncategorized sale rows, and shows any product name, description, or sale note attached to them, along with the payment method used for that sale and the tax rate applied.

    Sub doReport()

        'Define the timezone we're working in - used when formatting the date
        Dim timezoneOffset As Double: timezoneOffset = 9.5

        'Stop excel from trying to draw the screen while data is being moved about to speed things up
        Application.ScreenUpdating = False

        'Delete old report sheet if it exists
        If koalaFunctions.Custom_WorksheetExists("Items") Then
            Call koalaFunctions.Custom_DeleteSheet(ActiveWorkbook.Sheets("Items"))
        End If

        'Copy Sales data to new sheet.
        If koalaFunctions.Custom_WorksheetExists("kbSalesData") Then
            Call koalaFunctions.Custom_CopyRenameSheet(ActiveWorkbook.Sheets("kbSalesData"), "Items")
        Else
            MsgBox "No Data to work with - please export this template from KoalaBrain before using"
            Exit Sub
        End If

        'Deduplicate the sale rows by saleRowsUuid  - As rows may be repeated if they have multiple tax or discount rows (so that the additional tax/discount rows can be included) these need to be trimmed.  We don't need the additional rows as we aren't calculating any totals in this report
        'UUIDs are unique strings which identify a particular record in the KoalaBrain database
        Call koalaFunctions.Custom_RemoveDuplicates(ActiveWorkbook.Sheets("Items"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "saleRows.uuid"))

        'Sort by Sale uuid to ensure sale rows from the same sale are grouped
        Call koalaFunctions.Custom_SortSheetByColumn(ActiveWorkbook.Sheets("Items"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "uuid"), "Ascending")

        'Remove rows which have a category name
        Call koalaFunctions.Utility_RemoveRowsNotMatchingColumnValue(ActiveWorkbook.Sheets("Items"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "saleRows.categoryName"), "")

        'Rename the columns which will be used in the final report
        ActiveWorkbook.Sheets("Items").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "shortId")).value = "Sale Short ID"
        ActiveWorkbook.Sheets("Items").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "completionDate")).value = "Completed"
        ActiveWorkbook.Sheets("Items").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "saleRows.quantity")).value = "Quantity"
        ActiveWorkbook.Sheets("Items").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "saleRows.productName")).value = "Product"
        ActiveWorkbook.Sheets("Items").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "saleRows.productDescription")).value = "Description"
        ActiveWorkbook.Sheets("Items").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "note")).value = "Sale Note"
        ActiveWorkbook.Sheets("Items").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "contactName")).value = "Contact"
        ActiveWorkbook.Sheets("Items").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "saleRows.productPrice")).value = "Price"
        ActiveWorkbook.Sheets("Items").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "saleRows.saleTaxRows.taxClassName")).value = "Tax Rate"

        'Remove unneccesary columns
        Call koalaFunctions.Utility_RemoveColumnsNotMatchingHeaders(ActiveWorkbook.Sheets("Items"), Array("uuid", "Sale Short ID", "Completed", "Quantity", "Product", "Description", "Sale Note", "Contact", "Price", "Tax Rate"))

        'Find a payment for the sale row so it's method can be included in it's own col

        ActiveWorkbook.Sheets("Items").Cells(1, 11).value = "Payment Method"

        Dim i As Long
        Dim length As Long
        i = 2
        length = koalaFunctions.Custom_GetLastRow(ActiveWorkbook.Sheets("Items"))
        Dim temp As Long
        Dim findMe As String
        Do While i <= length

            findMe = ActiveWorkbook.Sheets("Items").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "uuid")).value

            If koalaFunctions.Utility_ValueExistsInColumn(ActiveWorkbook.Sheets("kbPaymentsData"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("kbPaymentsData"), "saleUuid"), findMe) Then

                temp = koalaFunctions.Utility_FindFirstRowWithColumnValue(ActiveWorkbook.Sheets("kbPaymentsData"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("kbPaymentsData"), "saleUuid"), findMe)

                ActiveWorkbook.Sheets("Items").Cells(i, 11).value = ActiveWorkbook.Sheets("kbPaymentsData").Cells(temp, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("kbPaymentsData"), "paymentMethodName")).value

            Else

                'no payment, do nothing

            End If

        'next
            i = i + 1

        Loop

        'Remove more unneccesary columns
        Call koalaFunctions.Utility_RemoveColumnsNotMatchingHeaders(ActiveWorkbook.Sheets("Items"), Array("Sale Short ID", "Completed", "Quantity", "Product", "Description", "Sale Note", "Contact", "Payment Method", "Price", "Tax Rate"))

        'Format the Date Due Column
        Call dateFormat(ActiveWorkbook.Sheets("Items"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Items"), "Completed"), timezoneOffset)

        'Resize cols.  All formatting will be lost when KoalaBrain inserts it's data, so any formatting must be added via VBA code
        ActiveWorkbook.Sheets("Items").Range("A:B").ColumnWidth = 16
        ActiveWorkbook.Sheets("Items").Range("C:D").ColumnWidth = 30
        ActiveWorkbook.Sheets("Items").Range("E:E").ColumnWidth = 8
        ActiveWorkbook.Sheets("Items").Range("F:G").ColumnWidth = 30
        ActiveWorkbook.Sheets("Items").Range("H:H").ColumnWidth = 8
        ActiveWorkbook.Sheets("Items").Range("I:I").ColumnWidth = 16
        ActiveWorkbook.Sheets("Items").Range("J:J").ColumnWidth = 16

        'Add a heading to the sheet including a report name, and the date range reported.  Date range is retrieved from the sheet 'kbReportInfo'
        ActiveWorkbook.Sheets("Items").rows(1).EntireRow.Insert
        ActiveWorkbook.Sheets("Items").rows(1).EntireRow.Insert
        ActiveWorkbook.Sheets("Items").rows(1).EntireRow.Insert
        ActiveWorkbook.Sheets("Items").Range("A1").value = "Uncategorized Sales Report"
        ActiveWorkbook.Sheets("Items").Range("A2:C2").NumberFormat = "dd/mmm/yyyy"
        ActiveWorkbook.Sheets("Items").Range("A2").value = DateValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("K2").value, 1, 10)) + TimeValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("K2").value, 12, 8)) + timezoneOffset / 24
        ActiveWorkbook.Sheets("Items").Range("B2").value = "to"
        ActiveWorkbook.Sheets("Items").Range("C2").value = DateValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("L2").value, 1, 10)) + TimeValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("L2").value, 12, 8)) + timezoneOffset / 24
        With ActiveWorkbook.Sheets("Items").Range("A1").Font
            .Size = 16
            .Bold = True
        End With
        With ActiveWorkbook.Sheets("Items").Range("A1:J4").Font
            .Bold = True
        End With

        'currency format columns
        ActiveWorkbook.Sheets("Items").Range("H:H").NumberFormat = "$#,##0.00"

        'Print size
        With Sheets("Items").PageSetup
            .FitToPagesWide = 1
            .FitToPagesTall = False
        End With

        'Set tab colour green
        ActiveWorkbook.Sheets("Items").Tab.ColorIndex = 4

        'Done! Let the user know, and re-enable screen updating
        MsgBox "Report Build Complete"
        Application.ScreenUpdating = True
    End Sub

    'Formats a cell to a more readable date format
    Sub dateFormat(targetSheet As Worksheet, columnReference As Long, timezoneOffset As Double)
        Dim temp As Variant
        For i = 2 To koalaFunctions.Custom_GetLastRow(targetSheet) 'i = 2 to skip header row
            temp = targetSheet.Cells(i, columnReference).value
            If Len(temp) > 0 Then 'don't try and format blank cells
                targetSheet.Cells(i, columnReference).NumberFormat = "dd/mmm/yyyy"
                targetSheet.Cells(i, columnReference).value = DateValue(Mid(temp, 1, 10)) + TimeValue(Mid(temp, 12, 8)) + timezoneOffset / 24
            End If
        Next i
    End Sub

This next report lists any sales where the completion date is more than 7 days away from the initial sale date, and displays that difference in a column.

    Sub doReport()

        'Define the timezone we're working in - used when formatting the date
        Dim timezoneOffset As Double: timezoneOffset = 9.5

        'Stop excel from trying to draw the screen while data is being moved about to speed things up
        Application.ScreenUpdating = False

        'Delete old report sheet if it exists
        If koalaFunctions.Custom_WorksheetExists("Sales") Then
            Call koalaFunctions.Custom_DeleteSheet(ActiveWorkbook.Sheets("Sales"))
        End If

        'Copy Sales data to new sheet.
        If koalaFunctions.Custom_WorksheetExists("kbSalesData") Then
            Call koalaFunctions.Custom_CopyRenameSheet(ActiveWorkbook.Sheets("kbSalesData"), "Sales")
        Else
            MsgBox "No Data to work with - please export this template from KoalaBrain before using"
            Exit Sub
        End If

        'Deduplicate the sale rows by sale uuid
        'UUIDs are unique strings which identify a particular record in the KoalaBrain database
        Call koalaFunctions.Custom_RemoveDuplicates(ActiveWorkbook.Sheets("Sales"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "uuid"))

        'Rename the columns which will be used in the final report
        ActiveWorkbook.Sheets("Sales").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "shortId")).value = "Sale Short ID"
        ActiveWorkbook.Sheets("Sales").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "completionDate")).value = "Completion Date"
        ActiveWorkbook.Sheets("Sales").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "saleDate")).value = "Sale Date"
        ActiveWorkbook.Sheets("Sales").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "note")).value = "Sale Note"
        ActiveWorkbook.Sheets("Sales").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "contactName")).value = "Contact"
        ActiveWorkbook.Sheets("Sales").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "layby")).value = "Layby"
        ActiveWorkbook.Sheets("Sales").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "order")).value = "Order"

        'Remove unneccesary columns
        Call koalaFunctions.Utility_RemoveColumnsNotMatchingHeaders(ActiveWorkbook.Sheets("Sales"), Array("Sale Short ID", "Completion Date", "Sale Date", "Sale Note", "Contact", "Layby", "Order"))

        'Format the Date Columns
        Call dateFormat(ActiveWorkbook.Sheets("Sales"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "Sale Date"), timezoneOffset)
        Call dateFormat(ActiveWorkbook.Sheets("Sales"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "Completion Date"), timezoneOffset)

        'Calculate differences
        ActiveWorkbook.Sheets("Sales").Cells(1, 8).value = "Days Difference"

        Dim i As Long
        Dim length As Long
        i = 2
        length = koalaFunctions.Custom_GetLastRow(ActiveWorkbook.Sheets("Sales"))
        Do While i <= length

            ActiveWorkbook.Sheets("Sales").Cells(i, 8).value = _
            DateDiff("d", ActiveWorkbook.Sheets("Sales").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "Sale Date")).value, _
            ActiveWorkbook.Sheets("Sales").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Sales"), "Completion Date")).value)

            'next
            i = i + 1

        Loop

        'Delete where difference < 7 days - starts at bottom and loops up instead of down so that deleting doesn't mess up row indexes for the next deletion
        'https://stackoverflow.com/questions/17814501/delete-entire-row-if-value-is-greater-than-or-less-than
        For j = ActiveWorkbook.Sheets("Sales").Range("H" & ActiveWorkbook.Sheets("Sales").rows.count).End(xlUp).row To 2 Step -1 'To 2 = to second row ie omit header
            If (ActiveWorkbook.Sheets("Sales").Range("H" & j).value < 7) Then
                ActiveWorkbook.Sheets("Sales").Range("H" & j).EntireRow.Delete
            End If
        Next j

        'Rearrange cols
        ActiveWorkbook.Sheets("Sales").Columns("B").Cut
        ActiveWorkbook.Sheets("Sales").Columns("H").Insert Shift:=xlToRight
        ActiveWorkbook.Sheets("Sales").Columns("D").Cut
        ActiveWorkbook.Sheets("Sales").Columns("H").Insert Shift:=xlToRight

        'Layby/Order cols - change 1 to YES, 0 to NO
        Columns("B").Replace What:="1", _
            Replacement:="YES", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
        Columns("C").Replace What:="1", _
            Replacement:="YES", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
        Columns("B").Replace What:="0", _
            Replacement:="", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
        Columns("C").Replace What:="0", _
            Replacement:="", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False

        'Resize cols.  All formatting will be lost when KoalaBrain inserts it's data, so any formatting must be added via VBA code
        ActiveWorkbook.Sheets("Sales").Range("A:H").ColumnWidth = 16

        'Add a heading to the sheet including a report name, and the date range reported.  Date range is retrieved from the sheet 'kbReportInfo'
        ActiveWorkbook.Sheets("Sales").rows(1).EntireRow.Insert
        ActiveWorkbook.Sheets("Sales").rows(1).EntireRow.Insert
        ActiveWorkbook.Sheets("Sales").rows(1).EntireRow.Insert
        ActiveWorkbook.Sheets("Sales").Range("A1").value = "Sales with a date/completion more than 7 days difference"
        ActiveWorkbook.Sheets("Sales").Range("A2:C2").NumberFormat = "dd/mmm/yyyy"
        ActiveWorkbook.Sheets("Sales").Range("A2").value = DateValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("K2").value, 1, 10)) + TimeValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("K2").value, 12, 8)) + timezoneOffset / 24
        ActiveWorkbook.Sheets("Sales").Range("B2").value = "to"
        ActiveWorkbook.Sheets("Sales").Range("C2").value = DateValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("L2").value, 1, 10)) + TimeValue(Mid(ActiveWorkbook.Sheets("kbReportInfo").Range("L2").value, 12, 8)) + timezoneOffset / 24
        With ActiveWorkbook.Sheets("Sales").Range("A1").Font
            .Size = 16
            .Bold = True
        End With
        With ActiveWorkbook.Sheets("Sales").Range("A1:H4").Font
            .Bold = True
        End With

        'Print size
        With Sheets("Sales").PageSetup
            .FitToPagesWide = 1
            .FitToPagesTall = False
        End With

        'Set tab colour green
        ActiveWorkbook.Sheets("Sales").Tab.ColorIndex = 4

        'Done! Let the user know, and re-enable screen updating
        MsgBox "Report Build Complete"
        Application.ScreenUpdating = True
    End Sub

    'Formats a cell to a more readable date format
    Sub dateFormat(targetSheet As Worksheet, columnReference As Long, timezoneOffset As Double)
        Dim temp As Variant
        For i = 2 To koalaFunctions.Custom_GetLastRow(targetSheet) 'i = 2 to skip header row
            temp = targetSheet.Cells(i, columnReference).value
            If Len(temp) > 0 Then 'don't try and format blank cells
                targetSheet.Cells(i, columnReference).NumberFormat = "dd/mmm/yyyy"
                targetSheet.Cells(i, columnReference).value = DateValue(Mid(temp, 1, 10)) + TimeValue(Mid(temp, 12, 8)) + timezoneOffset / 24
            End If
        Next i
    End Sub

These reports only took a couple of hours each to develop and test, and have proved valuable to the client, saving time on searching and identifying transactions and making the managemnt of business operations easier.