Overview

Field functions are the tools within Cosmos that enable the user to add details and context to a report. Whereas the Range List functions for rows and columns will dynamics create lists of information, such as customer numbers, the Field functions will provide all the context for the customer numbers that make the report useful, such as bringing in the name of the customer or the total sales for the customer for a particular period. 

Field functions are broken down into four categories:

• Field Lookups
• Sum
• Count Records
• Count Unique Values

This article will cover an overview of each of the four categories and additional information can be found in the relevant Knowledgebase article for each which can be found below:

How to Create a Field Lookup Function

How to Create a Sum Function

How to Count the Number of Records

How to Count Distinct Values

Details

While Range Lists typically form the backbone of many reports by enabling the user to create large amounts of dynamic content listed across rows, columns, or sheets (when using Sheet Lists), Field functions add the context that adds value to the reports.

Field functions can either return descriptive pieces of information, such as names, cities, descriptions, and so on, or they can aggregate data from transaction tables in the form of sums, counts of records, or counts of unique values. 

Working with Field Lookup Functions

When building reports, Field Lookup functions are meant to bring in descriptive context for values being returned by a Range List may return customer numbers with sales during a particular time period, and a Field Lookup function (or a series of them) can be used to retrieve data such as the customer's name, country, salesperson code, etc.

Unlike Aggregate functions, which can be placed anywhere in a report, including outside of a Range List, the Field Lookup functions can only be placed within a Range List region. 

For a detailed description of Field/Lookup function refer to our Knowledgebase article here:

How to Create a Field Lookup Function

Working with Aggregate Functions

Whereas Field Lookup functions are meant to go and retrieve a specific piece of information about a list function, Aggregate functions are meant to look at a large number of records, such as sales transactions and roll up the results based on the type of aggregation that the user wants to perform. The three types of Aggregate functions are:

• Sum: Will sum up a numeric field specified by the user matching the specified filter criteria
  • Details: How to Create a Sum Function
  • Example: Return a sum of all sales transactions for a specific customer for April 2022.
• Count Records: Will count the number of records in a table matching the specified filter criteria
  • Detail: How to Count the Number of Records 
  • Example: Return the number of GL transactions that posted to a particular account yesterday.
• Count Unique Values: Will count the number of distinct values for a specific field in a table matching the specified filter criteria
  • Details: How to Count Distinct Values 
  • Example: Will return how many different invoices were posted for a particular vendor last quarter.