6/5/2017 - Grouped Reports in Flow

Overview


Flow enables you to build many types of reports, such as tabular, grouped, pivot tables, tables, and data summaries. Here is the second in a series of posts focusing on building reports in Flow. You can learn more about these different types of reports in the Flow online help. A grouped report is an advanced report produced by Flow. Grouped Reports organize records into one or more nested groups where each group is is a collection of records with a common column data value.

There are two basic methods you can employ to create grouped reports in Flow. The first is to add a Grouped Report action to a new or existing workflow. The second way is to open a hypercube within the Flow portal then click on the report icon Create Report button in the toolbar located at the top of the hypercube view. This post will cover the first method.

Getting Started

Add the Sample Data


After logging into Flow, you'll first need to add the "Product Sales" sample dataset to your Flow account. Click on the down arrow down arrow button in the top menu bar then click on the sample data sample data icon to open the Add Sample Dataset dialog. Click the ADD link next to the "Product Sales" entry. The sample data will be added to your account.

sample data set

The sample data will be added to your account, see below.

sample data set
sample data set

Open Cloud Connect


Flow provides an integrated development environment for building analytics-oriented workflows called Cloud Connect.

Cloud Connect is ClickOnce deployed software for developing and managing workflows. To launch Cloud Connect, open the Workflows menu in the left sidebar of the Flow portal then click the Launch Cloud Connect button, see below:

Depending upon your browser, you may need to add an extension to enable ClickOnce and launch Cloud Connect. Click on the View Cloud Connect Requirements link beneath the Launch Cloud Connect button to learn the requirements for your particular browser. If this is your first time using Cloud Connect, it may require a minute or so to install. Once the installation has completed, the application will open automatically.

workflow menu
workflow menu

Cloud Connect

  • 1. Top Menu
  • 2. Workflow Library
  • 3. Workflow Action Editor Tab
  • 4. Workflow Actions Menu
  • 5. Working Data View

Add a New Workflow


To get started, we'll need to add a new workflow. From the Cloud Connect top menu, click the add workflow button. The Add New Workflow dialog will appear as shown on the right.

Enter the workflow name then click OK. The new workflow is added to the Workflows list on the left-side of your screen. To open the workflow for editing, simply double click its name. Alternatively, you can right-click the name and then select Open from the context menu.

add grouped reporting flow
open workflow

Add a Load Data Workflow Step


Add a Load Dataset step by clicking on the actions menu menu and selecting Load Dataset from the drop-down menu; the Load Dataset Dialog will display.

Select the "Product Sales" dataset from the drop-down list then click OK to add the Load Dataset workflow step.

Next, click the run button button on the Workflow Menu then pressEnter or click Yes in response to the Run All dialog. The Load Dataset action will run and import the Product Sales data. The data will be displayed in the Workind Data at the botton of your screen.

load dataset workflow action

Create a Grouped Report

Add an Expression to Extract the Year from Each Date Column


A grouped report organizes data into one or more nested groups where each group is a collection of records with a common column data value. We are going to build a report that groups our product sales data by Country, Year, and Segment as shown to the right. Our product sales data has a Date data point but no year data point on which to group. We, therefore, need to extract the year from each row's Date value and save it to a new data point.

To extract the year value from each record's Date column, we will add an Expression action which will evaluate each Date value, compute the year from it, and save the computed value to a new data point called, surprisingly, Year.

tabular report menu item

Add a Date Expression to Extract Year

tabular report menu item

Open the Expression Builder by clicking on the actions menu menu and selecting Expression Builder from the drop-down menu, the Expression Builder dialog will display.

Add Expression Steps:

  • Select the Product Sales dataset from the Collection drop-down list in Expression Builder's Select Working Data box.
  • In the Select Expression and Operation Type box, Select the Date expression type from theExpression Type drop-down list.
  • Next, select the Year operation from the Operation drop-down list.
  • Within the Build Expression box, under Input 1, use the Datapoint drop-down list to choose the Date datapoint.
  • In the Result box, enter a name for the expression result, in this case: Year
  • Click the Add Expression button to add the expression to the list of expressions that will be evaluated when this workflow step runs.
  • Click OK to add the expression action to the Create Grouped Report workflow.
tabular report menu item

Add a Build Hypercube Step


Flow builds grouped reports from hypercubes. A hypercube is a specialized data container used to group and summarize multidimensional data.

  • From the Working Data drop-down list, select the Product Sales dataset, a check box list of possible dimensions will be displayed.
  • Add dimensions for Segment, Country, and Year by checking the box next to each data point name.
  • Enter Grouped Report Hypercube in the Hypercube Name text box.
  • Click OK to add the Build Hypercube workflow step.

Click OK, the Build Hypercube workflow step will be added to the workflow steps list.

Run the workflow again and view the results. Click the run button button in the workflow menu bar and answer Yes to the prompt. When the workflow completes, the working data tab will display a flattened view of hypercube, see below.

run all dialog

Add Hypercube Expressions to Summarize Data


At this point, we've loaded our Product Sales dataset, added an expression to extract the year group value, and built a hypercube from which we can generate our report.

We now want to compute summary values for the Cost, Discount, Sales, and Profit columns. These values will be used as totals and subtotals for each nested group within our report. Flow provides an extensive library of functions and operations for computing summary values across hypercube dimensions. We are going to add another Expression Evaluation step that will sum the Cost, Discount, Sales, and Profit for each group.

From the Actions drop-down menu, select Expression Builder, the Expression Builder dialog appear as shown.

  • Select the Grouped Report Hypercube from the Collection drop-down list in the Expression Builder's Select Working Data box.
  • Check the Hypercube check box next to the Collection drop-down list.
  • In the Select Expression and Operation Type box, Select the Stat expression type from the Expression Type drop-down list.
  • Next, select the Sum operation from the Operation drop-down list.
  • Within the Build Expression box, under Input 1, use the Datapoint drop-down list to choose the Cost datapoint.
  • In the Result box, enter a name for the expression result, in this case: Total Cost
  • Click the Add Expression button to add the expression to the list of expressions that will be evaluated when this workflow step runs.
run all dialog

When you are finished, click OK. The Expression Evaluation workflow step will be added to the workflow steps list.

Run the workflow again and view the results. Click the run button button in the workflow menu bar and answer Yes to the prompt. When the workflow completes, the working data tab will display a flattened view of hypercube which includes our expression evaluation results.

grouped report hypercube

Add a Grouped Report Step


grouped report hypercube

From the results button results menu, select the Grouped Report menu item as shown, the Hypercube Group Report dialog will appear, see below.

Group Report Steps

  • From the Working Data drop-down list, select the Product Sales dataset.
  • Enter the Report Name, Title, and Report Description as shown.
  • Add Report Columns
    • In the Report Elements -> Select Report Columns select a column from the Columns drop-down list.
    • Enter the column header text Column Header Text text box or accept the default header (the column name)
    • If the column's data type is numeric, optionally select a Number Format from the drop-list.
    • Optional - use the Sort drop-down list to select a sort direction and include the the column in the report sort order.
    • Optional - check the Include Total box to display a column total.
    • Click the Add Column button to the add the report column. The column will be added to the Columns list.
run all dialog

Run the Workflow and View the Report


Click the run button button on Workflow Menu. Press Enter or click Yes in response to the Run All dialog. The workflow will load the data, create the report, and save the report result to your Flow account. To view the report, go to the Flow portal and and open the Reports & Charts section under the Results menu. Click on the "US Retail Sales Report" report name to display the report.

report result menu
sales report example

Summary


In this post, I covered how to make a simple five-step workflow to build and publish a grouped report. To generate a grouped report, you must first add a Load Dataset action. This action imports the required report data into the workflow's working data memory and builds the data point definitions used to create the report. After running the Load Dataset action, we added a Build Expression step to extract the year from each Date column value. We then built a hypercube with dimensions Segment, Country, and Year. Next, we added a hypercube expression step to summarize the Cost, Discount, Sales, and Profit values across each of our selected dimensions. Finally, we added a Group Report step to generate our report. To learn more about other reporting options in Flow, check out these related posts: