- 09 Oct 2024
- 14 Minutes to read
- DarkLight
Table Elements
- Updated on 09 Oct 2024
- 14 Minutes to read
- DarkLight
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 (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. Percentages. When selecting this option, column widths will be set to percentages 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. The unit of each value depends on the Width Type property. 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 Datasource 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. |
Keep Rows On Same Page | When enabled, the rows of the table will - if possible - not be splitted by a page break. This property is not available in all configurations. |
Fit To Available Width | When enabled, the width of the table will be adjusted to fit the available width within the created report document. All columns will change their widths proportionally. This property is not available in all configurations. |
Repeat Header | When enabled, the header of the table will be repeated on every page break. This property is not available in all configurations. |
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 Parent Table. When using this option and the table element needs to be excluded, the parent table that contains the placeholder referencing the table element will be removed from the template and may optionally be replaced with a replacement text. This option is only available if the Insertion Mode property is set to Write Into Existing Table. 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 Insertion 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:
%%<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:
%%<code>%%
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:
%%<code>%%
In a template edited with Microsoft Word 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.
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. Tota 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:
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.