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.

• 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

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

SheetExistsAction

Currently ignored. (Leave blank.)

Banding

The name of 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 can be included in the output sheet name using the syntax: <band>

Selection

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

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

Main Sheet Example:

SheetName	SheetExistsAction	Banding	Selection	Definition
Member Totals			Clear	MbrTotals
<band> Mbrs		MBR_BRANCH_NAME		MbrTotalsByBranch

In the above example, the template must contain two addition definition sheets: MbrTotals and MbrTotalsByBranch.

Definition Sheets

The definition sheet may contain any elements that you want. The sheet is process as follows:

• Copy the "USed Range" (1) of the sheet is copied into the new output file