To create a wide variety of dynamic reports there are several different Cosmos functions available. This article will cover an overview of each type and how it can be used when creating reports.
Separate articles are available to cover the details of each function type.
The different Cosmos function types available are described below.
Function that enables users to specify filters that should be applied throughout the entire report. These filters can be set by the user when the report is run.
Function that creates lists of values across sheets.
- Sheets Example: A monthly commission report where each salesperson that had a sale in the month is displayed on a separate sheet. Depending on the month for which the report is run, different salespeople will be dynamically returned based on who had sales that month.
Function that creates lists of values within sheets. Range Lists can be configured to return lists of data either going down rows or across columns.
- Rows Example: A trial balance report that lists out all accounts that are present in the chart of accounts. If a user adds a new account and re-runs the report the Range List will dynamically update to include the new account without any further action required by the user.
- Columns Example: A customer sales by month report where the user specifies a date range and the months are dynamically created across columns. By changing the date range to include additional months these months will dynamically update to include the new months without any further action required by the user
Function that sums or counts transactions and returns the total in the report. Aggregates can be used anywhere in the report and are not required to be placed within the region of a Range List as is the case with Field Lookups. If they are placed within the region of a Range List, the Aggregates function will automatically be copied down or across for all the Range List values that are returned.
- Aggregate Example: A user creates a Range List to return all customers with sales during a date range. The user can then use an Aggregate function, such as Sum, to total up all the transactions for each customer during the date range and return the total in the report.
Function that returns lookup values to provide additional information in the report; these are typically used in conjunction with Range Lists. Field Lookups are used to bring in additional fields, such as a name or an address, related to other values in the report.
- Lookup Example: A user creates a Range List to return all customers with sales during a date range. The user can then use a Lookup function to retrieve additional information about each customer, such as their Name, City, State, and Country Code. This data may reside in a different table from the one used in the Range List.