416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
Dashboard v5.0

This site makes extensive use of JavaScript.

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

Data Grid Examples

Hide navigation
RSS
Modified on Wed, 30 Apr 2014 02:30 PM Categorized as Data Controls, Data Grid, Level-Intermediate
Click to return to: Documentation | Designing Dashboards | DV Controls | Data Grid


Overview

The examples below show how to accomplish various tasks using the Data Grid control, or use some of the more advanced features of the Data Grid such as cell style rules and image columns.

Columns

Checkbox columns

If you add a KPI or dataset that has a boolean column to a data grid, you can display the boolean values using read-only checkboxes by adding a checkbox column to the data grid manually from the Columns property editor.

Data grid with a checkbox column.

Data grid with a checkbox column.


Fixed columns and long text

By default, when you add a KPI or dataset to a data grid, the data grid's view is automatically configured with a fixed column count of 1. This means that the first column in the grid will always be visible even as you scroll the data grid horizontally. This is useful for setting up row headers that are always visible.

However, if you have a fixed column that displays very long text, it is possible that the remaining columns will not be visible, even when you try to scroll them into view. In this case, you can either set a fixed column width and turn on text wrapping, or set the fixed column count to zero. To do the latter, follow these steps:

  1. Select the data grid control on the canvas and go to its Properties grid.
  2. Click the ellipsis button of the data grid's View property.
  3. In the View property editor, set the Fixed Column Count property to 0.
  4. Click Close to exit the View property editor.
  5. Save, then preview your dashboard.

All of the columns of the data grid can now be scrolled into view.

Using an image column

This example shows how to add an image column to a data grid. The image column will display a picture of the product manager who is assigned to a particular product.

Data grid with an image column.

Data grid with an image column.


Consider a dataset with the following columns:

  • Product ID
  • Product Description

Consider a website (e.g. intranet site) where image files can be accessed:

  • http://www.dundas.com/ProductSite/ProductManager1.png (picture corresponding to Product ID = 1)
  • http://www.dundas.com/ProductSite/ProductManager2.png (picture corresponding to Product ID = 2)
  • http://www.dundas.com/ProductSite/ProductManager3.png (picture corresponding to Product ID = 3)
  • http://www.dundas.com/ProductSite/ProductManager4.png (picture corresponding to Product ID = 4)

Note: These are not real links - they are intended to serve as examples only.

Configure the data grid as follows:

  1. Add the dataset to the data grid.
  2. Locate the data grid's Columns property and click its ellipsis button. The Columns property editor is displayed.
  3. Click the menu button beside the Add button. From the dropdown menu, choose the Image Column type. A new column appears in the list on the left and its properties appear on the right.
  4. Set the Data Column property to the Product ID column from the dataset.
  5. Set the Data Format String property to http://www.dundas.com/ProductSite/ProductManager{0}.png. The {0} portion of this string is a placeholder that will be replaced by an actual Data Column value when the dashboard is previewed or viewed.
  6. Set the Header Text property to Product Manager, which will appear in the header of the image column.
  7. Save, then preview your dashboard.

Image column properties.

Image column properties.


Setting column widths

The initial width of each column in the data grid is determined by each column's Width property, which you can set using the property grid or through scripting.

Width property of a column.

Width property of a column.


The width values can be specified using different conventions, each corresponding to a different column sizing method:

Sizing methodDescriptionProperty grid usageScript syntax
AutoA column that uses this mode will be resized to fit the space required by its header and cells.Clear the value of the Width property, which will set it to Auto.myColumn.Width = DashboardTableViewColumnWidth.Auto;
Pixel widthSet the column width to the specified number of pixels. Set the Width property to a numeric value (e.g. 100).myColumn.Width = DashboardTableViewColumnWidth.FromPixel(100);
Star sizingThe available width will be divided proportionally between all columns using star sizing.Set the Width property to one of: *, 1*, 2*, 3*, etc. Use a higher number prefix if you want to allocate more space for a specific column relative to the others.myColumn.Width = DashboardTableViewColumnWidth.FromStar(2);
Size to cellsIn this mode, the column will occupy the width required by the column's cells.Not available from the property grid.myColumn.Width = DashboardTableViewColumnWidth.SizeToCells;
Size to headerIn this mode, the column will occupy the width required by its header.Not available from the property grid.myColumn.Width = DashboardTableViewColumnWidth.SizeToHeader;

The default width for a column is 1*, which means that the available space will be divided equally between the columns.

Cell style rules (for coloring cells)

The following example shows how to configure the Cell Style Rule property of a data grid column. A cell style rule can be used to color the cells of a column based on comparing values determined from one or two other (or the same) columns against a set of numeric ranges. The other columns must belong to the same KPI/dataset as the column to be colored.

The general concept behind cell style rules is similar to that of states.

Cell style rule for a column.

Cell style rule for a column.


To configure the cell style rule for a column:

  1. In the Columns property dialog, select the data grid column that you want to style from the list on the left (e.g. Support Tickets).
  2. Click the ellipsis button of the Cell Style Rule property. In the Cell Style Rule property dialog:
    • Set the Compare Type property to Value (this is the default).
    • Locate the Ranges property and click its ellipsis button. In the Ranges property dialog:
      • Add a range that starts at 0 and ends at 50. Then click the ellipsis button of the range's Range Style property and set the Background color to Green. This represents the fact that a smaller number of support tickets is desirable.
      • Add a range that starts at 50 and ends at 100. Then click the ellipsis button of the range's Range Style property and set the Background color to Red. This warns of a high number of support tickets.
        Configuring a range.
    • Set the Value property to the same column which is being styled.
      Setting the Value property.
  3. Save your changes, then preview the dashboard.

Cell style rule in effect.

Cell style rule in effect.


More complex calculations can be set up by using two columns instead of one. In this case, use both the Value and Comparable Value properties of the cell style rule to select the data grid columns that will provide the numeric values for the calculations. Optionally, one of these columns can be the column that is being styled. Next, choose the Compare Type, which specifies how to calculate a numeric result for each row that will be compared against the cell style rule's numeric ranges. For example, if the calculated result for a row falls between 50 and 100, then the corresponding cell should be colored Red.

Four kinds of Compare Types are supported, each performing a different calculation:

Compare TypeCalculation
Value(Value)
Ratio(Value / ComparableValue)
Variance(Value - ComparableValue)
Variance Ratio(Value - ComparableValue) / abs(ComparableValue)

If the above compare types are not sufficient (e.g. you need to perform custom calculations to derive the coloring), then have a look at these two samples, which show how to perform data grid coloring using scripting:


Note: In Dundas Dashboard 5 or later, cell style rules also apply to totals and sub-totals. In this case, since cell style rules can reference columns other than the one the cell style rule is defined on, the aggregation used is the one defined on the column.

Secondary data source columns

See this article for details on how to access the secondary data source columns on the data grid: Accessing the Datapoints from a Secondary Data Source

Grouping

Creating a grouped column header

This example shows how to create a grouped column header that spans multiple columns in your data grid.

Data grid with a grouped column header.

Data grid with a grouped column header.


To set up this data grid:

  1. Add a KPI or dataset to the data grid. In the Data Grid Binding Setup wizard, default all choices until you reach the Define columns step. De-select all columns, then click Finish to close the wizard.
  2. Locate the data grid's Columns property and click its ellipsis button. The Columns property editor is displayed.
  3. Click the menu button beside the Add button and choose the Column Group type. A new column group is added to the list on the left and its properties appear on the right.
  4. Set the Header Text property to Product Information, which will appear as the display name of the grouped column header.
    Column group properties.
  5. Click the ellipsis button of the Columns property to open a second Columns property editor. Use the editor to add four new text columns. For each column:
    • Enter its display name using the Header Text property (e.g. Product SKU).
    • Set the Data Column property by choosing a column from your KPI or dataset.
  6. Save, and then preview the dashboard.

Tip: If you are setting up multiple grouped column headers, it is possible for the headers to be mis-aligned vertically. In this case, set the Arrange Column Headers By Level property of the data grid View to True to make the column headers line up.

KPI with a splitter dimension

In the following example, suppose you have defined a KPI with a splitter dimension (e.g. by using the KPI Setup Wizard):

  • KPI measure: Number of Downloads
  • KPI dimension: Download Date
    • Axis: Yes
  • KPI dimension: Product Group Name
    • Axis: Yes
    • Splitter: Yes

KPI with a splitter dimension.

KPI with a splitter dimension.


When this KPI is displayed as a line chart on a dashboard, a series appears for each unique value of the splitter dimension.

Line chart displaying two series (one for the Audio product group, one for the Video product group).

Line chart displaying two series (one for the Audio product group,
one for the Video product group).


When this KPI is displayed in a data grid, the splitter dimension appears as a column in the grid and the appropriate grouping is applied automatically to the rows (e.g. a group description is added to the data grid's Group Descriptions property).

Data grid displaying a KPI with a splitter dimension (Product Group Name).

Data grid displaying a KPI with a splitter dimension (Product Group Name).


Customizing row group buttons

You can customize the expand and collapse icons (buttons) when using row grouping.

Row grouping expand and collapse buttons.

Row grouping expand and collapse buttons.


Use the Row Group Button properties of the data grid View to change the size of the buttons or replace them with your own images.

Row group button properties.

Row group button properties.


States

Displaying KPI states

In this example, consider a KPI that is defined as follows:

  • KPI measure: Number of Downloads
  • KPI instant dimension: Product SKU
  • KPI state group: State Group 1, which has three states

KPI state group with 3 states.

KPI state group with 3 states.


If you add this KPI to a data grid, the data grid will be configured automatically with a state indicator column by default (via the Data Grid Binding Setup wizard):

Data grid with a state indicator column.

Data grid with a state indicator column.


You can access the properties for this column by using the Columns property to open the Columns property editor. Expand the State Indicator property to see the properties of the state indicator control:

Properties of the state indicator column.

Properties of the state indicator column.


Sorting on a state indicator column

To enable sorting on the state indicator column:

  1. Locate the data grid's Columns property and click its ellipsis button. The Columns property editor is displayed.
  2. Select the state indicator column from the list on the left.
  3. Click the Show advanced properties button at the top of the properties list.
  4. Set the Sort Column property to the same value as the Data Column property.
  5. Click Close.
  6. Save, then preview and test your dashboard.

You can now sort the data grid rows based on the state indicator column by clicking its header.

Adding a state indicator column manually

This example shows how to add a state indicator column manually to your data grid.

A state indicator column for a dataset-driven grid.

A state indicator column for a dataset-driven grid.


Consider a dataset with the following columns:

  • Product Description
  • Product SKU
  • Number of Downloads (numeric values between 0 and 100).

To add a state indicator column to your data grid:

  1. Drag the dataset and drop it over your data grid. Accept the default choices in the Data Grid Binding Setup wizard.
  2. Locate the data grid's Columns property and click its ellipsis button. The Columns property editor is displayed.
  3. In the Columns property editor:
    • Click the menu button beside the Add button. From the dropdown menu, choose the State Indicator Column type. A new column appears in the list on the left and its properties appear on the right.
    • Click the Show advanced properties button at the top of the properties list.
    • Set the Data Column property to the Number of Downloads column from the dataset.
    • Set the Sort Column advanced property to the same value as the Data Column property.
    • Set the Header Text property to Evaluation. This will appear as the header text for the state indicator column.
      State indicator column properties.
    • Expand the State Indicator property and locate its Indicators property. Click its ellipsis button to open the Indicators property editor.
      Indicators properties.
  4. In the Indicators property editor:
    • Locate the States property and click its ellipsis button. The States property editor is displayed.
      States properties.
  5. In the States property editor:
    • Click the Add button below the list on the left. A new state is added to the list and its properties appear on the right.
    • Set the Start Value property to 50.
    • Set the Shape Fill property to Green.
    • Set the Skinned Shape property to Checkmark.
      Good state properties.
    • Click the Add button below the list on the left to add a second state.
    • Set the End Value property to 50.
    • Set the End Value Exclusive property to True.
    • Set the Shape Fill property to Red.
    • Set the Skinned Shape property to CheckCross.
  6. Close the property editors.
  7. Save, then preview your dashboard.

Misc

Text wrapping in column headers and cells

If your data grid column has a fixed width, you can turn on text wrapping within its header or a cell as follows:

  1. Select the data grid control on the canvas and go to its Properties grid.
  2. Click the ellipsis button of the data grid's Columns property.
  3. In the Columns property editor, select your data grid column in the list on the left.
  4. In the properties grid for the column:
    • Set the Header Text Wrapping property to Wrap.
    • Set the Cells Text Wrapping property to Wrap.
  5. Optionally, change the text alignment as desired using these properties:
    • Header Text Alignment
    • Cells Text Alignment
  6. Click Close to close the Columns dialog.
  7. Save, then preview your dashboard.

Text wrapping and alignment properties.

Text wrapping and alignment properties.


Grid with text wrapping enabled.

Grid with text wrapping enabled.


Formatting data

If your data grid column displays numerical data from a KPI or dataset, you can apply a format string to the column in order to display formatted data values. For example, if the data values are too large (e.g. over one million), you can specify a custom numeric, conditional formatting string to display the values in a more compact way.

Example:

  1. Select the data grid control on the canvas and go to its Properties grid.
  2. Click the ellipsis button of the data grid's Columns property.
  3. In the Columns property editor, select your data grid column in the list on the left.
  4. In the properties grid for the column, click the ellipsis button of the Data Format String property. The Format Text dialog is displayed.
  5. Use the Format Text dialog to set the formatting string for the column.
  6. Click Close to close the Columns property editor.
  7. Save, then preview your dashboard.

Data Format String property for a data grid text column.

Data Format String property for a data grid text column.


Formatted data.

Formatted data.


If the data grid text column is showing values from a time dimension (or an OLAP dimension with date mapping), the date-time values will be displayed using the formatting defined by the time dimension. However, you can override this by specifying your own date format via the text column's Data Format String property. The only caveat is that the formatting will be fixed, and won't change depending on the time dimension level anymore.

The data grid uses default format strings based on data structure column types. For example, numeric columns get the format string "n". However, if your data comes from a stored procedure that returns NULLs for a column, the data grid may assume it is a numeric column and format it as such. In this case, you can change the format string to display the column the expected way (e.g. as a date), or make sure your stored procedure returns non-null values for the column.

See also: DashboardDataGridTextColumn Class

Exporting data

Notes on exporting data from a data grid:

  • If a cell value is specified using its column's Text property (with keywords or not), the exported value will always be textual, as opposed to the case when the cell value is specified using its column's DataColumn property.

  • If your data grid uses group descriptions/grouping, you can set the Expand Groups on Export advanced property to True (the default setting). This will force the data grid's groups to be expanded before the data is exported (so that group details will be exported as well). After the export, the data grid's groups will remain in the expanded state. If you set the Expand Groups on Export property to False, the exported data will mirror the state of the groups before exporting (i.e. if a group is collapsed, it's details won't be exported). After exporting, the state of the data grid's groups will be unchanged.

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.