Table Elements

General

Table report element can be used to export data in tabular form into the Template.

NOTE

Microsoft PowerPoint tables can only have a maximum number of rows and columns of 75. If you try to add a table with more rows or columns, an exception will occur.

General Properties

This type of report element has the following general properties:

Name	Description
Name	The name of the table element. This name will be displayed in the tree hierarchy of the content explorer of the Report Designer.
Code	The code of the table element. There must be no other report element within the same report with the same code. The code is used to reference the report element from within a Template (see below).
Condition	An optional condition Expression; if it returns True or 1 the condition is considered to be fulfilled and the table will be included in the report document. If it returns something other than True or 1 the exclusion settings (see below) will be applied. If you leave this property empty, the condition is considered to be always fulfilled. The condition expression is evaluated after the preparation Actions of the table element have been executed.
Preparation Actions	A list of Actions that will be executed before the table element will be processed.
Fail On Filter Exception	Determines what shall happen if an exception occurs when executing a preparation action that performs a filter (e.g. because the value to which you want to filter does not exist). When enabled, an exception will be thrown and the report document creation will be cancelled; when disabled, the exclusion settings (see below) will be applied.
Source	Datasource Object. When selecting this option, the table will be extracted from a datasource object (i.e. the visualization of a Qlik datasource or the table or view of an SQL Datasource. SQL Query. When selecting this option, the table will be extracted from the result of an SQL query executed in an SQL Datasource](/release35/docs/introduction-5). In that case any filters set (i.e. using Filter Datasource Field Action) will be ignored and the direct result of the SQL query will be used.
Datasource Object	The Datasource object from which the data shall be extracted. This property is only applicable if the Source is Datasource Object.
SQL Datasource	The SQL Datasource in which to execute the SQL query. This property is only applicable if the Source is SQL Query.
SQL Query	An Expression that returns a String that contains the SQL query to execute. This property is only applicable if the Source is SQL Query.
Include Header	When enabled, the table header will be included in the export. This property is not applicable in all scenarios and not all Datasource objects support headers.
Include Totals	When enabled, the grand totals of the table will be included in the export. This property is not applicable in all scenarios and not all Datasource objects support grand totals.
Column Selector	All Columns. When you select this option, all columns of the source table will be included in the exported table. Include Specified. If you select this option only columns specified in the Column Numbers property (see below) will be included in the exported table. Exclude Specified. If you select this option all columns of the source table will be included in the exported table except for those specified in the Column Numbers property (see below).
Column Numbers	An Expression that returns one or more column numbers. This property is only applicable if the Column Selector property is set to something other than All Columns. When the column selector is set to Include Specified, the expression needs to return one or more column numbers in the order in which they shall be included in the exported table. If the selector is set to Exclude Specified, the expression needs to return one or more column numbers to be excluded from the table.
Null Value	An Expression that evaluates to the representation of null values in the table. When left empty, null values will be represented with a blank.
Transpose	When enabled, the table will be transposed (rows will become columns and columns will become rows). The transposition takes place after the Include Header, Include Totals, and Column Selector properties have been applied but before styles, column widths, and row heights are applied.
Width Type	This property is not available in all configurations. Do Not Set. When selecting this option, column widths of the exported table will not be set. Automatic. When selecting this option, column widths will automatically be adjusted to fit contents. Pixels. When selecting this option, column widths will be set to pixels returned by the Column Width(s) property. Keep Original. If you choose this property, the original column widths will be kept. This option is not available for all Datasource objects.
Width(s)	An Expression that returns a single Number or a List of numbers that represent column widths in pixels. If there are more columns in the table than column widths provided, the last provided column width will be applied to the remaining columns. This property is only applicable if the Width Type property is set to Pixels or Percentages.
Row Height Type	This property is not available in all configurations. Do Not Set. When selecting this option, row heights of the exported table will not be set. Automatc. When selecting this option, row heights will automatically be adjusted to fit contents. Pixels. When selecting this option, row heights will be set to pixls returned by the Row Height(s) property. Keep Original. If you choose this property, the original row heights will be kept. This option is not available for all Datasource objects.
Height(s)	An Expression that returns a single Number or a List of numbers that represent row heights in pixels. If there are more rows in the table than row heights provided, the last provided row height will be applied to the remaining rows. This property is only applicable if the Height Type property is set to Pixels.
Insertion Mode	Determines how the table is inserted into the template. Create New Table. When selecting this option, a new table will be created in the template. Write Into Existing Table. When selecting this option, the table will be written into an existing table. Fill MSO Chart. When selecting this option, the data of the exported table is used to feed a Microsoft Office Chart (MSO Chart). You can find more information about the different insertion modes below.
Styling Mode	This property is not available in all configurations. Do Not Set. When selecting this option, no style will be applied to the exported table. Keep Original. When selecting this option, the style of the exported table will be kept as close as possible to the original style of the Datasource object. Custom. When selecting this option, you can specify custom style settings which will be applied for all Table Cell Types. Custom Per Cell Type. When selecting this option, you can specify individual style settings for every Table Cell Type.
Custom Style	This property is not available in all configurations. If the property Styling Mode is set to Custom Per Cell Type there will be one property per Table Cell Type. Do Not Set. When selecting this option, no style will be applied to the exported table. Keep Original. When selecting this option, the style of the exported table is as close as possible to the original style of the Datasource object. Custom. When selecting this option, you can specify a custom Table Cell Type. Global Style. When selecting this option, you can select a Global Style to apply.
Exclusion Behavior	Exclude Element. When choosing this option and the table element needs to be excluded, the table will simply be ommitted from the output and an optional replacement text (see below) may be inserted. Exclude Chart. When you select this option and the table element needs to be excluded, the MSO chart fed by the table element will be remvoed from the report document and optionally replaced with a replacement text. This option is only available if the csInsertion Mode property is set to Fill MSO Chart.
Replacement Text	An optional Expression that evaluates to a string that contains the replacement text to insert into the report document when the expression element needs to be excluded (e.g. if the condition is not fulfilled).

Insertion Modes

This chapter describes the various insertion modes that may be used to insert a table into the report document.

Create New Table

When setting the Insertion Mode property to Create New Table the exported table will be written to the report document as a new table. When dragging and dropping the report element from the content explorer of the Report Designer into the Template, a placeholder referencing the table element with the following syntax will be inserted:

Table Element Placeholder

%%<code>%%

In the template, the placeholder looks like this:

Table Element PlaceHolder

When the report document is created, the placeholder will replaced with a newly created table.

Write Into Existing Table

This insertion mode can be used to write the exported table into an existing table within the Template. When dragging and dropping the report element from the content explorer of the Report Designer into the Template, a placeholder referencing the table element with the following syntax will be inserted:

NOTE

The placeholder must always be put into the first column of the existing table.

A new property Existing Table Insertion Mode will be available. If set to Overwrite, the placeholder referencing the table element may be placed in any row of the existing table. The cell that contains the placeholder will be the starting cell (the top left cell of the exported table); when creating the report document, the exported table will be written into the cells of the existing table. An error will occur if the existing table does not have enough rows to hold the exported table. When setting the Existing Table Insertion Mode property to Insert Rows, the placeholder referencing the table element may be placed in any row and there must be at least one row below the row that contains the placeholder. Mail & Deploy will automatically insert the required amount of rows and use the row that contains the placeholder as a template for all header rows of the table and the row directly below the row that contains the placeholder as the template row for all data rows of the table. An error will occur if the existing table does not have at least one row below the row that contains the placeholder.

Fill MSO Chart

When setting the Insertion Mode property to Fill MSO Chart the exported table will not be directly written to the report document. Instead, you can add an MSO chart (a Microsoft Office Chart) in the Template and use the table element to feed data to it when creating the report document. To create a link between the MSO chart and the table element, you have to right-click the MSO chart and select Edit Alt Text. As alternative text, enter a placeholder for the table element in the following syntax:

Table Element Placeholder

%%<code>%%

In a template edited with Microsoft PowerPoint 2019, a table report element named MyTableElement would be referenced like this:

Referencing a Table Element from MSO Chart Alternative Text

When selecting an Insertion Mode of Fill MSO Chart new properties will become available to specify MSO Chart Settings by which you can dynamically set properties of the MSO chart that the table element feeds with data.

Cell Settings

These settings can be used to manipulate properties of cells of the exported table. Every table report element has Default settings and (optionally) a number of custom cell settings. For every cell of the exported table, the applicable cell settings are evaluated; if custom cell settings are found, they are applied, otherwise the default settings are used.

NOTE

Only the first cell settings whose selector fits a cell will be applied to that cell (even if subsequent cell settings may also apply); cell settings are never combined.

Default Settings

The default cell settings are applied to all cells for which no custom cell settings have been found. They have the following properties:

Name	Description
Custom Value	When enabled, you can specify an Expression that may be used to override the value of the cell. The expression needs to return the effective value of the cell. See below to see available expression parameters for this expression.
Style Application Mode	Do Not Set. When selecting this option, no style properties will be set for applicable cells. Keep Original. When selecting this option the style of applicable cells will be as close as possible to the source table. Custom. When selecting this option, a button will appear that allows you to specify custom style settings for applicable cells. Global Style. When selecting this option you can select a Global Style to apply to applicable cells. Please not that styling is not always available.

Custom Settings

You can specify zero or more custom cell settings which will be used with a higher priority than the default cell settings. Custom cell settings have the following additional properties:

Name	Description
Name	The name of the custom cell settings.
Cell Selector	Depending on Cell Type. If you select this option, you can select Table Cell Types to which the custom settings apply. Depending on Column Number. If you select this option, you can specify a range of column numbers (starting with 1) for which the custom settings apply. Depending in Column Number and Cell Type. If you select this option, you can specify a range of column numbers (starting with 1) and Table Cell Types. Only cells for which both conditions apply are affected. Depending on Row Number. If you select this option, you can specify a range of row numbers (starting with 1) for which the custom settings apply. Depending on Row Number and Cell Type. If you select this option, you can specify a range of row numbers (starting with 1) and Table Cell Types. Only cells for which both conditions apply are affected. Depending on Row and Column Number. If you select this option, you can specify a range of row numbers (starting with 1) and column numbers (starting with 1) to which the custom settings apply. Depending on Custom Condition. If you select this option, you can specify a custom condition Expression; the custom settings are applied to all cells for which the condition returns True or 1. See below for available expression parameters.

Expression Parameters

These are the available expression parameters for custom cell value and condition Expressions of cell settings:

Name	Description
CellValue	The value of the cell for which the cell settings are evaluated.
CellType	The Cell Type of the cell for which the cell settings are evaluated. 0. DataTopHeader 1. TotalsTopHeader 2. SubTotalsTopHeader 3. DataLeftHeader 4. TotalsLeftHeader 5. SubTotalsLeftHeader 6. Data 7. Total 8. SubTotal. 9. Filler
ColumnNumber	The column number of the cell for which the cell settings are evaluated (the first column has column number 1).
IsCellValueNumeric	True if the value of the cell for which the cell settings are evaluated is numeric, otherwise False.
RowNumber	The row number of the cell for which the cell settings are evaluated (the first row has row number 1).

Example 1

Let's suppose you only have default cell settings (i.e. no custom cell settings) and you have the following custom value expression set up:

Custom Value Expression

If([ColumnNumber]=1 And [RowNumber]=2, "Hello World", [CellValue])

Since there are no custom cell settings, the default settings are applied to all cells of the table. The custom value expression leads to the cell in the first column and second row to have the text Hello World; all other cells with keep their original value (defined by the [CellValue] expression parameter).

Example 2

Let's suppose you have custom cell settings with a selector Depending on Column Number and a column number range of 1 to 3 where the style application mode is set to Custom and the Bold style property of the custom style is set to Yes. The default settings have a style application mode of Keep Original. In that case, all cells to which the custom cell settings apply (all settings in the 1st to 3rd column) will have a bold text. All other cells will keep their original style, because the default cell settings are applied.

NOTE

Different cell settings are never combined; only the first cell settings whose selector targets a particular cell will be applied to that cell.