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.
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>
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}.
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."
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}.