Overview

Report Sheets enable users to create lists of information going across sheets. Report Sheets are similar, conceptually, to Rows Range Lists and Columns Range Lists but with the data being returned across sheets. 

For example, a user may want to create an Income Statement report, but they want it broken out where each sheet represents a department within an organization. By leveraging Report Sheets, the user can create the Income Statement once and, with a few clicks, have Cosmos create sheets for each department with the Income Statement being automatically filtered to the department on that sheet.

This article explains how to work with Report Sheets to create dynamic reports.

It is also possible to nest sheets using Child Sheets, which is covered in the article here:

Working with Child Sheets

Details

Report Sheets are managed in the Report Sheets section of the Cosmos Report Editor.

Creating Report Sheets will take the selected values, such as Salespeople, and create sheets for each Salesperson when the report is run. Other functions in the report can then reference the Report Sheets function to ensure that data on the sheet is filtered down to just the value that the sheet represents. 

If the Report Sheets function is added prior to other functions, such as Range Lists, these functions will automatically be set to filter on the Report Sheet function if there is a relationship defined in the data model. Any functions added prior to the Report Sheets function being created will need to have a filter added by the user to ensure that the data is being linked to the Report Sheet values (e.g. Salesperson) if desired.

Clicking the Add List Function button in the Report Sheets section will display the tables and fields in the data model to the user. Once the user clicks on the desired table and field, this will be displayed in the Report Sheets section.

Function

Expanding the Function section will show the user general information about the Report Sheets function.

Name: This represents the name of the Report Sheet. It is recommended to give this a descriptive name, such as "Salesperson Sheets", as this will be used in other functions when referring to the List Function.

List Type: This will enable the user to specify whether the Report Sheet is a Standard List or a Custom List. 

• Standard List: Returns a normal list of values based on the filter criteria and sort order specified by the user for the function.
• Custom List: Returns a list of values in the specific order that the user lists them. This is useful if the author would like things to be returned in a very specific order.

Table: This represents the table that the Report Sheet will be returning data from. The user can change this value using the drop-down.

• Important Note: If the table is changed the user may need to update any functions that reference the Report Sheet to ensure they are pointing to the correct field(s).

Field: This represents the field that the Report Sheet will be returning data from. The user can change this value using the drop-down.

• Important Note: If the field is changed the user may need to update any functions that reference the Report Sheet to ensure they are pointing to the correct field(s).

Filters

The Filters section enables the user to specify any filters that should apply to the Report Sheet to limit the data that is being returned. For example, the user could add a "Country Code" filter so that only customers from Canada are returned by the Range List when the report is run.

If no filters are specified, then all results will be returned from the database.

More details on working with filters are covered in the KB article here:

Working with Filters

Sorts

The Sorts section enables the user to specify how the data returned by the Report Sheet should be sorted. For example, the user could add a "Name" sort so that the list of customer numbers being returned is sorted alphabetically by the customer's name. 

If no sort order is specified, the results will be returned in the order in which they exist in the database.

More details on working with filters are covered in the KB article here:

How to Sort Data

Limits

The Limits section enables the user to control how many values will be returned by the Report Sheet when the report is run. For example, the user may only want to return the first 10 customers that match the filter criteria.

If no limit is specified, then all results will be returned from the database.

More details on working with filters are covered in the KB article here:

How to Limit the Number of Results Being Returned

Deleting a Report Sheet

If the user needs to delete a Report Sheet, they simply need to navigate to the Report Sheets section and click the "Delete List Function" button in the lower-left corner of the Report Sheets section.