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.

Using the Data Grid

Hide navigation
RSS
Modified on Mon, 21 Apr 2014 01:00 PM Categorized as Data Controls, Data Grid, Design, Level-Intermediate
Click to return to: Documentation | Designing Dashboards | DV Controls | Data Grid


Elements of a data grid

The figure below shows the main elements of a data grid control.

Elements of a data grid.

Elements of a data grid.


Getting started

The data grid control is capable of displaying data from one or more KPIs/datasets simultaneously. For example, you can easily configure a data grid to display several KPIs in the form of a scorecard, where each KPI is represented by one row in the data grid.

The basic usage for the data grid control is as follows:

  1. Drag the data grid control from the toolbox to the dashboard canvas.
  2. Drag the KPI or dataset that you want to display from the toolbox and drop it over your data grid control.
  3. Repeat Step 2 if you want to add more KPIs/datasets to your data grid control.

When you drag-and-drop a KPI or dataset onto a data grid, the Data Settings Configuration wizard is displayed. This wizard lets you perform a couple of optional steps that are common to other types of data visualization controls - you can click Next to bypass these steps and go on to the Data Grid Binding Setup wizard.

Data grid binding setup

The Data Grid Binding Setup wizard lets you map your data (KPIs and datasets) to the columns and rows of a data grid. You can choose which columns to show or hide, and there are convenient options for setting up the data grid to display a standard tabular view or as a scorecard. For more details, see Using the Data Grid Binding Setup Wizard.

Data Grid Binding Setup wizard.

Data Grid Binding Setup wizard.


Columns

Styling columns

There are 3 kinds of styles that apply to the appearance of a data grid column:

  • The Header Style controls the appearance of the header for the column.
  • The Cell Style controls the appearance of the cells in the column.
  • The Total Cell Style controls the appearance of the totals cell when the totals row is enabled.

To change the header, cell or total cell style for all of the columns of a data grid:

  1. Locate the data grid's View property and click its ellipsis button.
  2. From the View property dialog, locate the Cell Style property and click its command menu.
    View's Cell Style property menu.
  3. From the menu, choose New Value. You can now expand the Cell Style property to configure the appearance of the cells in each column.
  4. Repeat the above steps for the Header Style property to configure the appearance of the header for each column.
  5. Repeat the above steps for the Total Cell Style property to configure the appearance of the totals cell when the totals row is enabled.

Styling a specific column

Use the Columns property of the data grid to configure the styling for a specific column:

Columns property.

Columns property.


To configure the styling for a column at design-time:

  1. Locate the Columns property and click its ellipsis button. The Columns property editor is displayed.
  2. Select a column to edit from the list on the left. The properties of the column appear on the right.
  3. Locate the Cell Style property and click its command menu.
    Cell Style property menu.
  4. From the menu, choose New Value. You can now expand the Cell Style property to configure the appearance of the cells in the column.
  5. Repeat the above steps for the Header Style property to configure the appearance of the header for that column.
  6. Repeat the above steps for the Total Cell Style property to configure the appearance of the totals cell when the totals row is enabled.

Cell Style and Header Style properties for a column.

Cell Style and Header Style properties for a column.


Tip: If you want to apply the same cell, header and total cell styles to all grid columns, set the Cell Style, Header Style and Total Cell Style properties on the data grid's View. Then Reset the cell, header and total cell styles on each individual column (since the column styles take precedence when they are set).

Changing the order of columns

At design-time, use the Columns property of the data grid to change the order of its columns:

  1. Locate the Columns property and click its ellipsis button. The Columns property editor is displayed.
  2. Select a column for re-ordering from the list on the left.
  3. Use the two arrow buttons on the right to move the selected column up or down in the order.

Changing the column order.

Changing the column order.


While viewing a dashboard, you can also re-order the columns of a data grid interactively by applying drag-and-drop to its column headers.

Drag the Product SKU column to a new position.

Drag the Product SKU column to a new position.


Interactive re-ordering is enabled by default, and is controlled by two properties which you can configure at design-time as follows:

  1. Locate the View property and click its ellipsis button. The View property editor is displayed.
  2. Click the Show advanced properties button at the top of the property editor.
  3. Use the Enable Column Reordering property to enable or disable interactive re-ordering.
  4. Use the Column Reorder Indicator Fill property to specify the color of the vertical drag indicator that appears when you drag a column header.
  5. Click OK.

Column re-ordering properties.

Column re-ordering properties.


Sorting on a column

While viewing your dashboard, you can sort the rows of a data grid based on a column as follows:

  1. Click once on a column header to sort rows using ascending (or increasing) column values. A small, upward pointing triangle indicator appears at the top of the column header.
  2. Click a second time on the column header to sort rows using descending (or decreasing) column values. A downward pointing triangle indicator appears at the top of the column header.
  3. Click a third time on the column header to cancel sorting on that column. The sort indicator disappears from the column header.

Ascending sort indicator.

Ascending sort indicator.


By default, sorting is done based on a single column. This means that if you are sorting on one column, and then click on the header of a second column, sorting will be cleared on the first column and applied only to the second column. In order to sort the data based on multiple columns, press and hold down the SHIFT key, then click the header of each column you want to sort. A sort indicator will appear at the top of each column header that you clicked. The combined sort order is determined by the order in which you've clicked on the different column headers.

Using a column menu

For each column, you can enable a dropdown menu that lets dashboard viewers control the sorting on that column, or perform filtering based on selected column values.

Accessing a column menu.

Accessing a column menu.


To enable a column menu (at design-time):

  1. Locate the Columns property of the data grid and click its ellipsis button. The Columns property editor is displayed.
  2. Select a column from the list on the left.
  3. Set the Enable Column Menu property to True.

Enable Column Menu property.

Enable Column Menu property.


To use a column menu for sorting and filtering (while viewing a dashboard):

  1. Hover over the column header with your mouse. A menu button (i.e. upside-down triangle) appears to the right.
  2. Click the menu button to see the dropdown column menu.
  3. The top part of the menu lets you control the sorting on the column:
    • To sort the data by ascending column value, click the A to Z button. The ascending sort indicator (i.e. small triangle) appears at the top of the column header.
    • To sort the data by descending column value, click the Z to A button. The descending sort indicator appears at the top of the column header.
    • To cancel sorting on that column, click the Delete button. The sort indicator is removed from the column header.
  4. The rest of the menu lets you configure and apply a filter to the data in the grid based on selected column values:
    • To see all of the column values, choose the Select All option, then click Apply.
    • To see a subset of the column values, select the check boxes that correspond to the column values you want to see, then click Apply.
    • To remove the current filter, click Clear Filter.
  5. To close the column menu, click the menu button.

Adding a new column

You can add new columns manually to a data grid or remove existing columns via the Columns property editor.

The data grid supports several different types of columns:

  • Text - The default column type for displaying textual (e.g. numeric) values.
  • Image - Lets you display an image in each cell of the column.
  • Control type columns:
    • State Indicator - Lets you display a state indicator in each cell of the column.
    • Traffic Light - Lets you display a traffic light in each cell of the column.
    • Bullet Graph - Lets you display a bullet graph in each cell of the column.
    • Sparkline - Lets you display a sparkline in each cell of the column.
    • Data Bar - Lets you display a data bar in each cell of the column.
    • Checkbox - Lets you display a read-only checkbox in each cell of the column (for boolean-valued data).
  • Column Group - Lets you create a grouped column header that spans multiple columns.

To add a new column to the data grid:

  1. Locate the Columns property and click its ellipsis button. The Columns property editor is displayed.
  2. If you want to add a new text column, click the Add button below the list on the left.
  3. If you want to add one of the other supported column types, click the menu button which is located beside the Add button. From the dropdown menu, choose the desired column type. A new column appears in the list on the left and the properties of the newly added column appear on the right.
    Choosing the column type.
  4. Click the Show advanced properties button at the top of the properties list.
  5. Enter a display name for your column using the Header Text property.
  6. Use the Data Column property to select the measure/dimension/column from your KPI or dataset that will be providing the column values.
  7. If you want to give your dashboard viewers the ability to sort on this column, set the Sort Column property to the same value as the Data Column property.

Depending on the type of column, you may need to perform additional steps. See Data Grid Examples for more details.

Showing or hiding a column

At design-time

To hide a column in the data grid:

  1. Locate the Columns property and click its ellipsis button. The Columns property editor is displayed.
  2. Select the column you want to hide from the list on the left.
  3. Set its Visibility property to Collapsed.
  4. Click Close.

To show a column in the data grid:

  1. Locate the Columns property and click its ellipsis button. The Columns property editor is displayed.
  2. Select the column you want to show from the list on the left.
  3. Set its Visibility property to Visible.
  4. Click Close.

Tip: You can also control this property via scripting. See this example from the Dundas Dashboard Script Library.

At viewing-time

While viewing a dashboard, right-click over a data grid to see its context menu. From the menu, you can show or hide a specific column.

Show or hide columns at viewing-time.

Show or hide columns at viewing-time.


Hiding column headers

To hide (i.e. collapse) the column headers of a data grid:

  1. Locate the View property and click its ellipsis button. The View property editor is displayed.
  2. Set the Headers Visibility property to Collapsed.
    Setting column header visibility.
  3. Click OK.
  4. Save, and then preview your dashboard.

The data grid appears without the row of column headers displayed.

Resizing a column

While viewing a dashboard, users can interactively change the width of data grid columns if the data grid's Enable Column Resizing property is set to True. You can access this property at design-time as follows:

  1. Locate the View property and click its ellipsis button. The View property editor is displayed.
  2. Set the Enable Column Resizing property to True or False as desired. By default, column resizing is enabled for a data grid.

To resize the width of a column interactively (e.g. at viewing-time):

  1. Move the mouse pointer over the grid line on the right-side of the column that you want to resize.
  2. The mouse cursor appears as a double-sided arrow indicator.
    Resizing a column.
  3. If you want to change the width of the column manually, click once on the arrow indicator and drag horizontally with your mouse to move the grid line to the left or to the right as desired.
  4. Alternatively, double-click the arrow indicator in order to have the width of the column automatically increase (if needed) to fit all of the contents of that column.

Rows

Styling a row

There are three sets of styles that apply to the rows of the data grid:

  • The Row Styles property applies to the first row, and every other row thereafter.
  • The Alternate Row Styles property applies to the second row, and every other row thereafter.
  • The Total Row Style property applies to the totals row (if enabled).

Row style properties.

Row style properties.


To configure the Row Styles property:

  1. Click the property's ellipsis button. The Row Styles property editor is displayed.
  2. Click the Add button below the list on the left. A new row style is added to the list.
    Adding a new row style.
  3. Set the Appearance and Text properties of the new row style as desired.
  4. Click Close.

The data grid on the canvas is updated to reflect your changes to the row style.

Updated row style at design-time.

Updated row style at design-time.


Note that Row Styles and Alternate Row Styles are collection-style properties. You can add more than one row style in order to customize the styling for different levels when row grouping is in effect.

Two different row styles in effect.

Two different row styles in effect.


Setting the selection mode

The data grid supports three selection modes:

  1. Single Row - Only one row can be selected at any time.
    Single Row selection.
  2. Extended Row - Multiple rows can be selected at any time. To select a range of rows, click the first row that you want, then press the SHIFT key and click the last row in the range. To select rows individually, press the CTRL key and click the rows you want to include in the selection.
    Extended Row selection.
  3. Extended Cell - Any combination of cells from different rows can be selected at the same time, using the SHIFT and CTRL keys as described above.
    Extended Cell selection.

Use the Selection Mode property (under the Common category) to set the selection mode for the data grid:

Selection Mode advanced property.

Selection Mode advanced property.


Note: This is an Advanced property. You must click the Show advanced properties button at the top of the properties grid to see this property.

Copy and paste

Data that is displayed in your data grid can be copied to the Windows clipboard, for example, and then pasted into an external application such as Microsoft Excel.

To copy data that is displayed in your data grid to the clipboard (while viewing a dashboard):

  1. Select the data grid rows and/or cells containing the data you want to copy.
  2. Press CTRL-C to copy the selected data values to the clipboard.
  3. Go to your external application (e.g. Excel, Notepad) and paste the (tab-delimited) values as desired.

Note: If your selection mode is Extended Cell, only certain arrangements of selected cells are valid for copying. The general rule is that the selected cells must form a rectangular grid of values.

Grouping rows based on a column

At design-time, you can enable the data grid's Group Panel, which gives dashboard viewers the ability to apply grouping to rows interactively. The Group Panel appears on top of the data grid's column headers. To apply grouping to rows based on the values of a column, simply drag the column's header and drop it over the Group Panel.

The Group Panel.

The Group Panel.


To enable the Group Panel (at design-time):

  1. Locate the View property under the Common category.
    View property.
  2. Click the property's ellipsis button. The View property editor is displayed.
    View property editor.
  3. Set the Enable Column Grouping property to True.
  4. Set the Group Panel Visibility property to Visible.
  5. Click OK.

To use the Group Panel (while viewing a dashboard):

  1. Decide which column you want to group by.
    Group Panel is enabled.
  2. Drag the column header and drop it over the Group Panel. The rows in the grid will be grouped based on that column and there will be one row grouping for every set of unique column values.
    View property editor.
  3. By default, the column that you are grouping by is automatically hidden in the data grid. This behavior is controlled via the Auto-hide Dragged Group Columns advanced property of the data grid View. Set this property to False at design-time if you want to see the grouped by column(s) displayed in the grid.
  4. (Optional) Drag a second column header and drop it over the Group Panel if you want to add a second level of row grouping.

Two levels of row grouping.

Two levels of row grouping.


To remove a column from the Group Panel:

  1. Hover over the column in the Group Panel with your mouse.
    Removing a column from group panel.
  2. Click the ‘X’ button. The rows of the data grid will be ungrouped by that column.

Frozen rows

While using the Group Panel, if you expand some row groupings and scroll the data grid, you'll see that the header row of the current group is frozen by default. This makes it easy to see what the current grouping is when there are a lot of rows in the group.

Frozen row.

Frozen row.


You can enable/disable or configure this functionality using these advanced properties of the data grid View:

  • Freeze Parent Row Groups
  • Frozen Rows Separator Effect
  • Frozen Rows Separator Fill
  • Frozen Rows Separator Height

The separator element is on top of the frozen row group. It can be used to visually distinguish frozen rows from regular ones.

Using group descriptions

Use the Group Descriptions advanced property to set up columns for row grouping at design-time. This functionality lets you pre-configure the Group Panel with one or more columns to group by.

Group Descriptions property.

Group Descriptions property.


To specify a column for grouping (at design-time):

  1. Click the Show advanced properties button at the top of the properties list for the data grid.
  2. Click the ellipsis button of the Group Descriptions property. The Group Descriptions property editor is displayed.
  3. Click the Add button below the list on the left. A new group description is added to the list.
    Group Descriptions dialog.
  4. Click the Data Column property value and choose a column to group by.
  5. The Data Source Type property value should already be set depending on how your data grid is bound to data. For example, if your data grid is displaying multiple KPIs/datasets (scorecard view) where each KPI/dataset occupies one row, the Data Source Type should be set to Primary.
  6. Optionally, use the Format String property to specify how the values of the grouped column should be formatted for display. These formatted values appear in the expandable/collapsable header row for each set of grouped rows. See Formatting Text for details on format strings.
  7. Enter a name for the group description using the Text property.
  8. Click Close.

Save, and then preview your dashboard to see the chosen column displayed automatically in the Group Panel:

The Data Column appears in the Group Panel.

The Data Column appears in the Group Panel.


Transposing rows and columns

Use the Is Transposed property of the data grid View to transpose or pivot the rows and columns of a data grid.

As an example, consider a dataset that is displayed using a data grid as shown below. The header text of the first column has been cleared to avoid ambiguity.

Data grid displaying a dataset.

Data grid displaying a dataset.


To transpose the rows and columns of this data grid:

  1. Locate the View property and click its ellipsis button. The View property editor is displayed.
  2. Set the Is Transposed property to True. The rows and columns of the data grid are immediately transposed on the design canvas.
    Is Transposed property.
  3. Click OK.
  4. Save, and then preview your dashboard.

The transposed data grid appears as shown below.

Data grid with Is Transposed = True.

Data grid with Is Transposed = True.


Note: The Is Transposed property cannot be used in conjunction with row grouping (i.e. the Group Panel) or column groups (i.e. grouped column headers).

See also:


Totals and sub-totals

To display a totals row at the bottom of the data grid, set the data grid's Enable Totals property to True. The totals row is always visible, even as you scroll the data grid vertically.

Displaying totals and sub-totals.

Displaying totals and sub-totals.


If your data grid displays grouped rows, you can also show sub-totals for each group by setting the data grid's Enable Sub Totals property to True. If you have multiple levels of grouping, note that each group description also has its own Enable Subtotals property. The precedence rules for these properties are as follows:

  • If sub-totals are enabled for the data grid, you can still disable sub-totals at a specific level of grouping by setting the corresponding group description's Enable Subtotals property to False.
  • If sub-totals are disabled for the data grid, this overrides the settings in the group descriptions.

Enabling totals and sub-totals for a data grid.

Enabling totals and sub-totals for a data grid.


Other useful hints:

  • Use the data grid's Total Row Style property to change the appearance of the totals row. This includes background, foreground and font settings.
  • Use the data grid column's Total Cell Style property to change the appearance of a totals cell. This overrides the same property in the data grid's View, which in turn overrides the Total Row Style property. The Total Cell Style property lets you change foreground, background and font settings in addition to grid line settings.
  • Use the data grid View's Totals Caption property to change the caption text for the totals row. For example, you may want to change the text to ‘Average’ if the total values are actually averages (see the next section on Aggregator type).
  • By default, the caption text for the totals (or subtotals) row is displayed in the first column of the data grid. Thus, if the first column of your data grid displays numerical values, you won't see the total/subtotal value as it will be replaced by the caption text. To avoid this, set the data grid View's Show Totals Caption (or Show Sub Totals Caption) advanced property to False in order to hide the caption text.
  • If your data grid is displaying a DundasScript-calculated column, which is based on other columns, note that these dependent columns must be added to the data grid in order for totals to be displayed for the calculated column. If you don't want to show the dependent columns, you can collapse them in the data grid.
  • You can also customize totals and sub-totals in a data grid.

Aggregator type

By default, when you enable the totals row, the Aggregator Type property of each measure/metric column will be set to Sum. However, you can choose a different aggregator type on a per-column basis. The available choices include:

  • None (no aggregation will be applied to the column)
  • Average, Max, Min, or Sum (applies to numeric columns)
  • Count, First, or Last (can be used on non-numeric columns)

Aggregator type for a column.

Aggregator type for a column.


When previewing or viewing a dashboard, hover with your mouse over a total value in the data grid to see its aggregator type displayed in a tooltip. You can modify this tooltip by using the column's Total ToolTip advanced property (see the Keywords section for more details).

At viewing-time, you can also right-click over a data grid that has totals enabled to change the aggregator for any column.

Changing a column aggregator at viewing-time.

Changing a column aggregator at viewing-time.


Note: Limitation - If you have two or more data grid columns that are displaying the same data column, and you've set a different aggregator type on each column, the data grid will use the most recent aggregator for all of the columns.

Customizing totals and subtotals

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 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). See this article for an example of how to set up custom totals and subtotals via scripting.

In Dundas Dashboard 3.0.4 or later, there is one scenario where Dundas Dashboard will automatically set up the scripting to calculate a custom total and subtotals. If you add a KPI or dataset that contains a DundasScript-calculated column/measure to a data grid, and you use the Show/Replace all data option in the Data Grid Binding Setup wizard, a Row Loaded interaction script called CustomAggregations will be automatically generated for you. This script computes the custom total value by applying the DundasScript expression from the calculated column to the aggregated values.

Annotations

Cell annotations

A cell annotation is a user annotation that is attached to a specific cell of a data grid. You can create cell annotations while viewing a dashboard as shown in this example.

Annotating a cell in a data grid (while viewing a dashboard).

Annotating a cell in a data grid (while viewing a dashboard).


By default, a cell annotation indicator appears as a small, red triangle in the upper-right corner of the data grid cell. To change the appearance of cell annotation indicators for a data grid, go to its properties grid and expand the Cell Annotation Rectangle (advanced) property, which is located under the Other category.

Cell annotation indicator properties.

Cell annotation indicator properties.


Row annotations

When you annotate a data point in Dundas Dashboard, the annotation is actually associated with the dimension (i.e. x-axis) value. So if you are viewing a chart that displays multiple data points with the same dimension value, and you add an annotation to one of its data points, an annotation indicator will be displayed for each data point that has the same dimension value.

A bubble chart with repeating x-axis values.

A bubble chart with repeating x-axis values.


If you subsequently display the same KPI in a data grid, you will see a row annotation indicator in the top-left corner of each cell that shows the same dimension value.

A data grid showing row annotations.

A data grid showing row annotations.


To change the appearance of row annotation indicators for your data grid, go to its properties grid and expand the Row Annotation Rectangle (advanced) property, which is located under the Other category.

Keywords

The data grid supports the use of keywords within the values of its text and tooltip properties. You can also apply keywords in combination with standard .NET value formatting options. For more details, see Text Keywords.

You can use keywords within the following properties of a data grid column:

  • Data ToolTip
  • Text
  • Total ToolTip

Specifically, you can use the KPI/dataset keyword, #FIELD_NAME, or any of the following:

  • #AGGREGATORTYPE — Displays the aggregator type set for the column, used to display values in the totals row.
  • #HEADERTEXT — Displays the column header text.
  • #VALUE or #VAL — The value displayed by the column for each row.

By default, the Total ToolTip advanced property of a data grid column is set to the #AGGREGATORTYPE keyword, which displays the aggregator type (e.g. Sum) used by the column when the totals row is enabled.

Using keywords in tooltips

The following example shows how to add a keyword-based tooltip to a data grid column:

  1. Locate the data grid's Columns property and click its ellipsis button. The Columns property editor is displayed.
  2. Select a column from the list on the left. The column's properties appear on the right.
  3. Set the column's Data ToolTip advanced property to: Downloads for today: #NUMBER_OF_DOWNLOADS
    Data ToolTip property.
  4. Save, and then preview your dashboard.
  5. Hover over a column cell with your mouse to see its tooltip.

A keyword-based tooltip.

A keyword-based tooltip.


Formatting column text values

Consider the following data grid, which displays a ListPrice column with decimal values.

ListPrice column shows decimal values.

ListPrice column shows
decimal values.


You can use a keyword combined with a formatting string to display the ListPrice column values in currency format:

  1. Locate the data grid's Columns property and click its ellipsis button. The Columns property editor is displayed.
  2. Select the ListPrice column to edit from the list on the left.
  3. Set its Text advanced property to: #LISTPRICE{C2}
    Data ToolTip property.
  4. Save, and then preview your dashboard.

The data grid displays the ListPrice column values in the currency format as specified by the viewer's regional/language settings. For example, the figure below shows ListPrice values formatted using the regional settings of Germany.

Image

Note: This approach only handles the text formatting of the displayed values. The actual numerical values represented are unchanged. This may be an important consideration if your dashboards are being viewed by users in different regions of the world.

Related topics


Click to return to: Documentation | Designing Dashboards | DV Controls | Data Grid

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

Copyright © 2009-2014 Dundas Data Visualization, Inc.