Staging Data in a Dynamic Report with Excel VBA

When preparing a dynamic report in Excel, you need to arrange the data in the report structure. In this Blog post I demonstrate how to clear the report range and re-populate it with data.

Types of reports

Reports can take many forms, as far as presenting data is concerned.

A report is any artifact created from data for end use, or as required by another system as input.

A report can be a single page summarizing information about something (see the certificate example of last week’s Blog post), it can be an order for an employee to do something (such as in a production order) and it can be a listing of records (rows) of data with headers and possibly sub-sections summaries.

In this Blog post I will focus on the classic presentation of records presented as a table, without breaking the report by any column, meaning, there are no sub-headers or sub-summaries. Here is an example of such a report:

Understanding the dynamic report process

A dynamic report is an arrangement of formatted data and graphic elements, generated every time with updated data.

The finished report can be used in several ways and routed to several outlets. It can be saved as a separate Excel Workbook, sent to the printer, published as a PDF file, and emailed as an attachment.

A dynamic report is automatically generated and is triggered by a specific event, such as a click of a button.

The typical steps for the automatic report generation process we need to program include:

1. Collecting and staging the data in the structure required
2. Clearing contents, formatting, merged cells, borders and resetting rows’ height in the dynamic range of the report
3. Populating the report range with data
4. Adding additional elements such as charts (if needed)
5. Applying layout and format: rows’ heights, numbers format, colors, borders, merging cells
6. Save/publish/print the final report as needed

Populating the report with data

In my previous Blog post I explained and presented the printing aspects of a report in a PDF format.

Today, I will present some VBA code to populate the report with data.

First, you design A dedicated Worksheet for the report with its headers.

The access point to the report data range is by a single cell: the top-left cell in the data region of the report. In the above report example, it would be the cell to receive “110” as the first employee ID. Therefore, I name this range (a single-cell range) to be: Report_Data_Anchor.

The main controlling Subroutine orchestrating the report process, may look like the following set of statements to fulfill steps 1 to 3 above:

Const EMPLOYEES_REPORT_COLUMNS = 5

Sub EmployeesReportMain()

Set rngReportAnchor = ThisWorkbook.Names("Report_Data_Anchor").RefersToRange

    Dim arrReport() As Variant

    Dim lngRecords As Long

    Dim rngReportAnchor As Range

    Call ClearReportData("Report", "Report_Data_Anchor", EMPLOYEES_REPORT_COLUMNS)   

    lngRecords = PopulateEmployeesReportArray(arrReport)

    If (lngRecords > 0) Then

        lngRecords = PlaceArrayDataInReportSheetAtPointer(rngReportAnchor, arrReport)

    End If

End Sub

(error handling, totals accumulators and such were omitted for brevity).

Clearing the report data area

The first statement calls for a Subroutine to clear all content and formatting from the last time this report was produced.

As complex reports may contain colors, borders, rows heights, merged cells, number format etc., we need to clear everything from the data area.

I find the last row populated with data by taking the last populated cell in either column “A” or in the first report column and add an extra 10 lines for any report summaries presented below the data records area.

I usually start the data columns in column “B”, but I’m looking at column “A” anyway, for possible special sub-headers I may have placed there.

Sub ClearReportData(ByVal strSheetName As String, ByVal strAnchorRangeName As String, ByVal intReportColumns As Integer)

    Dim rngAnchor As Range

    Dim FirstRow As Long

    Dim LastRow As Long

    With ActiveWorkbook.Sheets(strSheetName)

        Set rngAnchor = .Range(strAnchorRangeName)

        FirstRow = rngAnchor.Row

        LastRow = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, .Cells(Rows.Count, rngAnchor.Column).End(xlUp).Row) + 10

            With .Cells(FirstRow, rngAnchor.Column).Resize(LastRow, intReportColumns)

                .UnMerge

                .ClearContents

                .ClearFormats

                .EntireRow.RowHeight = STANDARD_ROW_HEIGHT

                .HorizontalAlignment = xlGeneral

                .VerticalAlignment = xlGeneral

                .Orientation = xlHorizontal

            End With

    End With  

End Sub

Populating the data records cells

Fetching the data for the report is a whole discussion for a couple of Blog posts.

Among the possible approaches I will mention:

• A staging table in a hidden helper Worksheet with Excel Worksheet formulas
• In-memory processing arrays with loops
• SQL over data tables in Excel Worksheets
• SQL over data tables stored in an external relational database

I rarely use option one with data records reports, for more than one reason. I’d only recommend considering this option for a small amount of data, usually a summary report without listing records.

For data records listing I usually use SQL. It is handy and performant and once you have the standard functions to serve the automation of reports it is fast to set up.

In some cases, I’d upload Excel tables into arrays and loop over these arrays to construct the report data set into another array.

Either way, with data records listing reports I always seek to have an array holding the resulting report data set after all extractions and calculations.

As SQL is beyond the scope of this Blog post, I do want to show an example of looping through arrays, to complete the above code.

The following Function uploads employees from an employees table into arrEmp(), and uploads orders from an orders table into arrOrders().

The target array for the report data is passed into the Function: arrReport(). Recall that arrays are passed ByRef, therefore the calling Subroutine will enjoy a fully populated array accessible for next steps of the program once this Function completes.

Function PopulateEmployeesReportArray(ByRef arrReport() As Variant) As Long

    Dim arrEmp() As Variant

    Dim arrOrders() As Variant

    Dim lngRows As Long

    Dim i As Long

    With ThisWorkbook.Worksheets("Data")

        arrEmp = .Range("ExcelarateEmployeesTable[#Data]").Value

        arrOrders = .Range("ExcelarateOrdersTable[#Data]").Value

    End With

    lngRows = UBound(arrEmp, 1) - LBound(arrEmp, 1) + 1

    If (lngRows > 0) Then

        ReDim arrReport(1 To lngRows, 1 To EMPLOYEES_REPORT_COLUMNS)

        For i = LBound(arrEmp, 1) To UBound(arrEmp, 1)

            arrReport(i, 1) = arrEmp(i, 1) 'Emp ID

            arrReport(i, 2) = arrEmp(i, 2) 'Name

            arrReport(i, 3) = arrEmp(i, 6) 'Region

            arrReport(i, 4) = arrEmp(i, 5) 'Quota

            arrReport(i, 5) = GetTotalSalesPerEmployee(CLng(arrEmp(i, 1)), arrOrders) 'Total sales

            curTotalSalesInReport = curTotalSalesInReport + arrReport(i, 5)

        Next i

    End If   

    PopulateEmployeesReportArray = lngRows

End Function

The last report column (built into column 5 of the array) is a summary of all orders placed by the currently iterated employee. For that I call a dedicated Function to loop through the orders array and summarize those values that meet the relevant criteria: orders of that employee, for the report year and that are not in Cancelled status. Here it is:

Function GetTotalSalesPerEmployee(lngEmpId As Long, arrOrders() As Variant) As Currency

    Dim intYear As Integer

    Dim curTotalSales As Currency

    Dim i As Long

    intYear = ThisWorkbook.Names("Report_Year_Selector").RefersToRange.Value

    curTotalSales = 0

    For i = LBound(arrOrders, 1) To UBound(arrOrders, 1)

        If (Year(CDate(arrOrders(i, 3))) = intYear) And _

            (arrOrders(i, 2) <> "Cancelled") And _

            (CLng(arrOrders(i, 5)) = lngEmpId) Then

            curTotalSales = curTotalSales + CCur(arrOrders(i, 6))

        End If

    Next i

    GetTotalSalesPerEmployee = curTotalSales

End Function

Lastly, we need to transfer the report data from the designated array to the Worksheet range expecting the report data.

This is a simple one assignment statement taking a 2-dimentional array and “throwing” its content into a range:

Function PlaceArrayDataInReportSheetAtPointer(rngAnchor As Range, ByRef arr() As Variant) As Long

    Dim lngRows As Long

    Dim intCols As Integer

    lngRows = UBound(arr, 1) - LBound(arr, 1) + 1

    intCols = UBound(arr, 2) - LBound(arr, 2) + 1

    rngAnchor.Resize(lngRows, intCols).Value = arr

    PlaceArrayDataInReportSheetAtPointer = lngRows

End Function

What else is missing

In this Blog post I focused on the steps for arranging and placing the data in a report.

This is a simple example of a report, and typically there would also be a summary section with totals and possibly sub-totals and graphic elements.

Since the report length is unknown, it could span more than one page. I therefore define the top rows of the report Worksheet, including the columns’ headers row, to repeat at the top of each page. This is done in the Page Setup dialog box, of course.

I trust I provided you with a solid foundation for data records reports, on which you can build more as per your needs.

Finally, our report is missing formatting. For this, you’ll have to get back next week in which I demonstrate highly useful generic functions for formatting numbers in the report columns, borders, alignment and colors!

In the meantime, share this Blog post far and wide, for others to appreciate your big heart!