Using a table visualization
- Elements of a table
- Adding a table
- Row headers versus flat columns
- Table properties
- Apply state styles to the entire row
- Alternating row style
- Auto fit columns to fill
- Cell note style
- Corner area
- Animate cell value changes
- Row header layout
- Column headers
- Sorting and filtering
- Column properties
- See also
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.
2. Elements of a table
The figure below shows the main elements of a table visualization.
3. Adding a table
3.1. Drag a native database table
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.
3.2. Drag a metric set
Another way to add a table is to simply drag 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: OrderQty
- Rows: OrderDate hierarchy
- Columns: Product hierarchy
Drag this metric set to the dashboard canvas to see a table visualization that looks the same as the one in the full-screen metric set editor.
3.3. Drag measure and dimension columns
The third way to add a table is to drag any column of data, measure, or dimension from a data connector structure or data cube to the canvas of a metric set or dashboard. If it's numeric (a measure), it will appear as a table visualization with a single cell showing an aggregated value; otherwise it will appear as a single column of hierarchy values.
Now drag more data anywhere on the table, or onto the Data Analysis Panel.
This way of dynamically building your table results in the creation of an auto-generated metric set, which you'll see if you expand your dashboard item in the Explore window.
Non-numeric data can be displayed either as row headers, or a 'flat' column like numeric data.
- Row headers are always displayed on the left, and display how data is grouped. You can drag and drop their column headers to re-order them and change how the data is grouped, and expander buttons can be displayed to allow viewers to expand or collapse individual values. The order of row header 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.
By default, non-numeric data is displayed in row header columns on the left, while numeric columns are displayed on the right. You can change this by opening the Visualization tab of the Data Analysis Panel and moving a dimension from Row Header to Column.
Alternatively, you can right-click the column and select Display As Flat Column from the Re-Visualize menu.
And similarly select Display As Row Headers to return to the default.
In View mode, row header cells display tooltips showing the cell value preceded by the level caption name.
4.1. Display as a flat table
To quickly change all the row headers columns to flat mode, click Display As Flat Table in the toolbar or context menu for the selected table.
4.2. Display with row headers
To quickly change to the default mode, click Display With Row Headers in the toolbar or context menu for the selected table.
5. Table properties
Using the Properties window, you can customize everything from the style of table rows to the appearance of notes (annotations) added to cells. The most common properties are described below.
5.1. Apply state styles to the entire row
When using state styles, you can choose to apply them to the whole row instead of a single cell by using the Apply State Styles To Entire Row property.
For example, rather than setting up two separate conditions to indicate when your sales are above target use this property to style the whole row.
5.1.1. Apply state styles to row headers
When using the Apply State Styles to Entire Row property, you can choose to apply the state styles to the row headers as well.
After selecting the Apply state styles to entire row property, the Apply State Styles To Row Headers property is displayed. Select it to apply the same state styles to the row headers.
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.2. 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.
Change the background color of the alternating rows, for example.
5.3. 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.
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.
5.4. 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.
To customize the appearance of the cell note, go to the Look tab of the table's properties. Click Cell Note Style.
This lets you adjust the size and color of the cell note.
5.5. Corner area
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.
For example, set the Text Align property to Center in order to position the text in the center horizontally.
Set the Vertical Text Align property to Top in order to position the text at the top.
5.5.1. Corner header text
When the table is transposed (i.e., the Transpose Result option is selected on the metric set), the corner area will be blank. You can add custom text to function as the header.
From the Properties window, select Measure Column.
Click the field under Header Text and type your header title.
5.6.1. Expander color
Use the Expander Color property in the Row Header Style properties to change the color of the expander buttons for row headers.
For column header expanders, go to the Column Header Style properties to find the Expander Color property.
5.6.2. Show expanders only on hover
In the Main tab, under Interactivity, select the Show Expanders Only On Hover option if you want to show the expand and collapse buttons on row and column headers only when hovering over them or after tapping.
5.6.3. Hide expanders
In the Main tab, under Row Header Columns, select the column and click the Hide Expander option to hide the expander buttons on row headers.
In the Main tab under Interactivity, there is also a property named Allow Collapsing Separate Hierarchies, which can be unchecked to show expander buttons only for upper levels of a multi-level hierarchy rather than for any two hierarchies placed on Rows in the metric set.
5.7. Animate cell value changes
When the data a table cell changes value, the 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.
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.
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.
5.8. Row header layout
In the Layout tab under Table, you will find the Row Header Layout property. By default, this is set to Separate Columns, which means that when you select multiple hierarchies or levels, or expand a member, the headers corresponding to different hierarchy levels will appear in separate columns.
When the row header layout is set to Single Indented Column, row headers are displayed compactly in the same column and indented according to the corresponding hierarchy level.
You can control the width of this column and the indentation for each hierarchy level using the properties Single Indented Column Width and Indentation respectively.
Note that the Single Indented Column option requires the following:
- 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 Hide Grand Total property.
- The table's Show Totals At Bottom property must be unchecked.
5.9. Column headers
5.9.1. Allow column header merging
When your table has two adjacent columns with the same header text, you can merge the text into one header by using the Allow Column Header Merging property under Column Headers.
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.
5.9.2. Show column headers
By default, a table displays headers above the columns. To hide all column headers, uncheck the Show Column Headers property in the Main tab under 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). If there is only going to be one column, this results in a row of redundant column headers that you may want to hide by unchecking the Show Redundant Column Headers property.
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.9.4. Show 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.
Under the Column Hierarchy Headers property that appears, select a hierarchy to customize how its headers appear.
5.9.5. Rotate column header text
When working with multiple thin columns, you may want to rotate the text in the column headers.
In the Look properties for the table, click Column Header Style.
In the Text tab, enable the Rotate Column Header Text property and indicate a rotation angle.
5.10. Sorting and filtering
By default, 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.
You can choose to show only the Sort options by unchecking Show Filtering Options from the Main properties tab.
Similarly, you can show only the Filter options by unchecking Show Sorting Options, or hide the menu altogether by unchecking Show Sort & Filter Menu.
5.10.1. Filter indicator
You can include an indicator on columns, which will appear when filtering is applied.
You can select when this indicator appears using the Indicate Column Filtering drop-down menu:
- On Hover – A filter indicator will appear when hovering the cursor over a filtered column.
- When Filtered by Viewers (default) – A filter indicator will appear only if the filtering was applied by viewers, which includes user personalization and the Share Link functionality.
- Always – A filter indicator will always be present on the filtered column.
5.11. Column properties
Regular columns or 'flat' columns have a wide variety of settings that can be used to customize them. You can find these in the Properties window in the Main tab, under Columns. Select the column you want to customize (e.g., LineTotal).
By default, flat column values are displayed as text, but you can change the column type to one of the other supported column types:
- State Indicator
- Bullet Graph
- Data Bar
For example, change the Column Type property to Data Bar. The table now shows data bars in the LineTotal column instead of text values.
Instead of using the Properties window, you can also change the column type from the context menu. Right-click the table column to open the context menu.
From the context menu, click Re-Visualize, and then select the new column type you want (for example, Bullet Graph Column).
Some of the properties in the column 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.
5.11.2. Frozen column
Use the Frozen property from the Main tab to display the column on the left side of the table.
Similar to row header columns, the position of frozen columns will not change when you scroll the table.
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.
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.
Go to the Text tab of the column properties to see text-related properties of the 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.
5.11.5. 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.
5.11.6. 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.
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.
Go to the Look tab of the column properties to customize the look of the group header.
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.
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 (Headers) and the rest of the row (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 (Headers) and the rest of the column (Rows)
5.12.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.
5.12.2. Show totals at bottom
By default, a table displays totals at the top. Use the Show Totals At Bottom property from the Main properties tab to display the totals at the bottom instead.
5.12.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.
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.