Overview
Sheet Lists enable users to create lists of information going across sheets. Sheet Lists 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 Sheet Lists, 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 Sheet Lists to create dynamic reports.
It is also possible to nest sheets using Child Sheets, which is covered in the article here:
Details
Sheet Lists are managed in the Sheet Lists section of the Cosmos Report Editor.
Creating Sheet Lists 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 Sheet Lists function to ensure that data on the sheet is filtered down to just the value that the sheet represents.
If the Sheet Lists function is added prior to other functions, such as Range Lists, these functions will automatically be set to filter on the Sheet Lists function if there is a relationship defined in the data model. Any functions added prior to the Sheet Lists function being created will need to have a filter added by the user to ensure that the data is being linked to the Sheet Lists values (e.g. Salesperson) if desired.
Clicking the New button in the Sheet Lists 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 Data tab of the Sheets List section.
Name: This represents the name of the Sheet Lists function. 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.
General
Here is where the user can add any Child Sheets if they'd like. Please see our article on Child Sheets for more information.
The user can also specify whether the Sheet Lists function will be 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.
Data
Table: This represents the table that the Sheet Lists 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 Sheet Lists 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 Sheet Lists to ensure they are pointing to the correct field(s).
Set: Here you can choose which set to use. It will default to the name of the field being used.
Limit: Here the user can limit the amount of data the gets returned. For example if you have 25 sales people and you only want to create sheets for 3 of them. You would enter the number 3. And when the report runs it will only return the 3 sales people.
Filters
The Filters section enables the user to specify any filters that should apply to the Sheet Lists 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:
Sorts
The Sorts section enables the user to specify how the data returned by the Sheet Lists 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:
Limits
The Limits section enables the user to control how many values will be returned by the Sheet Lists 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 Sheet Lists function, they simply need to navigate to the Sheet Lists section and click the "DELETE" button in the upper-right corner of the Sheet Lists section.
Comments
0 comments
Article is closed for comments.