- 1 Introduction
- 2 Properties
- 3 Formatting
- 4 Calculations
- 5 Paging
- 6 Sorting
- 7 Sections and Pivoting
- 8 Data source specific functionality
- 9 Performance tips for large tables
- 10 See Also
Once the table has been inserted, several features are available which apply to all tables.
Please see the Table Properties page for more information.
As with grids, formatting for tables is defined on the XLCubedFormats worksheet.
Tables will automatically pick up the default formatting for the column headers and data area.
In addition you can also define formats that will only apply to certain sections of the table.
You can format specific columns easily by right clicking the table and selecting "Format Column" or "Format Column Title" from the menu. This will show the formatting dialog, and automatically create the relevant rows in the format sheet.
This can also be done manually on the format sheet. To do this type the word "Column" as the column name, and then the name of the column.
You can the format the header and data areas. The following example makes the "SalesTerritoryKey" header bold, and uses italic formatting for the data.
You can highlight specific values in a table by entering the column name and value, and setting the data cell format. The following example highlights December in red, and makes months starting with "A" bold.
You can add your own calculated columns to an XLCubed table, which can contain any Excel formula.
To add a calculation, simple right click a column header and select "Add Calculation".
When you enter a calculation in the first cell, it will automatically "fill down". If the calculation references and columns in the table these will be stored as references to the column, so they will continue to work even if the columns are reordered.
In this example the total value is calculated from two columns retrieved, and the calculated column has been formatted as currency, as described in the formatting section.
If you have a large amount of data, it is often useful to display it in a series of pages. This is easily done with XLCubed tables.
The simplest way is to simply right click the table and select "Insert Pager".
Alternatively, go to the Slicer menu, and select "Insert data table pager". Right click the pager to configure which table you wish to page, and the page size, and click OK. This is useful when you want the pager to be on a different sheet to the table.
Tables can easily be sorted by right-clicking a column header and selecting the sorting option.
You can sort by several columns by sorting them one after the other, and clear all sorting by selecting the appropriate menu item.
In the following example we sorted the "SalesTerritoryKey", and then the "Sales Amount". This gives a report with the Sales Terrorities in sorted order, and within these the values are in ascending order.
Sections and Pivoting
These are two features that change the layout of a table from that returned by a query, making the result more useful for users. Repeating data can be eliminated, and layout improved (e.g. for charting).
Often one or more columns will repeat their value for many lines in the report. Often this is not useful information, and Sections can reduce this redundancy.
When you enable sections, column values will be written once as a header. As an example, take the following report.
EnglishCountryRegionName is repeated a great deal, and does not add much value to the report. By going into the Table Properties screen, we can enable sections, and use one column in the header.
The result is as follows (EnglishCountryRegionName column has been configured to highlight in bold, using the standard formatting sheet).
The style of the sections can be controlled using the Table Properties form.
- Sections in one cell
- Sections in separate cells
- Sections in separate rows
- Display column names
- Toggles the column name as part of the section.
Using a combination of sections and the format sheet, it is possible to produce a classic "Reporting Services" style report:
Hide repeating values
New in Version 7.5 there is an option to hide repeating values - so only the columns with a changed value will be displayed in each section.
With this option enabled, "Car and Bike Stores" would be shown once and each sub-category listed underneath:
Pivoting a column takes the values for that column, and makes a new column for each.
For example, instead of having months going down, with one month on each row, we can pivot the column and have months go across the columns. The following example shows this in action.
This screenshot shows one column for month names:
Click on the column to pivot and right-click. From the XLCubed menu select Pivot 'columnname'
Now we have one column for each month and a table that is much easier to read.
Data source specific functionality
Depending on where the data for a table originated from there may additional options for interacting with the data.
- Edit Parameters
- Link the query parameters to Excel cells or slicers.
- Edit Connection Range
- Link the Connection String to a cell, allows driving the server or database from other parameters in the workbook.
Tabular Analysis Services
- Breakout (available from measure columns)
- Create a ranked list of the top or bottom values making up the number selected.
Performance tips for large tables
If you have very large tables, the following options will speed up data refreshes:
- Apply Formatting
- Merge repeating cells
- Resize Columns