Overview

The Field/Lookup function is a core function in Cosmos and enables the user to retrieve descriptive information about a Range List quickly and easily. 

An example of this would be a Range List that has customer numbers listed out going down rows, and Field/Lookup functions that could then pull in data related to each customer number such as the customer's name, city, state, and country.

Details

To use Field/Lookup functions there must be Range Lists present in the report. Whereas aggregate functions, such as Sum, Count Records, and Count Distinct Values can be used in a standalone manner and placed anywhere in the report, Field/Lookup functions are dependent on Range Lists and must be placed with the Range List region.

Inserting a Field/Lookup Function

In the example below, there has been a row Range List created to list out customer numbers in cell A2. The region for this Range List is set to the selected section in the screenshot, which is A2:D2.

To insert a Field/Lookup function, the user should click the cell for the function to be placed in. For this example, we will want to insert the customer's name, so the user will click in cell B2.

The user will then expand the Fields section of the Cosmos Report Editor and click the Add Field Function button.

The user will then be shown the data model and can select which table and field to return. In this example we want to return the customer's name, so the user will want to make sure that the Type is set to Field/Lookup, which will tell Cosmos to go and grab the value related to the Range List. The user would then navigate to the Customer table, click on it to expand it, and then click on the Name field.

Cosmos will then insert a function in cell B2 and "Name" will be shown in that cell to represent the field that will be returned.

If the user selects cell B2, they can then see details about the function in the Cosmos Report Editor. The following fields can be seen and updated by the user:

Name: The name of the function. Updating this will also update the name that is displayed in the cell in which the function is located.

List Function: The Range List that the function is bound to. Cosmos will automatically link the Field/Lookup function to the Range List that includes the cell the function is being added into. If there are multiple Range Lists that cover the Field/Lookup Function, such as placing the function at the intersection of a rows Range List and a columns Range list, the user will be asked to pick which Range List the Field/Lookup function should be bound to. The user can change the List Function that the Field/Lookup function is bound to by clicking the dropdown.

Field: Represents the field that will be returned in the cell when the report is run. The user can click the dropdown and select a different field to be returned. If the Field is changed the user should remember to also rename the function, if relevant, to avoid confusion around the function name returned in the cell the function is located in.

Deleting a Field/Lookup Function

To delete a Field/Lookup function the user should select the cell that contains the function to be deleted and click the Delete button in the lower-right hand corner of the Fields section in the Cosmos Report Editor.

This will permanently remove the function from the report. Once the function has been deleted the user will need to follow the steps to add the function into the report if they would like to get it back.