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

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.

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)

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.
ColumnHierarchies	Name of range containing the column hierarchies.
ColumnMembers	Name of range containing the column members.
RowHierarchies	Name of range containing the row hierarchies.
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.

Here you can see tha 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.
FiltersRangeName	Name of range containing the filter hierarchies and members.

Filters

To add filters to a sheet you