Using a table visualization
- Elements of a table
- Adding a table
- Row headers versus a flat table
- Table properties
- Allow column header merging
- Apply state styles to the entire row
- Alternating row style
- Auto fit columns to fill
- Cell note style
- Corner area
- Expander color
- Highlight cell value changes
- Row header layout
- Show column headers
- Show column hierarchy headers
- Show expanders only on hover
- Show redundant column headers
- Show the sort and filter menu
- Column properties
- Total rows
- 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 control that is used in the Metric Set Designer.
Related video: Introduction to Data Tables
2. Elements of a table
The figure below shows the main elements of a table control.
3. Adding a table
3.1. Drag a native database table
One way to add a table control to your dashboard is by dragging a native database table from a data connector in the Explore window, and placing it directly onto the dashboard canvas. This method of adding a table is mostly for previewing data because it uses the Raw Data retrieval format 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 axis: OrderQty
- ROWS axis: OrderDate hierarchy
- COLUMNS axis: Product hierarchy
Drag this metric set to the dashboard canvas to see a table control that looks the same as the one in the Metric Set Designer.
3.3. Drag measure and dimension columns
The third way to add a table is to drag a measure column from a data connector structure or data cube to the canvas. The measure will appear as a table visualization with a single cell showing an aggregated value.
Then drag other columns (e.g., representing dimensions) onto the table.
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.
4. Row headers versus a flat table
By default, non-numeric data is displayed in expandable row header columns on the left, while numeric columns are grouped on the right. You can change this by opening the Visualization tab of the Data Analysis Panel and moving a dimension from the Row Header field to the Column field. When all of the dimensions are in the Column field, the table mode is called a Flat Table.
4.1. Display with row headers
To quickly change to this mode, click Display With Row Headers in the toolbar or context menu for a selected table.
In View mode, row header cells display tooltips showing the cell value preceded by the level caption name.
4.2. Display as a flat table
To quickly change to this mode, click Display As Flat Table in the toolbar or context menu for a 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. A few of the basic properties are described below to get you started.
5.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.
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.2. 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.3. Alternating row style
To change the appearance of alternating rows in a table, go to Properties \ Look \ ROWS and click the Alternating Row Style link.
Change the background color of the alternating rows, for example.
5.4. Auto fit columns to fill
By default, columns are automatically sized to fit their content. So when you resize a table wider than is needed, you'll see an area of white space.
Set the Auto Fit Columns To Fill property to True to have the columns sized to fit the table's available width.
5.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.
To customize the appearance of the cell note, go to the Look tab of the table's properties. Click the Cell Note Style link.
This lets you adjust the size and color of the cell note.
5.6. 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. To edit the corner area properties, go to Properties \ Look \ HEADERS and click the Corner Area Style link.
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.6.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 panel, select Measure Column.
Click the field under Header Text and type your header title.
Use the Expander Color property under Row Header Style properties to change the color of the expander buttons for row headers.
For column header expanders, go to Column Header Style properties to find the Expander Color property.
5.8. Highlight cell value changes
When underlying data changes in value, and corresponding cells in a table change value, the changed cells can be animated with a highlight color for a brief duration.
To use this feature, open the Data Analysis Panel for the table visualization and click Edit. Make sure the Bypass Data Cache option is checked.
Go to Properties for the table visualization and set a value for the Data auto refresh interval (seconds) property, such as 10 seconds.
Then go to the Look section and scroll down to find the Highlight Cell Value Changes property. Enable it and optionally change the highlight color or duration of the animation (i.e., duration for the highlight color to appear and then fade away).
Switch to View mode and effect a change in the data to see the cell value animation. The cell(s) will be highlighted in color which then fades away after the specified duration.
5.9. Row header layout
Under Layout \ Table, you will find the Row Header Layout property. By default, this is set to Separate Columns, which means that when you expand a row header, 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. Open the Data Analysis Panel, click Edit, and then look for the Shown Totals field. If you want to hide them, use the Hide Grand Total property.
- The table's Show Totals At Bottom property must be unchecked.
5.10. Show column headers
By default, a table displays headers above the columns. To hide all column headers, uncheck the Show Column Headers property under the MAIN \ HEADERS section.
5.11. Show column hierarchy headers
When working with data organized into columns, you may want to identify each row of column hierarchy headers.
Under the Main \ Headers section, select Show Column Hierarchy Headers.
Under the Column Hierarchy Headers section that appears, select a hierarchy to customize its text further.
5.12. Show expanders only on hover
Under the Main \ Headers section, select the Show Expanders Only On Hover option if you want to show the expand and collapse buttons for row headers only when hovering over them or after tapping.
A table with a COLUMNS hierarchy such as Product will repeat a single column header (e.g., OrderQty) for each columns hierarchy value (e.g., for each Product value). This results in a row of redundant column headers which you can 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.14. Show the sort and filter menu
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.15. Column properties
You can customize column-specific properties under Main \ Table \ Columns. Select the column you want to customize (e.g. LineTotal).
By default, measure column values are displayed as text. But you can change the column type to something else.
Change the Column Type property to Data Bar. The table now shows data bars in the LineTotal column instead of text values.
Supported column types include:
- State Indicator
- Bullet Graph
- Data Bar
Instead of using the Properties window, you can also change the column type from the canvas. To do this, right-click over the table column you want to change.
From the menu, click Re-Visualize, and then select the new column type you want (e.g., Bullet Graph Column).
The LineTotal column now shows bullet graphs.
5.15.2. Frozen column
Use the Frozen property from the Main column properties tab 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 other columns in your 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 Color Rules under CELL STYLE.
There are three types of color rules you may apply:
- Auto Color Rule - used to apply a range of colors based on the value in the cell. This rule can be applied only to measure columns. Click the plus under Color Rules to add an Auto Color Rule.
- Range Color Rule - used to apply a specific color to a range of values. This rule can be applied only to measure columns. Click the selection icon next to the plus under Color Rules, and select Range Color Rule.
- Discrete Color Rule - used to apply a specific color to a member of a hierarchy. This rule can be applied only to hierarchies displayed as flat table columns. Click the plus under Color Rules to add a Discrete Color Rule.
Select the added color rule to further customize the applicable range or values, color, and legend text.
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.15.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 tick the Allow HTML Formatting property.
5.15.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.
5.16. Total rows
To change the appearance of rows containing totals, go to the Look section of the table properties and use the following links:
- Total Row Style (Headers) - change the appearance of the row header cells in rows containing the grand total and subtotals.
- Total Row Style (Rows) - change the appearance of column cells in rows containing the grand total and subtotals.
- Grand Total Row Style - change the appearance of the whole row containing the grand total. These properties take precedence over any setting in the above two links.
5.16.1. Freeze total rows
Use the Freeze Total Rows property from the Main properties section to display the grand total and subtotals at the edge of the table, even when scrolled out of view.
5.16.2. Show totals at bottom
By default, a table displays totals at the top. Use Show Totals At Bottom property from the Main properties section to display the totals at the bottom instead.