Using Report Subtotals

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

createReport

We name our Report and click Create to proceed to the Report Designer.

nameReport

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.

addDataSource

Next, we add desirable columns to our Report from dbo_Sales Totals by Amount.

addColumns

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.

addSubtotals

In the resulting popup window we select a Field that will be used for this subtotal. In this case we selected Companyname field.

selectCompanyNameField

Next, we can configure Aggregation Type and Format for this subtotal. We press Ok button to save and close this popup window.

savecompanyNameSubtotal

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…

addSecondSubtotal

We pick Saleamount field for the next subtotal.

pickSaleAmtField

And select Sum as Aggregation type.

pickSumForSaleAmtSubtotal

Next, we add another Subtotal using Saleamount field and Average for Aggregation type…

AddAvgSaleAmtSubtotal

In the same manner we add a Subtotal for Saleamount with Min Aggregation type...

addMinSaleAmtSubtotal

And Max Aggregation type.

addMaxSaleAmtSubtotal

Our Subtotals Box should now reflect 5 different Subtotals we have just added.

subtotalsFinished

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.

addBars

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.

nameBarsAndPickFirstSubtotal

To add more subtotals we can click Add Series button in this configuration window.

addSeriesToBars

From the second Field dropdown we select Average(SaleAmount) Subtotal.

avarageFieldAdded

In the same manner we add Max(SaleAmount) and Min(SaleAmount) Subtotals. Next we click Add Chart to save and close this popup window.

addMaxMinAndSaveChart

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.

selectBarsView

In the Chart Data we locate Color Picker for our Sum(SaleAmount) subtotal and pick a desirable color for this bar.

pickColorForSum

Notice, that we can define different colors for different Subtotals in our Chart.

addColorsToBars

From the Report Designer’s┬átop panel we click Run Report link to test our Report.

runReport

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.

selectBarViewWhenReportIsRunning

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.

BarsViewResult

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.

createPage

Next, we name our Page and click Create to proceed to the Page Designer.

namePage

In the Page Designer we add Columns component from Reports > Charts category in the ToolBox to our workspace.

addColumnsToThePage

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.

addSumSubtotalToThePage

Next, in the Chart Data configurations for our Columns element we use Add Series to add more Subtotals to this chart.

addSeriesToPage

We add Average, Max and Min subtotals to our Chart.

seriesAddedToPage

Again, we can use Color Picker for each Series to define the desirable color for the column.

addColorTosumColumnPage

Here, we define different colors for each Subtotal.

colorsAddedToPage

From the Page Designer’s top panel we click Preview link to preview our Page.

previewPage

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.

pagePreviewWithColumns

Additional Resources