Using Report SubtotalsLast Updated: 07/19/2016 Introduced in Verision: 2.0 |
This tutorial demonstrates how to use Report Subtotals to add Charts to the Report or to the Page.
Example:
For this example we Integrated with Northwind database. To learn how to Integrate With Database please refer to this tutorial: Integrating an External Database with a Flow.
We begin in the Designer Folder with clicking Create Report > Create Report button from Folder Actions panel.
We name our Report and click Create to proceed to the Report Designer.
In the Report Designer we expand Data Source > Common and locate Northwind database category. It should be present if we Integrated with Northwind database previously. From Northwind category we are going to add dbo_Sales Totals by Amount Data Source to our Report.
Next, we add desirable columns to our Report from dbo_Sales Totals by Amount.
Then, we navigate to the Report’s Properties and under Results section locate Sub Totals: box. In this Sub Totals box we click Add New link to add new subtotals.
In the resulting popup window we select a Field that will be used for this subtotal. In this case we selected Companyname field.
Next, we can configure Aggregation Type and Format for this subtotal. We press Ok button to save and close this popup window.
In the same manner we can add more Subtotals to our Report using Add New Link for Sub Totals: box. For this Example we are going to add more subtotals…
We pick Saleamount field for the next subtotal.
And select Sum as Aggregation type.
Next, we add another Subtotal using Saleamount field and Average for Aggregation type…
In the same manner we add a Subtotal for Saleamount with Min Aggregation type...
And Max Aggregation type.
Our Subtotals Box should now reflect 5 different Subtotals we have just added.
Now we are ready to use our Subtotals to create a Chart for this Report. From the Views panel in the Report Designer we click Add [+] > Charts > Bars to add a new chart view with Bars design.
In the Resulting popup window we need to configure our Chart. First, we give our chart a Title. Next, from the Field dropdown we can pick a Subtotal that we have created previously in this tutorial. We pick Sum(SaleAmount) subtotal.
To add more subtotals we can click Add Series button in this configuration window.
From the second Field dropdown we select Average(SaleAmount) Subtotal.
In the same manner we add Max(SaleAmount) and Min(SaleAmount) Subtotals. Next we click Add Chart to save and close this popup window.
Back in the Report Designer on the Views panel we can see a new link for the view we have just created. We click it to configure this Chart.
In the Chart Data we locate Color Picker for our Sum(SaleAmount) subtotal and pick a desirable color for this bar.
Notice, that we can define different colors for different Subtotals in our Chart.
From the Report Designer’s top panel we click Run Report link to test our Report.
Our Report opens… Mention, that all subtotals we have defined for this Report are displayed at the bottom in the Data Grid view. We click Charts icon to change our View from Data Grid to Charts.
Our Bar Chart view opens. We can observe all Bars that we have added to this Chart previously. They represent Subtotals that we have defined and they are colored as we configured… We can close Report Preview and Report Designer.
Report Subtotals can also be used to create Charts for the Page.
In the Designer Folder we click Create Page/Dashboard from Folder Actions panel to create a new Page.
Next, we name our Page and click Create to proceed to the Page Designer.
In the Page Designer we add Columns component from Reports > Charts category in the ToolBox to our workspace.
For this element we navigate to the Properties and locate Chart Data configurations. We keep Mode: Use report subtotals. Next, we use Source Picker to pick our Report with subtotals. In the Pick Report popup window we pick our Report and define Default Series as Sum(SaleAmount) as well.
Next, in the Chart Data configurations for our Columns element we use Add Series to add more Subtotals to this chart.
We add Average, Max and Min subtotals to our Chart.
Again, we can use Color Picker for each Series to define the desirable color for the column.
Here, we define different colors for each Subtotal.
From the Page Designer’s top panel we click Preview link to preview our Page.
And, we can observe that our chart is being displayed as we configured: column’s height represents the SaleAmount value, and columns are colored as we defined.
