Creating Custom Reports in KoalaBrain using Excel and VBA

I created KoalaBrain in part due to a client with unique reporting requirements. Many business and finance types are proficient with Microsoft Excel, and scripting it in Visual Basic for Application, so it made sense to base custom reports around this.

VBA Reports in KoalaBrain dumps sales and payment data into different sheets in a spreadsheet and provides it as an excel .xlsm file. The user can upload an optional template into KoalaBrain, which will add the sheets to that template and return it including the data and any macros which were in the template. This makes for simple, easily produced custom reports at a single click (or uploaded on a schedule for remote viewing if you use KoalaBrain Online).

Here’s how to create a simple report which displays orders which contain items from a specified category, and which where created in a specified time period. It’ll be used to print off order sheets to be sent to the manufacturer of the items in that category.

What you’ll need

• KoalaBrain, installed and configured. Heres a link to a 10 minute quick start guide
• Some Sales entered as Orders
• Microsoft Excel – versions from 2013 onwards should work
• The KoalaBrain Excel Helper Library – Download koalaVbaFunctions.bas from the ‘Attachments’ section at this link. This is a free library containing excel functions for calculating taxes and totals with KoalaBrain’s data, as well as helper functions for filtering, sorting, and manipulating data in Excel
• A basic understanding of how Excel macros work, and about 15 minutes

Steps

• Create a new Excel file in the .xlsm format – this will be the new report template
  • Open Excel to a blank worksheet
  • Rename ‘Sheet1’ to ‘Orders’ – our template VBA code will reference this sheet by name
  • Click ‘File’ -> ‘Save As’
  • Select ‘Excel Macro-Enabled Workbook (.xlsm)’ from the ‘File Format’ Dropdown
  • Enter an appropriate file name and save
• Import vba lib into the report
  • Enable the developer tab in the Excel Toolbar
  • Download the file koalaVbaFunctions.bas
  • Click on the ‘Developer’ tab in Excel and then click on ‘Visual Basic’
  • Click ‘File’ -> ‘Import File’ and select the downloaded koalaVbaFunctions.bas file
  • The file will have been imported as ‘Module1’. Click on it in the sidebar, and rename it to ‘koalaFunctions’ in the ‘Properties’ box below the project explorer
• Add a reporting module VBA code
  • Click on ‘Insert’ -> ‘Module’
  • The new module will have been created as ‘Module1’. Click on it in the sidebar, and rename it to ‘orderReport’ in the ‘Properties’ box below the project explorer
  • Double-click on ‘orderReport’ in the modules list to bring up the editor. Copy and paste the following code into the editor

    Sub doReport()
        'Define an array of the categories we want to show the orders for
        Dim categories As Variant: categories = Array("Widgets", "Gizmos")

        '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 Orders report sheet if it exists
        If koalaFunctions.Custom_WorksheetExists("Orders") Then
            Call koalaFunctions.Custom_DeleteSheet(ActiveWorkbook.Sheets("Orders"))
        End If

        'Copy Sales data to new 'Orders' sheet.  We're copying from the sheet which includes incomplete sales as orders will not yet have been completed.  Bail out if the workbook hasn't been populated with KoalaBrain data
        If koalaFunctions.Custom_WorksheetExists("kbSalesIncIncompleteData") Then
            Call koalaFunctions.Custom_CopyRenameSheet(ActiveWorkbook.Sheets("kbSalesIncIncompleteData"), "Orders")
        Else
            MsgBox "No Data to work with - please export this template from KoalaBrain before using"
            Exit Sub
        End If

        'Remove sales which are not Orders from the copied data
        'As a habit I avoid using ActiveSheet and similar functions as I find things get confusing when working with large workbooks with many sheets.  Instead I explicitely reference the sheet each time.  It may look more cluttered, but it helps reduce mistakes
        Call koalaFunctions.Utility_RemoveRowsNotMatchingColumnValue(ActiveWorkbook.Sheets("Orders"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "order"), 1)

        'Remove Sale rows where the categoryName is not in the array of specified categories
        Dim i As Long: i = 2  'The variable we'll increment in the loop to track the current row number.  Start at row 2 as row 1 is the column headings
        Dim length As Long: length = koalaFunctions.Custom_GetLastRow(ActiveWorkbook.Sheets("Orders"))  'Number of rows in the sheet
        Do While i <= length
            If Not koalaFunctions.Utility_IsInArray(ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "saleRows.categoryName")).value, categories) Then
                ActiveWorkbook.Sheets("Orders").rows(i).EntireRow.Delete
                i = i - 1 'As we've deleted a row, we need to adjust the loop position
                length = length - 1 ' ...and the loop length
            End If
            i = i + 1 'Go to the next row
        Loop

        '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, just the contents of the order
        'UUIDs are unique strings which identify a particular record in the KoalaBrain database
        Call koalaFunctions.Custom_RemoveDuplicates(ActiveWorkbook.Sheets("Orders"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "saleRows.uuid"))

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

        'Rename the columns which will be used in the final report
        ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "shortId")).value = "Sale Short ID"
        ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "dueByDate")).value = "Due Date"
        ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "saleRows.quantity")).value = "Quantity"
        ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "saleRows.productName")).value = "Product"
        ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "note")).value = "Note"
        ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "contactName")).value = "Contact"
        ActiveWorkbook.Sheets("Orders").Cells(1, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "contactPhone")).value = "Phone"

        'Remove unneccesary columns
        Call koalaFunctions.Utility_RemoveColumnsNotMatchingHeaders(ActiveWorkbook.Sheets("Orders"), Array("uuid", "Sale Short ID", "Due Date", "Quantity", "Product", "Note", "Contact", "Phone"))

        'Format the Date Due Column
        Call dateFormat(ActiveWorkbook.Sheets("Orders"), koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Due Date"), 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("Orders").Range("B:B").ColumnWidth = 35
        ActiveWorkbook.Sheets("Orders").Range("C:E").ColumnWidth = 12

        'Loop through the Sale Rows and perform formatting - highlighting and adding dividers between items in different sales.  We know the sale has changed when the Sale UUID column is not the same as the last.
        i = 2 'Reset loop values
        length = koalaFunctions.Custom_GetLastRow(ActiveWorkbook.Sheets("Orders")) 'Reset loop values
        Dim lastSaleUuid As String 'Keeping track of the last sale in the lopo by UUID.  Column A will hold the sale UUID after the above column deletions, and be removed at the end of this section, so treat column B as the start of the real formatted report
        Do While i <= length
            'Merge quantity and product Name Cells
            ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Product")).value = ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Quantity")).value & "x " & ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Product")).value
            If lastSaleUuid = ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "uuid")).value Then
                '  Remove repeated information like the sale short ID and note to make things look nicer.
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Sale Short ID")).Clear
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Due Date")).Clear
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Note")).Clear
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Contact")).Clear
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Phone")).Clear
                'Move the Quantity, Product columns to the left.  As the number of columns is now < 10 I've started referring to them by column letter rather than using a lookup function
                ActiveWorkbook.Sheets("Orders").Range("B" & i & ":G" & i).Delete Shift:=xlShiftToLeft
            Else
                'Duplicate the first row in a sale - the first will become the header with a grey background
                ActiveWorkbook.Sheets("Orders").Cells(i, 1).EntireRow.Copy
                ActiveWorkbook.Sheets("Orders").Cells(i, 1).EntireRow.Insert Shift:=xlDown
                ActiveWorkbook.Sheets("Orders").Cells(i, 1).EntireRow.Interior.Color = RGB(225, 225, 225)
                'Add another row below and move the note into it - it's preformatted text so it needs room
                ActiveWorkbook.Sheets("Orders").Cells(i + 1, 1).EntireRow.Insert Shift:=xlDown
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Note")).Cut ActiveWorkbook.Sheets("Orders").Cells(i + 1, 2) ' Pasting into the row BELOW as the loop is still in the position of the first row.  Pasting into the second column as the first (A) is the UUID which is only used for caculations and later removed
                ActiveWorkbook.Sheets("Orders").Range("B" & i + 1).value = "Note: " & vbNewLine & ActiveWorkbook.Sheets("Orders").Range("B" & i + 1).value
                ActiveWorkbook.Sheets("Orders").Range("B" & i + 1).WrapText = True  ' Wrap note text
                ActiveWorkbook.Sheets("Orders").rows(i + 1).EntireRow.AutoFit ' Fit cell to note text height
                'With duplicating and moving now complete, make the first row of the order details bold
                With ActiveWorkbook.Sheets("Orders").Cells(i, 1).EntireRow.Font
                .Bold = True
                End With
                i = i + 2 'We've added rows, update the loop position
                length = length + 2 'And the loop length
                'The duplicated row will have it's repeated information removed and it's Quantity, Product columns moved to the left, as above
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Sale Short ID")).Clear
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Due Date")).Clear
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Note")).Clear
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Contact")).Clear
                ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "Phone")).Clear
                ActiveWorkbook.Sheets("Orders").Range("B" & i & ":G" & i).Delete Shift:=xlShiftToLeft
            End If
            lastSaleUuid = ActiveWorkbook.Sheets("Orders").Cells(i, koalaFunctions.Utility_GetColumnByName(ActiveWorkbook.Sheets("Orders"), "uuid")).value
            i = i + 1 'Go to the next row
        Loop

        'Remove more unneccesary columns
        Call koalaFunctions.Utility_RemoveColumnsNotMatchingHeaders(ActiveWorkbook.Sheets("Orders"), Array("Sale Short ID", "Due Date", "Contact", "Phone"))

        '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("Orders").rows(1).EntireRow.Insert
        ActiveWorkbook.Sheets("Orders").rows(1).EntireRow.Insert
        ActiveWorkbook.Sheets("Orders").rows(1).EntireRow.Insert
        ActiveWorkbook.Sheets("Orders").Range("A1").value = "Orders Report"
        ActiveWorkbook.Sheets("Orders").Range("A2:C2").NumberFormat = "dd/mmm/yyyy"
        ActiveWorkbook.Sheets("Orders").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("Orders").Range("B2").value = "to"
        ActiveWorkbook.Sheets("Orders").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("Orders").Range("A1").Font
            .Size = 16
            .Bold = True
        End With
        With ActiveWorkbook.Sheets("Orders").Range("A1:E4").Font
            .Bold = True
        End With

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

        '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

• There’s room for refinement but it’s a custom report in a bit over 100 lines of code, including styling. Not bad! The code is commented and will explain what each step does, be sure to read through it 🙂
  • We want this function to run automatically when the workbook opens. Double click the ‘ThisWorkbook’ module in the project explorer
  • Copy and paste the following code into the editor

Private Sub Workbook_Open()
    Call orderReport.doReport
End Sub

• Close the Visual Basic Editor and return to Excel
  • Save your work and quit Excel – Our template is ready for use
• Create a new VBA Report in KoalaBrain using the new template
  • Click on the ‘VBA Reports’ icon on the KoalaBrain main menu
  • Click on ‘New Report’ (Label 1)
  • Fill out the new report name, then click ‘Select File’ under ‘Upload a macro-enabled Excel template (.xlsm file)
  • Click ‘Browse’ and browse for the Excel file we created above, then click ‘Select File’
  • Click ‘Save Report’
• Generate and view the report

• Click on the newly created report in the list (Label 2)
  • Click on ‘Generate’ in the preview sidebar (Label 3)
  • Select the date range for the Sale date of the sales/orders you wish to be included in the report
  • Click ‘Generate Report’, and save the report wherever you want it
  • Optionally schedule the report Click on the ‘Schedule Report’ Button (Label 4)
  • Click on ‘New Scheduled Report’ and select a reporting schedule
  • Provided you have a KoalaBrain Online subscription, your reports will be uploaded on your schedule for viewing online
  • View the report in Excel Open the report in Excel and allow content and macros when prompted
  • The report function will run automatically, formatting the data added to the workbook by Koalabrain, and alerting you when complete
  • If you were to want to be able to edit the contents of the Order sheet and save the changes permanently, you could add a check to the doReport() function in the VBA script to only run once by checking for an empty ‘Orders’ sheet
  • You’re done! See the end result below