Using a table visualization

1. Overview

A table visualization lets you display data from a metric set using a tabular view. A table is also known as a data grid or data table, and it is the default type of visualization that is used when first selecting data if you did not choose another type.

A table visualization using dashboard styling
A table visualization using dashboard styling

2. Elements of a table

The figure below shows the main elements of a table visualization.

Elements of a table visualization
Elements of a table visualization

3. Adding a table

3.1. Drag a database table or spreadsheet

One way to create a table visualization is to drag a table or sheet from under a data connector in the Explore window to the canvas of a metric set or dashboard.

This method of adding a table is mostly for previewing data because it uses the raw data retrieval mode, which has some limitations.

Dragging a native database table to the dashboard canvas
Dragging a native database table to the dashboard canvas

3.2. Drag a metric set

You can add a table to a dashboard or view simply by dragging a metric set from the Explore window that is already visualized as a table. For example, consider a metric set that is configured as follows:

  • Measures: Order Quantity
  • Rows: Date hierarchy
  • Columns: Product hierarchy

Example full screen metric set
Example full screen metric set

Drag this metric set to the dashboard canvas to see a table visualization that looks the same as the one created in the full screen metric set.

Drag a metric set to the dashboard canvas
Drag a metric set to the dashboard canvas

You can also Re-Visualize any other visualization to a Table from the toolbar. Or, add a new table to your view from Data Visualization in the toolbar, and then drag any metric set onto the empty table.

3.3. Add data

To create your own table, drag any column of data, measure, or dimension from a data connector or data cube to the canvas of a metric set or view. It will initially appear as a table visualization.

If it's numeric (a measure), it will appear as a single cell showing an aggregated value such as the sum; otherwise, it will appear as a single column of hierarchy values.

Drag Order Quantity to the canvas
Drag Order Quantity to the canvas

Now drag more data anywhere on the table, or onto the Data Analysis Panel.

Dundas BI may re-visualize your data according to its recommendations if you haven't chosen a visualization yet. To keep using a table, you can drag data onto one of its labeled areas, such as Row Header for non-numeric data like country names.

Drag Country onto Row Header
Drag Country onto Row Header

Otherwise, if the visualization changes you can always choose the highlighted Re-Visualize button in the toolbar and change it back to Table. You can also choose a table ahead of time from Re-Visualize or Data Visualization in the toolbar.

Order quantity by continent/country
Order quantity by continent/country

These steps create a metric set, which determines which data to display and data-related settings. On a view such as a dashboard, this creates an auto-generated metric set, which you can find listed under your dashboard in the Explore window.

4. Row headers versus flat columns

Non-numeric data is usually displayed as row headers, but can also be displayed as a 'flat' column like numeric data. See the table elements diagram to compare them.

  • Row headers are always displayed on the left (and frozen there when scrolling horizontally), and they display how data is grouped. Viewers can drag and drop their column headers to re-order them and change the grouping, and expander buttons can expand or collapse values. The order of these columns is maintained when sorting unless you choose to change it.
  • Flat columns have a wide variety of customization options. They can be placed in any order (to the right of any row headers), and dragging and dropping their column headers is unrelated to how the data is grouped. When sorting non-numeric data in a flat column, it is automatically grouped so that it takes priority for sorting.

4.1. Flat columns

To change how non-numeric is displayed, you can right-click a row header column, select Re-Visualize, and then Display As Flat Column

Display as a flat column
Display as a flat column

Similarly, you can select Display As Row Headers on a flat column.

Display as row headers
Display as row headers

Alternatively, open the Visualization tab of the Data Analysis Panel and move a hierarchy from Row Header to Column or vice versa.

Tip
Hold Shift on the keyboard while dragging to move it rather than copy.

4.2. Flat table

To quickly change the entire table to flat columns, select the table and find Display As Flat Table in the toolbar under Re-Visualize (or directly in the toolbar in previous versions).

Changing a table with row headers to a flat table
Changing a table with row headers to a flat table

For a table with flat columns, you can choose Display With Row Headers.

Changing a table with flat columns to use row headers
Changing a table with flat columns to use row headers

Note
This re-visualizing affects the entire table and can reset column ordering and properties.

5. Table properties

Some important properties for customizing your table can be accessed from the toolbar, such as row header layout, how to display totals, and auto-fitting of columns.

Table properties in the toolbar
Table properties in the toolbar

You can use the Quick Access Properties popup to customize properties such as colors and fonts. The popup that initially appears customizes the entire table.

To customize a specific element, such as row header or alternating row backgrounds, click the table again on that element.

Customizing row header backgrounds from the popup
Customizing row header backgrounds from the popup

Users with access to the Properties window can customize every aspect of the table's appearance and layout. Some key properties are described in the sections below. 

Main table properties
Main table properties

Tip
In the Properties window, hover over (or long-tap) a property to see its description in a tooltip popup.

5.1. Column properties

Regular columns or 'flat' columns have a wide variety of settings that can be used to customize them.

Select a column to customize in the Properties window in the Main tab, under Columns. You can also click directly on a column in the table, or right-click and choose Column Properties.

Click LineTotal to customize the corresponding measure column
Click LineTotal to customize the corresponding measure column

Note
Row Header Columns are displayed differently and have fewer settings as they are primarily for indicating the data grouping. You can choose to display data as flat columns instead of row header columns.

5.1.1. Column type

By default, flat column values are displayed as text, but you can change the column type to one of the other supported column types:

  • Image
  • Hyperlink
  • State Indicator
  • Bullet Graph
  • Data Bar
  • Checkbox

For example, change the Column Type property to Data Bar. The table now shows data bars in the LineTotal column instead of text values.

Change the column type to Data Bar
Change the column type to Data Bar

Instead of using the Properties window, you can also change the column type from the context menu.

Right-click the table column to open its context menu, click Re-Visualize, and then select the new column type (for example, Bullet Graph Column).

Change the column type to Bullet Graph by re-visualizing
Change the column type to Bullet Graph by re-visualizing

Some of the column's properties in the Properties window will be specific to the selected column type. For example, you can further customize State Indicator, Data Bar, and Bullet Graph columns by clicking Customize....

Data Bar and Bullet Graph columns also have the property Show Axis in The Header, which replaces the column header text with an axis and can be further customized by clicking Customize axis.

LineTotal column shows bullet graphs with an axis for a header
LineTotal column shows bullet graphs with an axis for a header

To customize how measures and other data are visualized by a data bar, bullet graph, or state indicator column, click the column to select it and open its Data Analysis Panel to the Visualization tab. The same visualization options are available as for an individual chart, bullet graph, or state indicator.

5.1.2. Frozen columns

Use the Frozen property from the Main tab to display the column on the left side of the table.

Frozen column property
Frozen column property

Similar to row header columns, the position of frozen columns will not change when you scroll the table.

Note
This property is not available when using Columns in the metric set or assigning data for Column Headers.

5.1.3. State styles

You can set up states on your metric set and use State Styles on table columns to change the appearance of different cells based on conditions. The article Set up states on a metric set demonstrates this with a table visualization.

When using state styles, you can choose to apply them to the whole row instead of just the cells of that column by using the Apply State Styles To Entire Row property.

Apply State Styles To Entire Row property
Apply State Styles To Entire Row property

Note
When using column grouping with this feature, the style will be applied to the row within that column group, not the whole table.

After selecting this option, you can also choose to apply the state styles to the row headers in each row by selecting the property Apply State Styles To Row Headers that appears.

Apply State Styles To Row Headers property
Apply State Styles To Row Headers property

This will apply to row headers that do not span multiple rows. For row headers that span multiple rows, consider setting up color rules on the column in the table's Row Header Columns property instead.

5.1.4. Color rules

You can change the color of cells in the column based on the data in each cell. Go to the Look tab of the column properties and locate the Color Rules property under Cell Style.

Color Rules property for a table column
Color Rules property for a table column

There are three types of color rules you can apply:

  • Auto Color Rule - Used to automatically determine colors and corresponding ranges based on the values in the cell. This rule can be applied only to measure columns. Click the + button to add to a measure column.
  • Range Color Rule - Used for setting up each range of values and its corresponding color yourself. This rule can be applied only to measure columns. Use the menu icon to the right of the property to choose this rule type.
  • Discrete Color Rule - Used to set up the corresponding color for a hierarchy member. Click the + button to add a rule for each value of a hierarchy column.

Select the added color rule to further customize the applicable range or values, color, and legend text. Color rule properties are the same in all Dundas BI visualizations - see Apply colors to data points or series using color rules for more details and examples.

An Auto Color rule applied
An Auto Color rule applied

Tip
There is a similar Color Rules property in the table's Row Header Columns that supports adding a discrete color rule for each value.

5.1.5. Custom text

Go to the Text tab of the column properties to see text-related properties of the column.

Custom Text property for a table column
Custom Text property for a table column

Under Rows, the Text property is read-only and indicates what data this column is connected to. You can use the Header Text property to change the text displayed in the column header. This property supports regular text, as well as hierarchy keywords, such as [Product], to refer to a hierarchy placed on Columns in the metric set.

If you want to change the text displayed in the cells of this table column, use the Custom Text property. By default, this property is unset, which means it will display the data connected to this column. You can use a placeholder keyword consisting of the data element name and optional format string (see Display date only on column instead of date/time for an example). For information on keywords, see the Formatting text article.

Note
Custom text and formatting set up in visualization properties are not included when exporting to Excel, but the metric set format settings are.

5.1.6. HTML formatting

If your data contains HTML tags, which you want to apply for formatting rather than display as text, in the Text tab of the column properties, select the Allow HTML Formatting option.

Allow HTML formatting
Allow HTML formatting

5.1.7. Custom groups

Go to the Text tab of the column properties and indicate a Group Text to create a custom group with the provided header.

Create a custom group
Create a custom group

Add the same Group Text to other columns to group them together.

Scroll down to the Group Header Style section to customize the text in the group header.

Change the group header text style
Change the group header text style

Go to the Look tab of the column properties to customize the look of the group header.

Change the group header style
Change the group header style

Note
The Right Border Color of the group header will take precedence over the Left Border Color of the following group header.

5.1.8. Tooltip

When you hover over a table cell in View mode, a tooltip appears which shows the hierarchy and measure values associated with that cell/data point. You can customize the text of this tooltip as follows.

Go to the Text tab of the column properties to see the Tooltip property, which contains regular text and placeholders or keywords for inserting measure and hierarchy values.

Tooltip property
Tooltip property

5.2. Column headers

The Main tab of the table's properties contains several options determining which column headers should be displayed.

Use the Look tab instead to customize their appearance, including rotated column header text.

5.2.1. Redundant column headers

A table with a hierarchy placed on Columns in the metric set (e.g., Product) will repeat the column headers for each columns hierarchy value (e.g., repeat OrderQty for each Product value).

This is displayed for consistency even if there is only one column while you perform your analysis, and this single column header is repeated in this case.

Redundant column headers displayed
Redundant column headers displayed

You can hide these column headers by unchecking the Show Redundant Column Headers property.

Redundant column headers hidden
Redundant column headers hidden

The above functionality will work even if two column headers have been previously merged into a single column header via the Allow Column Header Merging property.

5.2.2. Merging

When your table has two adjacent columns with the same header text, you can merge the text into one header by expanding Column Headers and selecting Allow Column Header Merging.

For example, if your table has two Sales columns, one displaying text values and the other showing state indicators, you can use this property to merge the headers into a single Sales header.

Allow Column Header Merging property
Allow Column Header Merging property

5.2.3. Column hierarchy headers

When working with data organized into columns, you may want to identify each row of column hierarchy headers.

In the Main tab, select the Show Column Hierarchy Headers property.

Show Column Hierarchy Headers property
Show Column Hierarchy Headers property

Under the Column Hierarchy Headers property that appears, select a hierarchy to customize how its headers appear.

5.3. Expanders

In the Main tab under Row Header Columns, you can select a column and then the Hide Expander option to hide the expander buttons on row headers.

There are other options in the table's properties under Interactivity. Uncheck Allow Collapsing Separate Hierarchies to show expander buttons only when displaying an upper level from a multi-level hierarchy. Select the Show Expanders Only On Hover option if you want to show expander buttons only when hovering over them or after tapping their cell.

To change the color of the expander buttons in row headers, go to the Look tab, and in either the Row Header Style or Column Header Style properties set the Expander Color.

Expander Color property
Expander Color property

5.4. Sorting and filtering

You can hover over column headers in view mode to access sorting and filtering options. 

Click the icon that appears to view the Sort and Filter menu.

Sort and Filter popup
Sort and Filter popup

You can choose to show only the Sort options or only the Filter options in this popup by unchecking Show Filtering Options or Show Sorting Options, found under Sorting & Filtering in the Main tab.

Hide the menu altogether by unchecking Show Sort & Filter Menu.

Sort and Filter menu properties
Sort and Filter menu properties

Note
For more information about sorting, see the article on Sorting a table visualization.

You can use an icon over column headers to indicate when filtering is applied. 

Column filter indicator
Column filter indicator

Choose how this indicator appears for filtered columns using the Indicate Column Filtering property:

  • On Hover – A filter indicator will appear when hovering the mouse cursor over a filtered column or after tapping it.
  • When Filtered by Viewers (default) – A filter indicator will appear on its own, but only if the filtering was applied by viewers of a checked in metric set (not while checked out to you).
  • Always – A filter indicator will always be present for a filtered column.

Indicate column filtering
Indicate column filtering

5.5. Totals

The settings that determine which totals are displayed are on the metric set, for example to show them only for some hierarchies, or only on rows or columns.

When totals are enabled, the table's properties described below can customize where they're displayed. To customize their appearance, see the total style properties in the Look tab.

5.5.1. Freeze total rows

Use the Freeze Total Rows property from the Main properties tab to display the grand total and subtotals at the edge of the table, even when scrolled out of view.

Freeze Total Rows property
Freeze Total Rows property

5.5.2. Show totals at bottom

You can decide between showing total rows at the top or bottom (and total columns left or right) by selecting or unchecking the Show Totals At Bottom property in the Main properties tab.

Show Totals At Bottom property
Show Totals At Bottom property

5.5.3. Item counts in subtotals

When working with any table that includes subtotals, you can hover over its cell to view the number of items that made up that subtotal amount. Subtotals are displayed if enabled in tables with multiple hierarchies or multiple levels, including when a member is expanded.

Subtotal tooltips show the number of items
Subtotal tooltips show the number of items

This can be helpful as context for the subtotal amounts when doing analysis.

If you would prefer to display this item count in the cell text of the table itself, enable the Show Item Counts In Subtotals property, found in the Text tab for the table.

Show the item counts in subtotal text
Show the item counts in subtotal text

5.6. Look

The Look tab of the table's properties is the main place for customizing the appearance of each part of the table.

Some appearance properties are also available on individual columns, which take priority when set.

Tip
Dundas BI provides very different built-in table styles in the Styles > Shared > Dundas folder and in other folders, which you can apply as a shortcut.

5.6.1. Column header style

Use the Column Header Style to change the appearance both for the 'last row' of column headers displayed directly above your rows of data, and additional column headers displayed above when a hierarchy is placed on Columns in the metric set.

Use the tabs to access all the options. In the Text tab, you can enable Rotate Column Header Text and set a rotation angle.

Rotate column header text
Rotate column header text

5.6.2. Total styles

To change the appearance of rows or columns containing totals, go to the Look tab of the table properties and use the total or grand total style options.

  • Total Row Style - Change the appearance of rows containing the grand total and subtotals, split between row header styling under Headers and the rest of the row under Rows.
  • Grand Total Row Style - Change the appearance of the whole row containing the grand total. These properties take precedence over the Total Row Style.
  • Grand Total Column Style - Change the appearance of the grand total column, split between column header styling under Headers and the rest of the column under Rows.

Change the styling for totals
Change the styling for totals

5.6.3. Corner area style

The corner area of a table visualization is the portion that is above the row headers and to the left of the column headers, used to display headers identifying each column of row headers. To edit the corner area properties, in the Properties window go to the Look tab's Headers section, and click Corner Area Style.

Corner Area Style
Corner Area Style

For example, set the Text Align property to Center in order to position the text in the center horizontally.

Set Text Align property to Center
Set Text Align property to Center

Set the Vertical Text Align property to Top in order to position the text at the top.

Set Vertical Text Align property to Top
Set Vertical Text Align property to Top

5.6.4. Alternating row style

To change the appearance of alternating rows in a table, in the Properties window go to the Look tab's Rows section, and click the Alternating Row Style.

Click the Alternating Row Style
Click the Alternating Row Style

Change the background color of the alternating rows, for example.

Changing the appearance of alternating rows
Changing the appearance of alternating rows

5.6.5. Cell note style

When you add a note (annotation) on a table cell, the note appears as a small, red triangle in the top right corner of the cell. Hover over this triangle indicator in View mode to see the details of the note.

Hover over a cell note to see its details
Hover over a cell note to see its details

To customize the appearance of the cell note, go to the Look tab of the table's properties. Click Cell Note Style.

Click Cell Note Style
Click Cell Note Style

This lets you adjust the size and color of the cell note.

Cell note style properties
Cell note style properties

5.6.6. Animate cell value changes

When a measure value (number) changes, its cell can be animated for a brief duration. These changes could occur as a result of filtering or other interactions, or from changes in the underlying data when it's set up to automatically refresh, where the measure value corresponding with a particular hierarchy value changes (not when the hierarchy values change).

Go to the Look tab of the table properties and scroll down to find the Animation section. Enable the property Animate Cell Value Changes and select the type of animation to use:

  • Highlight temporarily changes the background color of the changed cell. This animation type comes with the option to select the highlight color.
  • Slide moves the values in the direction of the change. Values that go up will slide up, and values that go down will slide down.

Enable the Animate Cell Value Changes property
Enable the Animate Cell Value Changes property

Switch to View mode and effect a change in the data to see the cell value animation. For example, a measure value was corrected below to increase sales for Egypt to $6,000.

Cells that changed value are animated
Cells that changed value are animated

5.7. Layout

5.7.1. Row header layout

In the Layout tab under Table, you will find the Row Header Layout property. By default, this is set to Columns, which means each hierarchy or hierarchy level has a separate column with its own column header.

Row headers laid out as separate columns
Row headers laid out as separate columns

When the row header layout is set to Indented, row headers are displayed compactly in the same column, and each hierarchy or level is indented separately within it. 

Row headers laid out as indented
Row headers laid out as indented

You can control the width of this column and the amount of indenting using the properties Single Indented Column Width and Indentation respectively.

Adjust the column width and indentation
Adjust the column width and indentation

Note that the indented option requires the following:

  1. Totals must be enabled on the metric set. To find this setting, click the Edit button in the Data Analysis Panel and set Shown Totals. The grand total can be hidden using the table's Hide Grand Total property.
  2. The table must be set to show totals at the top.

5.7.2. Auto fit columns to fill

By default, columns are automatically sized to fit their content. When you resize a table wider than is needed, you'll see an area of white space.

Auto Fit Columns To Fill is unchecked
Auto Fit Columns To Fill is unchecked

In the Properties window's Layout tab, select the Auto Fit Columns To Fill option to have the columns sized to fit the table's available width.

Auto Fit Columns To Fill is checked
Auto Fit Columns To Fill is checked

6. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri