Overview

Range Lists form a core component of creating dynamic, useful reports for users. In addition to enabling the author to return a lot of data with a single function, it also gives the author a high degree of control in terms of how the data is returned into the report.

Details

Range Lists are a powerful tool to enable authors to create dynamic reports. Even though Range Lists can be specified to move data in different directions, such as down rows or across columns, the general behavior and set up of the Range Lists is the same. 

It is important to note that Range Lists will return a unique list of values from the database. For example, if the Range List is created to return a list of customer numbers from a sales transactions table, it will only show each customer number once when the report is run. If a particular customer had 100 transactions that match the Range List filters this customer will not appear 100 times in the report, it will only be returned one time.

Setting the Range

The user has full control over setting the range that will be copied for every value returned by the Range List. This results in the user being able to be extremely specific regarding the shape of the report and how the data should be returned. 

To set the range the user would first highlight the selection of cells to be included (and copied) for every value, such as a customer number, which will be returned by the Range List. 

Once the user has selected the desired cells they will navigate to the Range List section of the Report Editor and click the Add Range List button. 

The user will then be shown a list of the tables and fields in the Cosmos database. Clicking on the desired field will create the Range List and insert a function in the upper-left-most cell containing the value of the field that the user selected. 

For example, if the user selected the "No" field from the "Customer" table, the resulting function after clicking the field in the Report Editor would look like this:

Details of the Range List will then be visible to the user when they click on any cells in the range. 

List Direction: The user can click the drop-down to specify whether the Range List will return values going down rows or across columns.

Address Range: This specifies the current range that the Range List covers. Everything in this range will be copied down (or across, for columns lists) for every value that is returned by the function

Select Range: Clicking this button will select all the cells in the Range List. This can make it easy to select the entire range list to visually see where it lives within the worksheet or to drag and drop the Range List to a new location within the worksheet.

Set Range: By selecting a range of cells and then clicking this button the Address Range for the Range List will be updated with the new selection. This can be helpful when adding additional fields to a report that requires the Range List to now be larger than it was originally set as.

Function

Expanding the Function section will show the user general information about the Range List function. 

Name: This represents the name of the Range List. It is recommended to give this a descriptive name, such as "Customer List", 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 Range List 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 Range List 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 Range List to ensure they are pointing to the correct field(s).

Field: This represents the field that the Range List 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 Range List 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 Range List 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 Range List 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 Range List 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 Range List

If the user needs to delete a Range List, they simply need to click on the cell containing the Range List and click the "Delete" button in the lower-right corner of the Range List section.