Rows Range Lists are one of the most used Cosmos functions because they enable users to create dynamic lists of data quickly and easily in their reports. With a few clicks users can create reports that list thousands of rows of data from their Cosmos database.
To create a Range List that will return data going down rows, there are two steps for the user to follow. In the example below the user is looking to return a list of customers from the Customer table, and for each customer the user would like to see the following fields:
- Customer No
- Country Code
Step 1: Select the Desired Range of Cells
The first step is for the user to identify and select the range of cells that the user would like included when the results are returned. In the above example, for every customer that is returned the user would like to bring in data for all the headings identified in columns A through E.
Once the user has identified the range, they would highlight the desired range in Excel and then select the field to return for the list which is covered in Step 2 below. The selected range of cells would then look like this in Excel:
It is important to note that Cosmos will examine the range of cells selected and assist the user by automatically setting the List Direction (Rows or Columns) based on the shape of the selected range. If the range selected has more rows than columns, Cosmos will set the List Direction as Columns. If the range selected has more columns than rows, or the same number of columns as rows, Cosmos will set the List Direction as Rows. The user can manually change the List Direction by going to the Range Lists section -> General -> Direction, clicking the dropdown, and selecting the desired option.
Step 2: Select the Table and Field to Return
Once the range of cells has been selected, the user will navigate to the Task Pane and go to the Range Lists section. From there the user will click the New button.
Clicking this button will display the list of tables and fields available in the Cosmos database. The user will go to the table and field that they wish to return for the Range List (e.g. for the example above it would be the "No" field in the "Customer" table) and click ADD.
This will insert a Range List into the report in the upper-left corner of the selected range as shown below.
The default function name that is displayed will represent the table and field name that the function is returning. The user can adjust the function name in the Function -> Name section of the Report Editor.
When the report is run the customer numbers will then be returned in the report as shown below.