Overview

Child Sheets enable the user to create reports that will dynamically generate sheets that are nested and grouped together.

An example of this could be a report that creates a sheet for each Salesperson that had transactions last month, and after the Salesperson it would then generate a sheet for each customer that had transactions last month for that Salesperson. 

This example would create a series of sheets that look like this:

In addition to nesting Report Sheets within Report Sheets, as covered in the example above, it is also possible to have a simpler version where the Report Sheets would list out all the Salesperson sheets as summary information, and then after each Salesperson would be a detail sheet that could include all the sales transaction details for each Salesperson. 

By having control over this level of formatting the user can create a wide variety of useful reports without having to manually go back through and rearrange the order of the sheets after the report has been generated.

Child Sheets build upon Report Sheets and more details on that topic can be found in the article here:

Creating Report Sheets

Details

Child sheets can be added to a report that already has an existing Report Sheets function and that has more than one sheet. 

In the example below, there are two sheets that exist in the report:

• Salesperson: This sheet contains a Report Sheets function to list out the Salesperson Name from the Customer table.
• Customer: This sheet contains a Report Sheets function to list out the Customer No from the Customer table. It will only list out customers related to the Salesperson Name on the preceding sheet.

Salesperson Report Sheet:

Customer Report Sheet:

Creating the Primary Report Sheet

The first step when working with Child Sheets is to create the primary Report Sheet. In this example, it will be the Report Sheet that lists out the Salesperson Name from the Customer table.

To create the primary Report Sheet, expand the Report Sheets section of the Cosmos Report Editor, click the Add List Function button, and select the desired table and field. In this example it will be the Customer table and the Salesperson Name field.

Now that a Report Sheets function has been created that will list out the Salesperson Names, ensure that a second sheet has been inserted. In this example, we created a new sheet and named it Customer, since it will eventually contain a secondary Report Sheet function to return all the customers associated with the primary Report Sheet's Salesperson Name.

Adding the Child Sheet to the Primary Report Sheet Function

Once the primary Report Sheet function has been created, the next step will be to configure the Report Sheet function to include the Customer sheet for every Salesperson that is listed out. 

To set this navigate to the Report Sheet function and click the dropdown for the Child Sheets section.

This will display a list of all eligible sheets in the Excel workbook. Any sheet that is greyed out is disabled and will include a brief description of why that sheet is not eligible to be a child sheet. In the example below, the Salesperson sheet isn't eligible to be a child sheet because this is the sheet that we're already on, and a sheet can't be a child of itself. 

The user can check the box for the Customer sheet. This will cause the sheet to be included in the range of the Salesperson Report Sheet function, similar to how Range Lists work for rows and columns.

The example below shows the Customer sheet selected as a child sheet of the Salesperson Report Sheets function.

Creating the Secondary Report Sheet

After creating the primary Report Sheet and linking it to the child sheet, the next step is to insert a Report Sheet function to generate the list of customer numbers.

To create the secondary Report Sheet, navigate to the Customer sheet, expand the Report Sheets section of the Cosmos Report Editor, click the Add List Function button, and select the desired table and field. In this example it will be the Customer table and the No field.

In addition, the Report Sheets function has automatically been joined to the primary Report Sheet so it will be filtering based on the Salesperson being returned on the preceding sheet.

The results shown below represent each salesperson being returned (e.g. Peter Saddow and Linda Martin), and after each Salesperson is a sheet with the Salesperson Name and Customer Number joined together. 

Child sheets can be a very powerful way to build reports that require dynamic sheets to be generated and links together in a specific manner without manual effort on the part of the user.