Range lists provide an easy way for users to create reports with data dynamically moving down rows or across columns. It is also possible to nest lists within each other to create grouping reports. For example, the user may want to show a list of all the Salesperson Codes that had transactions in a specific date range, and then underneath each Salesperson Code they want to show all the Customer Numbers that had sales within the date range and related to the salesperson.
This article will walk through how to nest range lists to create grouping reports.
Since nesting Range Lists is based on using either Rows Range Lists or Columns Range Lists it is recommended to already be familiar with how those features work. More details on each are covered in the Knowledgebase articles below:
Nesting Range Lists involved placing one Range List within the region of another Range List. These will commonly be set up within the same Range List type (e.g. a Rows Range List within another Rows Range List) but it is possible to have Range Lists overlap as well, such as having a Rows Range List for customers intersect with a Columns Range List for months; at the intersection of the two could live a function that sums up the sales for each customer (going down) and each month (going across).
A foundational concept in working with nested Range Lists is understanding the regions for each list and what these regions do. More details of this are covered here:
In summary, each Range List will have a region that is copied down for each value that the Range List returns. In the screenshot below, the rows List Function for Customers will copy everything from cell A1 to cell G6 for each customer that is returned. Any text, Cosmos functions, Excel functions, and formatting will be copied down when the report is run.
As shown below, the resulting list of customer numbers would look like this:
To create a nested rows list, you would simply need to insert another Range List within the region of the first rows Range List. This will create a report that returns all the customer numbers, as shown above, and beneath each customer number it would return a list of all items that the customer purchased, for example.
The resulting nested Range List for items beneath customers would look like this:
Assuming there is a relationship specified between the Items Range List and the Customers Range List in the data model, the filter will also be set automatically so that only items related to each customer are returned.
There is no limit to the number of levels that can be nested in a report.