Multisheet Templates

Multisheet Templates allow you to create workbook with multiple sheets from a single template designed in Excel. You define ranges for the sets of column and row members available, then define how to combine them for each output sheet.

For example, we can define two sets of columns and two sets of rows, and use this to create four output sheets.

This input

Creates this output

Using this configuration

Here is the workbook used in the above example: [1]

How it works

This is an enhancement to the Scheduling component and allows the schedule to create multiple worksheets in an output workbook.

Based on the output sheet configuration, FluenceXL will create the required sheets and copy in the members defined for that sheet.

Any formatting on the members will be carried across into the output sheet.

At the intersection of the column and row members data will be retrieved from the model and put into the worksheet. Again, any formatting of the data are will be preserved so you can add number formats, bold total rows or highlighted backgrounds.

This works the same way as Freeform reports and shares many of the same settings.

Basic Configuration

The template book must contain at least one Connection to a model which will be used for data retrieval. (You can have multiple connections and use a different one for each sheet, discussed later)

You must tick the box in the Workbook Options -> Web Publication screen to mark the workbook as being a Multisheet Template.

The output sheet configuration must be in a named range called SheetsConfig, and at a minimum it must contain the following columns:

SheetName	Name of the output sheet to create.
ColumnMembers	Name of range containing the column members.
RowMembers	Name of range containing the row members.

Note the ColumnMembers and RowMembers ranges must be on the same worksheet. The other names can be on separate sheets if it makes the template maintenance simpler.

You must also have named ranges for ColumnHierarchies and RowHierarchies so that the members can be associated with their hierarchies. If you have the same hierarchies for many column or row sets you can create workbook names for those.

In the above example we have different hierarchies being used in each column/row member set, so have overridden those settings on each row.

• In general you can have an Excel name in the workbook for a given setting, and can override these for each configuration row as required.

Here you can see the names created in Excel for the sheets config area, as well as for each of the column and row areas.

Additional Configuration Columns

You can have extra columns in your configuration table to control the output of each sheet created. These are all optional.

TabColor	Color of the worksheet tab in Excel.
ConnectionID	ID of the connection to use (if not the first conection in the workbook).
StartColumn/Row	Position to begin output in the worksheet produced. Defaults to 2,2 cell $B$2.
NullReplacement	What to put in null cells, default is to leave them blank.
MemberMode	How to interpret the member text. Values can be Validate/Caption/Key/UniqueName.
InvalidMemberReplacement	What to put in cells when a member is invalid.
BlankHandling	How to handle a blank in a member cell. Values can be CopyPrevious/DefaultMember/Error. Default is CopyPrevious.
ReplaceFormulaeWithValues	Should calculations in the data area be replaced with fixed values.

You can also override any default by adding columns of the same name in the config table. For example you could add a column called FilterArea to provide a different Excel name, and override the default FilterArea named range in the workbook.

Filters

To add filters to a sheet you specify the name of the Excel range that contains the filters you want to use for the sheet.

Add a named range called FilterArea to provide default filters for all sheets produced.

This range must have exactly two columns, and each row must contain the name of the hierarchy followed by the member to filter on.

Calculations

You can add calculations into the columns and rows areas by entering a formula into the data area there the columns and rows intersect. This will then be propagated to the output worksheet.

Headers and Footers

You can add a header or footer to each output worksheet by creating a named range called SheetHeader or SheetFooter. This name can be overridden in the config table by adding a column called SheetHeader/SheetFooter and putting a different name in the cell for the output sheet.

The Header/Footer area will be copied to be the same width as the data in the sheet created, so you should add formatting extending to the right so that it'll look good when copied.

Column and Row Configuration

Some settings are configure for each column or row. In this case you create a named range for the entire column or row with the specified name and the settings are read for each cell as the template is generated.

For example to hide some columns you create a named range called ColumnHidden and set some of the values to false.

In this case columns $E:$E and $I:$I will be hidden in the result file.

Available settings are

ColumnHidden/RowHidden	Hide the specified columns/rows. Default is false.
ColumnVisible/RowVisible	Show the specified columns/rows. Default is true.
ColumnNumberScaling/RowNumberScaling	Scales the numeric results, can be used where you want to have numbers in thousands/million/billions. This changes the actual number in the cell, if you do not want this use number formatting instead to only change the display. Possible values are single letter K, M, B and T for thousands, millions, billions and trillions. Alternatively you can put a number in and the result will be multiplied by that. For example -1 would swap the sign of all results.

Output Variables

You can create Excel names with special values that will be populated during report generation. This can be useful to change some settings based on the sheet name.

SheetName	Name of the sheet being created.
RunDate	Date/time of generation.