Example II: Multi-page Report Generated with a Loop

L2-DA Demo Workflow for Sessions 4 - Part II

Advanced Reporting

Learning objective:

• Prepare a multi-page report using KNIME Reporting Extension

Workflow description: A data set with customer transaction information is used in these examples.

In the first example, a multi-page report is generated. A stacked area chart is followed by a detailed list of sales from different stores.

In the second example, a multi-page report is generated using a loop. A flow variable is used to customize each page of the report.

In the third example, a multi-page report is generated by combining two reports into a single report.

Reading and processing the data

1. A customer transaction data set is read from an Excel file by an Excel Reader node.

2. A metanode Date&Time Processing processes the transaction date information.

   • The column for transaction dates, Date, is converted to Date&Time data type.

   • The data is filtered by the transaction date, 1-1-2019 or later, inclusive.

   • The information on the year and the quarter is extracted as columns Year and Quarter, respectively.

Example I: Multi-page Report on Overall Trend and Sale Data by Stores

Aggregating transactions by stores

1. A GroupBy node is used to aggregate the data at each store:

   • Total sale amount (sum of Price)

   • Number of unique customers

   • Store type (online or onsite)

2. A Column Renamer node is used to rename some columns

Quarterly trend of sale data by store type

1. A Pivot node is used to aggregate quarterly trend on total sale by store type

   • Group: Year & Quarter

   • Pivot: StoreType

   • Aggregation: sum of Price

2. An Expression node is used to join Year and Quarter into the format 2019/Q1, saved in a new column QuarterAndYear.

Generating a multi-page report from multiple components

1. A Report Template Creator node is used to define the paper size (A4) and the orientation (Portrait) of the report.

2. The component Overall Trend produces a stacked area chart of the quarterly sale data by store type, with a header. The output of the Report Template Creator node is propagated to the bottom input port. Open the component for more details.

3. The output port of the Overall Trend component is connected to a Report Page Break node inserting a page break.

4. The Sale Data by Store component produces a report with a list of stores with their total sale amount, the number of unique customers, and the store type. Input the data from the store-wise aggregation data on the top input port, and the output from the Report Page Break node at the bottom input port. Open the component for more details.

5. Save the consolidated report to a PDF file by a Report PDF Writer node. Specify the Output location somewhere on your computer.

Group Loop Start for store type

A Group Loop Start node iterates over different classes of StoreType. The current iteration is passed on as a flow variable StoreType.

Aggregation by customer ID

A GroupBy node is used to summarize the total spending and the number of unique items for each customer. In addition to the aggregation results, CustomerGroup is also included in the output table.

Quarterly sale by customer groups

1. A Pivot node is used to summarize the quarterly sale by customer groups.

2. Any missing values are imputed with zeros with a Missing Value node.

3. An Expression node is used to join Year and Quarter into the format 2019/Q1, saved in a new column QuarterAndYear.

4. Aggregations results for the three different customer groups are renamed as CC1, CC2, and CC3 by a Column Renamer node

Generating a detailed report

1. A Report Template Creator node is used to define the paper size (A4) and the orientation (Portrait) of the report.

2. The component Detailed Report by Customer Group produces a detailed report for the store type of the current iteration, consisting of multiple views. The output of the Report Template Creator node is propagated to the bottom input port. Open the component for more details.

Report Loop End

The Report Loop End combines individual reports from different iterations into a single combined report. Make sure to check Insert page break between reports.

Saving the report as PDF

Save the consolidated report to a PDF file by a Report PDF Writer node. Specify the Output location somewhere on your computer.

Example III: Multiple reports into a single report

1. A Report Concatenate node is used to combine reports generated from Examples I and II. Make sure to check Insert page break between reports.

2. Save the consolidated report to a PDF file by a Report PDF Writer node. Specify the Output location somewhere on your computer.