Overview

Columns Range Lists can be extremely helpful when building reports 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 columns of data from their Cosmos database.

Details

To create a Range List that will return data going across columns, 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 going down rows, and for each customer the user would like to list out all the months in the year to show monthly sales. The customer number Range List for rows data has already been created in cell A2. For more information about Rows Range Lists please see the related KB article Creating Rows Range Lists.

The steps below will cover how to add in the columns Range List for months. 

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, the user would like to return a list of months in cell B1 that will go across columns. The user plans to add a function into cell B2 to return the sales for each customer for each month, so the columns Range List in column B will also need to include cell B2.

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 B1:B2 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 Range Lists -> 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 "Month Year" field in the "Date" table) and click ADD.

This will insert a Range List into the report in the upper-right corner of the selected range as shown below.

The default function name that is displayed will represent the field name that the function is returning. The user can adjust the function name in the Range Lists -> Name section of the Task Pane.

When the report is run the customer numbers and month names will then be returned in the report as shown below.