416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Customizing Totals and Subtotals in a Data Grid

Hide navigation
RSS
Modified on Fri, 28 Feb 2014 08:56 AM Categorized as Data Grid, Level-Advanced, Script Library
Dundas Dashboard Script LibraryPrevious


Overview

This example shows how to customize the numeric values in a totals or subtotals row of a data grid. By adding a script for the data grid's Row Loaded interaction, you can retrieve a total or subtotal value, modify it, and then have the data grid display the updated value in the same or different column.

Background

By default, the standard total and subtotal values in a data grid are calculated using the Sum aggregator type. You can change this on a per-column basis by setting the column's Aggregator Type property to a different aggregator such as Average, Count, First, Last, Max, or Min. However, there may be cases like the example presented in this article where you need to perform a custom aggregation (e.g. your data grid shows a column of percentages which are calculated based on values from other columns).

Setup

Dataset

Create a virtual table and corresponding dataset as shown here:

Dataset preview.

Dataset preview.


Dashboard

Create the initial dashboard as follows:

  1. Drag the dataset from the toolbox to the dashboard canvas.
  2. In the Data Settings Configuration and Data Grid Binding Setup wizard, accept the default options for each step.
  3. Go to the Properties window for the data grid. Click on the data grid's View property and enable grouping:
    • Set the Group Panel Visibility property to Visible.
    • Set the Enable Column Grouping property to True
  4. Save, then preview your dashboard.
  5. Drag the Project column header to the group panel in order to group the rows by project, then expand each project.

Initial data grid with rows grouped by Project.

Initial data grid with rows grouped by Project.


Next, enable the display of the standard totals and subtotals as follows:

  1. Go to the Properties window for the data grid:
    • Set the Enable Sub Totals property to True.
    • Set the Enable Totals property to True.
  2. Save, then preview the dashboard.
  3. Drag the Project column header to the group panel in order to group the rows by project, then expand each project.

The data grid now displays total and subtotal values for the last 3 columns. However, observe that the total and subtotal values in the PercentComplete column are incorrect. This is because a Sum aggregation is used by default. But choosing another type of standard aggregator will not work either as a custom aggregation needs to be performed.

Data grid showing incorrect percent totals and subtotals.

Data grid showing incorrect percent totals and subtotals.


To perform the custom aggregation that is required:

  1. Go to the Properties window for the data grid and access the Columns property editor.
  2. Select the PercentComplete column and set its Aggregator Type to None.
    Aggregator Type property.
  3. Use scripting to perform the custom aggregation as shown in the next section.

Script

Row Loaded

Add the following script code for the Row Loaded interaction (which is classified as advanced) of the data grid. The script retrieves the total/subtotal values from the Effort_Days and Remaining_Days columns and uses them to calculate the correct PercentComplete total/subtotal values.

#UNSUPPORTED UNLIMITED DashboardDataGrid grid = DataGrid1; DashboardDataGridRow row = (DashboardDataGridRow)e.VisualElementArgs; if (row != null) { if (row.IsSubtotal) { object dataSource = row.DataSource; // Get the columns involved. DashboardDataGridDataColumn effortColumn = grid.Columns[2]; DashboardDataGridDataColumn remainingColumn = grid.Columns[3]; DashboardDataGridDataColumn percentColumn = grid.Columns[4]; // Get the subtotal values for the effort and remaining days. object effortSubtotal = grid.GetSubtotal(dataSource, effortColumn); object remainingSubtotal = grid.GetSubtotal(dataSource, remainingColumn); // Compute and set a new subtotal value for the percent complete column. if (effortSubtotal != null && remainingSubtotal != null) { double percentComplete = 100 * ((double)effortSubtotal - (double)remainingSubtotal) / (double)effortSubtotal; grid.SetSubtotal(dataSource, percentColumn, percentComplete); } } else if (row.IsTotal) { // Get the columns involved. DashboardDataGridDataColumn effortColumn = grid.Columns[2]; DashboardDataGridDataColumn remainingColumn = grid.Columns[3]; DashboardDataGridDataColumn percentColumn = grid.Columns[4]; // Get the total values for the effort and remaining days. object effortTotal = grid.GetTotal(effortColumn); object remainingTotal = grid.GetTotal(remainingColumn); // Compute and set a new total value for the percent complete column. if (effortTotal != null && remainingTotal != null) { double percentComplete = 100 * ((double)effortTotal - (double)remainingTotal) / (double)effortTotal; grid.SetTotal(percentColumn, percentComplete); } } }

Testing

Save, then preview the dashboard in the same way as before to see the resulting data grid with the correct total and subtotal values in the PercentComplete column.

Data grid showing custom subtotals and total.

Data grid showing custom subtotals and total.


Using Column Group

When the cell you want to customize is inside a Column Group, like the Ratio(C1/C2) column in the Datagrid below:
DataGrid with Column Group.

DataGrid with Column Group.


Here is the part of the code to specify the column to format: ... DashboardDataGridColumnGroup group1= grid.Columns[1]; DashboardDataGridColumn columntoformat= group1.Columns[2]; DashboardDataGridDataColumn percentColumn = columntoformat; ...

DataGrid with Column Group - Result.

DataGrid with Column Group - Result.


Note: If sort order of the columns in the DataGrid changed, you need to update your script to reflect the new column indexes.

Related topics


About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.