Creating and Running a Report with Table Layout and a computed Column

This tutorial demonstrates how to create a report definition with a table layout, which uses computed columns and filtering. Additionally we will schedule this report for execution.

The report definition should provides an overview on the number of product items that have been sold in a process for different customers. We use a computed column to provide customer details containing the customer name and last four digits of the customer Id. Later we will filter the report for specific countries.

Preparing the Workflow and Data

Create a model containing a structured data with customer information.

You can also import the completed example model provided by Infinity Process Platform. Therefore download the following ZIP file containing the model ProductOrders prepared for this example:

all-tutorials.zip

You find this model in folder reports. This folder also contains the completed example report, which you can import as well.

The customer details data should contain the following entries:

For example:

Structured Data
Figure: Structured Data

Create a simple process reading data for the customer details.

Process
Figure: Process Diagram

Open the process properties panel to add descriptors, which will be used in our report. Click on Data Path and add the following descriptors:

Setting Descriptors
Figure: Setting Descriptors

For details on setting process data path a descriptors, please refer to chapter Configuring Data Paths in the Business Process Modeling Handbook.

Save and deploy the model.

Creating Data Input

Start the process and enter data several times. Enter long numbers for customer Ids.

Enter Data
Figure: Enter customer details with long Id

Creating the Report Definition

To create the report definition, switch to the Reporting perspective. Click the Create Ad hoc Report button to create the report definition.

Creating a new Report
Figure: Creating a new Report

In the Data Source tab keep the default selection to retrieve data about process instances in the Retrieve Data about drop-down list.

Data Source
Figure: Retrieve data about process instances

Adding a computed Column

Now we create a computed column to display the customer name together with the last four digits of the customer Id.

  1. In the Computed Columns section, click the Add icon

    Add a Computed Column

  2. Add a field name and the name the field is displayed with
  3. In the JavaScript Formula section enter the following code:
    CustomerName + " - " + (CustomerId - parseInt((CustomerId / 10000), 10) * 10000);

JavaScript
Figure: Enter JavaScript to compute the column

Configuring the Data Set

Switch to the Data Set tab to configure the data set.

  1. Select Series Group
  2. Select Fact Count
  3. In the Dimension drop-down list select our computed column

  4. Select to group the data set by descriptor OrderedItem

The data set is now configured as in the following screenshot:

Data Set properties
Figure: Setting Data Set properties

Specifying the Layout

Switch to the Layout tab to specify the table layout.

  1. Select Simple Report
  2. Enter the Report Title
  3. Select Layout Type Table
  4. Choose Columns for Display Dimension as
  5. Select Display Totals

Layout Settings
Figure: Layout Settings

Previewing the Report

Switch to the Preview tab to view the report. The columns are created for each computed column containing the customer name together with the last four digits of the customer id. The table layout is grouped by the name of the ordered items. Additionally a Total column is displayed with the total count for each row.

Report Preview
Figure: Report Preview

Storing the Report

To set properties for storing the report, switch to the Storage tab. Enter the report definition name and optionally a description of the report. Determine to store the report definition as Personal Report Template.

Storage properties
Figure: Setting Storage properties

To save the report, click the Save icon Save in the upper right corner of the view.

Saving the Report
Figure: Saving the Report

You can now find the saved report in the following locations:

Running the Report with a Filter Parameter

Now we filter our report to filter out products from a specific country:

  1. In the Filtering section, click the Plus icon to add a filter
  2. Select Country in the Descriptors section of the drop-down list

    Filter - descriptor

  3. In the value list select all countries you like to include in your report

    Filter - operator

  4. Click the Add Parameter icon to add this filter as parameter

    Filter - param

The entered filter should look similar to the one in the following screenshot:

Filter
Figure: Setting a report filter

Viewing the filtered report

Switch to the Preview tab to see the resulting report. You can see that the column for the country to be filtered out does not appear anymore in the table. Above the table, the filter is displayed in the Parameters section.

Filter Result
Figure: Report preview result with filter

Scheduling the Report Execution

To schedule our report definition for weekly execution perform the following steps:

  1. Starting the Reporting Daemon
  2. Setting Scheduling Properties

Starting the Reporting Daemon

The reporting daemon must be running to facilitate report execution. It runs periodically to check the next recurrence date for the report definition. Once the next recurrence date arrives, it executes the report and distributes it via the specified delivery method. To start the report daemon:

  1. Open the Administration perspective
  2. Select Daemon in the launch panel
  3. In the Actions column of the Reporting Daemon in the displayed Daemon table click the icon to start the reporting daemon.

Setting Scheduling Properties

To set scheduling execution properties do the following:

  1. Open the Scheduling tab
  2. Enable the option to schedule the report template to be executed with one or more recurrences
  3. Select Weekly in the Recurrence Pattern section
  4. To receive an E-mail with the report attached, select E-mail to me in the Delivery section
  5. In the Next Execution section you can see when the report template will be executed next

Scheduling
Figure: Scheduling the report

An E-mail will be sent to you on a weekly basis with the report in HTML format as attachment.

Report E-mail
Figure: Report E-mail

Exporting the Report as Excel File

You can export our report to an Excel file. In the Preview tab click the Excel button in the left upper corner of the table.

Export as Excel File
Figure: Export as Excel File

In the upcoming browser dialog select a location to store the report as Excel file. You can now view and edit your report results in Excel format.

Exported Excel Result
Figure: Exported Excel Result