Scroll

Excel: Grouping on Separate Worksheets

The following article describes a feature which is available in v2016.2.8 or later.

The default behavior for Microsoft Excel report exporting runs the entire report on a single Worksheet, whose name is the name of the report.

If a report contains Group Sections, an Excel report can be set to create a new Worksheet for each new instance of a group.

Usage

The report in question must contain a Group Header section.

Elements preceding the first group header will appear on the first page as normal. Each time a new group header is reached, the Excel document will break onto a new worksheet, containing all the contents of the group. The name of each worksheet will be the data field on which the group breaks. If the group breaks on a data category, the Unique Key field will be used to name the worksheet.

NOTE. New worksheets will only be created for the outer-most grouping.

There are two ways to set Excel group breaking on a report:

Using the API flag:

myReport.GroupsOnSeparateWorksheets = true;

Editing or adding the following key in the <main> section of the report xml:

<groups_on_separate_worksheets>True</groups_on_separate_worksheets>

Example

See the following report design as an example. Note that it has a group section with a Group Header, Detail, and Group Footer, which break on the data field {Product.ProductName}.

Default Behavior

When exporting to Excel, the default behavior would result in an report which looks like the following image:

Note that the entire report, including multiple groups, is displayed on a single page (worksheet), and that the name of the worksheet is the title of the report: "Orders by Product."

Group per Worksheet

By setting the Group on Separate Worksheets tag on this report, the output would result in a report which looks like the following image.

Note that the first worksheet is named the title of the report, "Orders by Product." There is a new worksheet created each time a new group is reached, named by the data field by which the group is breaking, {Products.ProductName}.


Hidden Article Information

Article Author
Exago Development
created 2016-09-30 20:05:41 UTC
updated 2017-02-21 18:50:21 UTC

Labels
Grouping, Group Header, excel, Report Designer, output, Report Output,
Have more questions? Submit a request