Table Elements

General

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

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](/release35/docs/templates-2) (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. 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.
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.
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.
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. Write Into Worksheet. When selecting this option, the table will be written into a worksheet of the template. 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.
Delete Row Above	When enabled, the row directly above the exported table will be deleted from the worksheet after the table has been exported.
Delete Row Below	When enabled, the row directly below the exported table will be deleted from the worksheet after the table has been exported.
Create Header Filter	When selected, all cells of the last header row will have the Microsoft Excel filter feature enabled. Please note that Microsoft Excel only supports one set of filterable cells per worksheet.
Row Insertion Mode	This property is not available in all configurations. Overwrite. When selecting this option, the table will be exported with the upper left cell of the table starting at the cell that contains the placeholder; existing content in the worksheet will be overwritten if it overlaps the table. Insert Rows. When selecting this option, required rows will be inserted into the worksheet as required by the table size. All the content of the worksheet below the table will be shifted down by the number of inserted rows. Insert Cells. When selecting this option, required cells will be inserted into the worksheet as required. All the content below the table (but only content in cells that are within columns covered by the table) will be shifted down. Copy Rows. When selecting this option, required rows will be inserted into the worksheet and inserted rows will be copied from the row that contains the placeholder of the table element. Contents below the table will be shifted down. Copy Body Rows. When selecting this option, required rows that belong to the body of the table will be inserted into the worksheet and copied from the first row in the template that is below the header area of the table (e.g. if the table has two header rows, then the row that contains the placeholder and the row directly below it will not be changed during the export; the row below the second header row will be used as a template for body rows of the table).
Synchronize Cell Insertion	Option only available when the property Row Insertion mode is Insert Cells. It allows to place two or more table tags in the same row in the Excel worksheet. All these Table elements should have this property enabled. For more information, check the following article: https://community.mail-and-deploy.com/support/solutions/articles/77000558836-excel-report-synchronize-cell-insertion-example
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 Style. 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 element will simply be ommitted from the output and an optional replacement text (see below) may be inserted. Exclude Row(s). If you choose this option, the row that contains the reference to the report element (or the rows covered by the MSO chart you fill) will be remvoed from the report document. Exclude Sheet. If you choose this option, the sheet that contains the reference to the report element (or the MSO chart) will be removed from the report document.
Replacement Text	An optional Expression that returns a String that contains the replacement text to insert into the report document when the report element needs to be excluded (e.g. if the condition is not fulfilled). Not all exclusion behaviors support a replacement text.

Insertion Modes

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

Write Into Worksheet

When setting the Insertion Mode property to Write Into Worksheet the exported table will be written to the worksheet. 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>%%

The table that will be written to the report document will start at the cell that contains the placeholder. In the template, the placeholder looks like this:

Table Element 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 Excel 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 ](/release35/docs/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 Valuen 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.