Excel Template File

Overview

An Excel Template report is defined by a definition (template) file that specifies how the report should be created. Each template file must contain a Select Sheet, a Main Sheet and one or more Definition Sheets.

• Select Sheet

Like the DocumentDistribution file, the select sheet contains selection definitions. Selections defined in the template file are local and can only be used by the current report request. This sheet is required (and must contain valid column headers) even if no selections are defined.

• Main Sheet

This sheet contains a list of definition sheets to execute. The main sheet allows you to specify a selection that applies to the definition sheet as well as a banding value. Specifying a banding field will cause "Sheet Level" banding to be applied.

• Definition Sheets

Each definition sheet is a template of the sheet that will be created when the report is executed. The sheet contains cells that are copied "as is" without any changes as well as cells that are "evaluated". Cells that are evaluated can contain chart exports, or text that contains substituted values.

Select Sheet

This sheet contains local selection definitions used by the current report. Each selection consists of one or more steps that share the same selection name.

Main Sheet

The main sheet contains a list of all definition sheets in the template file. Each row in the main sheet refers to a single definition sheet to be executed. Definition sheets are executed in the order they appear in the main sheet.

SheetName (required)

The name of the sheet that will be created. The name can include references to the current date, the current banding value and to QlikView variables.

* <band> - The current banding value. Use for file level banding.

* <date> - The current date. This is the actual create date when the file was created.

* $(varname) - The value of any qlikview variable. Variables can be created by the selection.

SheetExistsAction (ignored)

This field is currently unused, leave blank.

Banding (optional)

The name of a banding field. When a banding field is specified, the definition sheet is re-evaluated for each unique value of the banding field. Specifying a banding field causes "sheet level" banding and a new sheet will be created for each unique (possible) value. The banding value should be included in the output sheet name using the syntax: <band>

Selection (optional)

The name of a selection defined in the "Select Sheet" of either the current template file or the main document distribution file. If a selection with the same name exists in both locations, the local (template file) definition will be used.

Definition (required)

The name of a definition sheet. A sheet with this name must exist in the current document. If the definition sheet name is prefixed by a # character, the sheet will be ignored.

SheetAction (optional)

One of Copy or Process. Default is Process. When set to Copy the definition sheet is copied in it's entirety, however, individual cells are not processed looking for special tags.

Main Sheet Example:

SheetName	SheetExistsAction	Banding	Selection	Definition	SheetAction
Instructions				Instructions	Copy
Member Totals			Clear	MbrTotals	Process
<band> Mbrs		MBR_BRANCH_NAME		MbrTotalsByBranch	Process

In the above example, the template must contain a total of 5 sheets: Select, Main, Instructions, MbrTotals and MbrTotalsByBranch. The Instructions sheet is copied without any additional processing.

Definition Sheets

The definition sheet may contain any elements that you want. For example, the sheet may contain a logo, as well as text and calculated fields. The sheet may also contain special tags that will export charts and tables from the QlikView application into the final sheet. A tag can also generate customized text. The sheet is processed as follows:

• If a selection is specified, execute the selection
• If a banding field is specified, loop on each possible banding field value:

* Copy the "Used Range" (1) of the definition sheet into the new output file

* Set the row heights and column widths in the new sheet to match the definition sheet

* Walk all rows and columns looking for special tags

* If an export tag is found, process the export and insert the required number of rows.

* If a text tag is found, process the text statement and insert the text field in the final document

Special Template Tags

The definition sheet may contain special xml tags that are processed during the report execution.

Text Tag

The text tag will process the text within the tag and substitute any special values that it finds.

The general form of a text tag is:

<Text>Any text with substitutions.</Text>

The text substitution codes are:

• <date>

The current date.

• <band>

The current banding value.

• $(VarName)

The value of any QlikView variable.

• Examples:

<Text>Totals for <band> as of $(PriorMonthDate)</Text>

Will produce: Totals for FIRST AVENUE as of 2013-03-31

<Text>Report execution date: <date></Text>

Will produce: Report Execution date: 2013-04-19

The substituted text will appear in the final document with the same attributes (alignment, font, color, etc.) as it had in the definition sheet.

Export Tag

The export tag exports a single chart or table from the QlikView application into the same (relative) cell in the final document. When the exported chart is inserted in the final document additional rows will also be inserted to make room for any other cells that may appear in following rows in the definition sheet. (In other words, you do not need to anticipate the number of rows that will be consumed by the chart - the correct number of rows will always be inserted.)

The general form of the export tag is:

<Export><ObjectType>QlikView Object ID</ObjectType><Select>...</Select><Dims>...</Dims><Parms>...</Parms><Format>...</Format></Export>

In version 1.5.3 (and higher), in addition to exporting charts and tables (as data) it is possible to export charts as an image. Exporting the image of Text Boxes is also supported.

To export a chart or table as data, use:

<Export><Chart>QlikView Object ID</Chart>...</Export>
  For backwards compatibility, the following is also supported:
  <Export><Object>QlikView Object ID</Object>...</Export>

To export the Image of a chart, use:

<Export><Image>QlikView Object ID</Image>...</Export>

To export the Image of a text box, use:

<Export><Text>QlikView Object ID</Text>...</Export>

Note: This is not the same as using a Text Tag. This exports an actual QlikView Text Box whereas a Text Tag creates a string of text.

• Object ID (required)

The object ID of the chart, table or text box. When exporting a chart or table as data the object is exported into a temporary file (ExcelTemp) and then copied into the final sheet after the appropriate number of rows have been inserted.

• Select (optional)

A selection to be executed prior to exporting the object. Once the object has been exported, the selection is returned to it's previous state.

• Dims (optional)

Many Vital Signs charts have selectable dimensions. To ensure the chart is exported with the correct dimension, specify the Dims tag as follows:

<Dims>DimName1=FieldName1</Dims>

if the chart has more than one selectable dimension, separate the dimension names with commas as follows:

<Dims>DimName1=FieldName1,DimName2=FieldName2</Dims>

• Parms (optional)

Several chart parameters can be temporarily changed prior to the export:

*SuppressHeader

Suppresses the chart header. When selected (=1) no headers will be included in the export. In this case, chart header values should be supplied in the definition template.

Note: This option is only supported with charts. It is ignored for straight tables.

*SuppressTotals

Suppresses chart totals. When selected (=1) no totals will be included in the export.

*AlwaysFullyExpanded

Valid only for Pivot Tables. When selected (=1) forces all pivot dimensions to be fully expanded prior to the export.

*UsePartialSum

Valid only for Pivot Tables. When selected (=1) forces all pivot dimensions to display sub totals.

If multiple parms are being specified, they should be separated by a comma as follows:

<Parms>SuppressHeader=1, AlwaysFullyExpanded=1</Parms>

• Format (optional)

Several formatting parameters can be specified as part of the export tag. By default, the format from the corresponding cell of the template file is used. These formatting options are applied after the default settings have been applied.

*FontSize

Sets the font to the specified point size.

*Bold

When selected (=1) displays text in bold.

*Italic

When selected (=1) displays text in italic.

*Underline

When selected (=1) displays text underlined.

*FontColor

Sets the font color to the specified value.

*BackColor

Sets the background color to the specified value.

*BorderColor

Sets the border color to the specified value.

*AutoFitRow

When selected (=1) adjusts the height of the row to fit the cell contents - useful for chart images.

*AutoFitColumn

When selected (=1) adjusts the width of the column to fit the cell contents - useful for chart images.

*RangeName

Sets the specified Excel Range Name to the cell range containing the exported data. Range names can include <band> and $(varname). Blanks are not allowed in a range name and will be removed.

If multiple format options are being specified, they should be separated by a comma as follows:

<Format>Bold=1, AutoFitColumn=1, RangeName=LNDATA_<Band></Format>