Creating a Table

After selecting ‘Table’ on the Chart tab you can choose which data objects you want to use on the Data tab. Initially the fields section will show a Header field (please refer to the section on Crosstabs for more information) and one Data Column field. You will only be able to add Dimensions into the Header section, but you can add Measures or Dimensions into a Data Column. Data objects are added to the fields in the same way as they are for a Chart, please refer to Adding data to a chart for more information.

Every time a data object has been added to a field, a new field will be automatically generated.

Changing the order of the columns

The data objects will be displayed in the table in the order they were added to the fields. To change the order, click on the data object that you want to move and click the Up or Down arrows.

Repeat this process until the column is in the correct position.

Removing data

To remove columns from a table, click the  icon beneath the relevant column.

Editing a Table

To add additional columns, click the  icon.

An extra field will be inserted beneath the highlighted field.

Example

In the following example, a new column will be inserted between ‘Job Role’ and ‘Employee Count’.

The ‘Editor Max Rows’ field at the top-right of the ‘Edit Chart’ screen can be used to set the number of rows displayed whilst editing a chart, this can be a useful way of seeing how a table will look without it having to display every row of data. To display all rows, leave this field blank.

Pagination

This section can be used to control how many rows of data are displayed on each page in the table. This can make the table quicker to load on the dashboard and will provide users with a navigation option to scroll through the different pages. If the table is exported to CSV all rows will automatically be exported to the same CSV file.

 Enter the relevant number into the Pagination field.

When Pagination has been added to a table, a navigation option will be available on the dashboard which will allow users to scroll through the different pages.

The single right and left arrows are used to move through the table one page at a time.

The double arrows are used to move to first page in the table. 

Table Filters

Filters are added to a table in the same way as they are to a chart, please refer to section for more information.

Table Sorting

You can sort the data in table columns in the same way as you sort data in a chart, please refer to Sorting your Chart for more information.

Table Attributes

The attributes applicable to Measures and Dimensions differ slightly but this section will cover each of them.

Dimensions

• Show Dimension Sub Total Break - this allows you to add a sub total at each break in dimension value. For example, if the dimension contains a list of Regions and you want to see how much money each region generates, applying a ‘Dimension Sub Total Break’ will display a sub total each time the region changes.

Measures

• Make the column invisible - the column is not displayed but does form part of the SQL that is executed to build the table. This means that a) it will ‘Group By’ that column and b) it can be used to drill down on (which means it can also be enclosed in {{  }} for display at the next drill level). Including a unique identifier, but making it invisible would, for example, ensure that all rows were displayed, even if they weren’t unique.

• Display column as HTML - If you embed HTML tags within the definition of the data object, and this option is ticked, then that HTML will be applied in the data table; for example, to display an image or to force certain items to display in bold or a different colour.

• Show brackets for negative values - Negative values in the column in question will be displayed as (45.68) instead of -45.68. If you add prefixes to your numbers, e.g. currency symbols, the currency symbol will be displayed instead the brackets - (£50)

• Percent Ratio/Width - By default all columns will be ‘100’ meaning that they are of equal width. If you make one of the columns ‘50’ it will display at half the width of the ‘100’ columns, if you make another column ‘200’ it will display at twice the width. The numbers don’t need to add up to 100! 

Data Table Total types

On data table Dimensions you can change the Column Total Type. ‘Not Selected’ is the default value but you will be able to choose from By Count or By Distinct.

If you select ‘By Count’, the total figure will count how many rows there are in the column. In this example, there are 798 rows of data in the Job Role column.

If you select ‘By Distinct’, the total figure will count how many distinct items there are in that column. We know that there are 798 rows in the table but in the Job Role column, there are only 9 distinct values.

There are more options for Column Total Type for Measures. Again, the default is ‘Not Selected’ but you will also be able to choose from other options such as Sum or AVG for example.

The options are self-explanatory, but in our example data table selecting ‘By AVG’, for example, would show a mean average for that measure in the totals row.

In the following example, we have also added Row Totals - this option will only be available for Crosstab tables. These work the same as Column Totals, in that you can choose which calculation you want to perform. This example shows a SUM of Sales Values for each date (Row total) and for each individual Salesperson (Column total).

Dimension Sub Totals

When Dimensions have been used in a Table, you will be able to add a Subtotal at each change in the values in the Dimension.

In the Attributes screen, tick the ‘Show Dimension Sub Total Break’ box for the Dimension that you want to subtotal by. This will create a sub total at each change in value for that dimension, the relevant dimension column will also be sorted into ascending alphabetical order although this can be changed on the Sorting tab if required.

Example

In the following example, a Sub Total has been added to the Sales Year column. The Column Total for the Sales Value field has also been specified as SUM. This means that the Sales Year column will be sorted into alphabetical order and every time the value changes in this column, e.g. a different year, a sum will be added to the Sales Value field.

Grand Total Labels

When a total is selected for a column in a table, the text ‘TOTAL:’ will appear at the bottom of the first column of the table by default providing that column does not already have a total specified.

In the following example, a Sum total has been selected for the Sales Value column and the text ‘Total:’ is displayed by default at the bottom of the Region column

This text can be customised by changing the ‘Alternate Column Total Title’ field on the Attributes tab.

If you’re using a Crosstab table, you will also have the option of entering a new title for Row totals.