Excel Workbook Report

Excel Workbook Report

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.

Configuring the Excel Workbook Report Object

• 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.

Mapping

• 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.

Configuration

1. To configure the Excel Workbook Report object, right-click on the header, select Properties from the context menu and a dialog box will open.

2. 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.

3. Once The File Path and Data reading options have been specified on this screen, click Next.

Summary Report

Layout Builder

The next window is the Layout Builder. On this window, the layout of the excel report file can be modified.

4. 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.

Report Options

5. 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.

Aggregate Transformation Properties

6. 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.

Source Input Preview

Summary Report – With Data Lines

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.

Summary Report – Without Data Lines

Crosstab Report

A Crosstab Summary displays summarized information about two fields in a two-dimensional matrix. The values for one field are displayed down the left-most column of the matrix and the values for the other key field are displayed across the top row as columns. This two-dimensional arrangement displays only a single measure at a time.

Let’s see how we can make a Cross Tab Summary using Excel Report Source.

Mapping

Since we want to use information from two tables (order and order details) we have joined them and to calculate the total we used an expression object. We can then map data fields we needed from both to the Excel Report Object.

Layout Builder

For this case:

CustomerID: 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 customers.

ProductID: We will select the None option from the Aggregate Function drop-down list for this field as we want to spread the Product Values to spread across the top row(columns).

Total: We will select the Sum option from the Aggregate Function drop-down list for this field as we want totals for each type of product and the totals for each customer.

Report Options

Note: For this use case we have chosen Cross Tab 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.

• Row Total Text: You can specify the name for Row total 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.

Crosstab Options

Column Field: You can select which field or attribute you want to use as the column headers in the resulting cross-tabulation report.

Note: The selected column field should have Aggregate Function None selected in the layout builder.

Row Totals – Check this Option if you want to add each rows total to your report.

Style

You can also modify the style of your report.

• Add Grand Total: Inserts the grand total in the report.

• Add Blank Line Before Grand Total: Inserts a blank line before the grand total in the report.

• Add Row Total: Inserts the row totals in the report.

• Add Blank Line After Subtotal: Inserts a blank line after each subtotal in the report.

Aggregate Transformation Properties

Click on 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.

After defining the options, Click Ok.

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. The summary in the table shows the sales data for different products purchased by various customers, identified by their CustomerID.

Time Series Report

Time Series summary displays summarized information about two key fields in a two - dimensional matrix. The values for one field are displayed down the left most column of the matrix and the time intervals (such as days, months, quarters, or years) are displayed across the top row as columns.

Let’s see how we can make a Time Series Summary using Excel Report Source.

Mapping

Since we want to use information from two tables (order and order details) we have joined them and to calculate the total we used an expression object. We can then map data fields we needed from both to the Excel Report Object.

Layout Builder

For this case:

CustomerID: 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 customers.

OrderDate: We will select the None option from the Aggregate Function drop-down list for this field as we want to use this date field across the top row(columns).

Total: We will select the Sum option from the Aggregate Function drop-down list for this field as we want the totals for each customer.

Report Options:

Note: For this use case we have chosen Time Series 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.

• Row Total Text: You can specify the name for Row total 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.

Timeseries Report Options

Time Unit Drop-down: You can specify the time interval for the time series analysis. Available options include:

• Year: Analyze data on a yearly basis.

• Month: Analyze data on a monthly basis.

• Day: Analyze data on a daily basis.

• Week: Analyze data on a weekly basis.

• Quarter: Analyze data on a quarterly basis.

Start Date: You can specify the start date for the time series analysis. This defines the beginning of the time period for which data will be analyzed.

End Date: You can specify the end date for the time series analysis. This defines the end of the time period for which data will be analyzed.

Date Field: Field from the dataset that contains the date or timestamp information. The selected date field will be used to create the time series.

Style

You can also modify the style of your report.

• Add Grand Total: Inserts the grand total in the report.

• Add Blank Line Before Grand Total: Inserts a blank line before the grand total in the report.

• Add Row Total: Inserts the row totals in the report.

• Add Blank Line After Subtotal: Inserts a blank line after each subtotal in the report.

Aggregate Transformation Properties

Click on 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.

After defining the options, Click Ok.

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. This summary table shows the number of sales across different years for customers, identified by their CustomerID.