If a report contains Group Sections, an Excel export can optionally create a new worksheet for each new instance of the outermost 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 from the Data Object’s configuration will be used to name the worksheet.
Note
This feature only applies to Excel export file outputs. To display groups on different pages in PDF and RTF export file outputs, add a Page Break in the report design.
The report must contain at least one Group Header section. Elements preceding the first group header will appear on the first worksheet as normal. Each time a new group header is reached for the outermost group, including the very first one, the workbook will create a new worksheet with the contents of that group.
There are two ways to enable grouping on separate worksheets. Either:
myReport.GroupsOnSeparateWorksheets = true;
<main>
section of the report’s XML definition to True:
<groups_on_separate_worksheets>True</groups_on_separate_worksheets>
Caution
Search the XML for this node first, adding it to a report that already has it will break the report and cause errors to appear in the user interface.
Consider a report that displays summary data of the orders made by each customer of the organization. The report below includes a Group Header section that breaks on the sort field Customers.CompanyName
.
If this report is exported to an Excel workbook, the resulting output looks like something like this:
Note that the entire report, including multiple customer company names (the outermost group header), is displayed on a single tab (worksheet), and that the name of the worksheet is the title of the report: Orders per Customer.
By enabling the Group on Separate Worksheets property on this report, the resulting output looks like something like this:
Note that the first worksheet is still named the title of the report, Orders per Customer but now there is a new worksheet created each time a new Customer’s Company Name occurs, one worksheet for each customer’s name.