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 Sheet Lists within Sheet Lists, as covered in the example above, it is also possible to have a simpler version where the Sheet Lists 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 Sheet Lists and more details on that topic can be found in the article here:

Creating Sheet Lists

Details

Child sheets can be added to a report that already has an existing Sheet Lists 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 Sheet List function to lists out the Salesperson Name from the Customer table.
• Customer: This sheet contains a Sheet List 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 Sheet Lists section of the Cosmos Task Pane, click the New 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 Sheet Lists 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 Sheet Lists 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 Sheet Lists function to include the Customer sheet for every Salesperson that is listed out. 

To set this navigate to the Sheet Lists function, click EDIT, then open the General section, and click the Child Sheets dropdown.

This will display a list of all eligible sheets in the Excel workbook. Any sheet that is greyed out is disabled. 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 click on the Customer sheet name to select it. This will cause the sheet to be included in the range of the Salesperson Sheet Lists 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 Sheet Lists 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 Sheet Lists function to generate the list of customer numbers.

To create the secondary Report Sheet, navigate to the Customer sheet, expand the Sheet Lists section of the Cosmos Task Pane, click the New button, and select the desired table and field. In this example it will be the Customer table and the No field.

In addition, the Sheets Lists function has automatically been joined to the primary Sheet List 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.