© Copyright 2023, Astera Software
The Excel Workbook Report object in Astera is designed to tabulate information from selected fields and present the results in a one- or two-dimensional matrix. This feature enables deeper analysis of data by organizing it in a way that facilitates the identification of trends, patterns, and insights.
To get the object from the Toolbox, go to Toolbox > Destinations > Excel Workbook Report. If you are unable to see the Toolbox, go to View > Toolbox or press Ctrl + Alt + X.
Drag-and-drop the Excel Workbook Report object onto the designer.
The dragged report object is empty right now. This is because the data fields are not mapped to it yet. While any source can be used, for this particular use case, we will demonstrate using a Report Source that is extracting data from a PDF source file.
Configure the source object and place it onto the designer next to the Excel Workbook Report object.
Note: We are using the data that we extracted from a customer invoice with the help of Astera's report modeling tool.
Now map the data fields from the source object to the report object.
To configure the Excel Workbook Report object, right-click on the header, select Properties from the context menu and a dialog box will open.
Provide the File Path. This is where the excel report file will be saved.
The dialog box has some other configuration options such as worksheet and start address, which work just like that of Excel Workbook Destination.
Once The File Path and Data reading options have been specified on this screen, click Next.
The next window is the Layout Builder. On this window, the layout of the excel report file can be modified.
Here, you can write names of fields as you want them to appear in your destination in the Header column and specify the relevant Aggregate Functions for them.
Aggregate Functions define how the data will be summarized in the report:
Group By: Groups records based on unique values in the specified field.
Sum: Calculates the total sum of the specified field.
Count: Counts the number of records.
Average: Calculates the average value of the specified field.
Max: Finds the maximum value in the specified field.
Min: Finds the minimum value in the specified field.
First: Returns the first record in a sorted list based on the specified field.
Last: Returns the last record in a sorted list based on the specified field.
Variance: Calculates the variance of the specified field.
Standard Deviation: Calculates the standard deviation of the specified field.
None: Includes the field in the report without applying any aggregation. This is useful when you want certain field values in the data lines but don’t want to apply any aggregation on them.
For this case:
AccountID: We will select the Group By option from the Aggregate Function drop-down list for this field as we want to group the records based on individual accounts.
OrderID: We will select the Group By option from the Aggregate Function drop-down list for this field want to see orders within each account.
TOTAL: For this field we will select the Aggregate Function Sum, to calculate the total amount per order.
QUANTITY: For this field we will select the Aggregate Function Sum, to calculate the total quantity per order.
ITEM: Since we want to show item names in the data lines but do not want to apply any aggregates on them, we will select Aggregate Function None.
The same approach will be applied to the ITEM CODE, DESCRIPTION, and PRICE fields. We will select Aggregate Function None for each of these fields to ensure that their specific values are displayed in separate data lines without any aggregation.
Click Next. The Report Options window will now open.
Report Type: You can select from three report types. Summary, Cross Tab or Time Series.
Note: For this use case we have chosen Summary as the Report Type.
Title: To provide a meaningful title to your report, enter a new title into the Title field.
Subtotal Text: You can specify the name for Subtotal field.
Grand Total Text: You can specify the name for Grand total field.
Enable Case Sensitive Match – Check this option if you want to Group your data on a case sensitive basis. For example, if you have customer names like "john" and "John," enabling this option will treat them as distinct groups rather than combining them into a single group.
Style
You can also modify the style of your report.
Show Data Lines: If you want to see the subtotals and grand totals along with the actual records of data then you check this option.
Insert Blank Line Before Grand Total: Inserts a blank line before the grand total in the report.
Write Grand Total: Adds the grand total to the report. If unchecked, the grand total won't be included.
Insert Blank Line Before Subtotal: Inserts a blank line before each subtotal in the report.
Insert Blank Line After Subtotal: Inserts a blank line after each subtotal in the report.
Click Next. The Aggregate Transformation Properties window will now open.
There are three sorting options in Aggregate transformation:
Incoming data is pre-sorted on group by fields: This option Aggregate requires data to be sorted by the specified Group-By field.
Sort Incoming data before building aggregate: This option will first sort the incoming data, then build its aggregate.
Build aggregate using unsorted data: This option will build aggregate using the incoming data whether it is sorted or not.
The Excel Report object is successfully configured, and the report file can now be created by running the dataflow.
Below, you can see a sample of how the summary report appears.
Note: If you want to see only the subtotals and the grand total you can uncheck the Show Data Lines option in the Report Options wizard. This will display only the summarized totals without individual records.
