Overview

The Sum function is one of the more commonly used functions in Cosmos and enables the user to sum up values across many transactions. 

An example of this would be a Range List that has customer numbers listed out going down rows, and a Sum function that could then total the sales for each customer for a specified date range.

Details

Sum functions 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/Lookup functions. If they are placed within the region of a Range List, the Sum function will automatically be copied down or across for all the Range List values that are returned. The Sum function will also inherit any Report Options that are related to the field that the function is summing up if the Report Option was added to the report prior to the Sum function being added. If the Report Option is added later the user will need to manually add the Report Option filter to the Sum function.

Inserting a Sum Function

In the example below, there has been a row Range List created to list out customer numbers in cell A2, along with descriptive information for things such as the customer's name, city, and country. The region for this Range List is set to the selected section in the screenshot, which is A2:E2.

To insert a Sum function, the user should click the cell for the function to be placed in. For this example, we will want to insert a Sum function to total sales for each customer, so the user will click in cell E2.

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 total sales for the customer, so the user will want to make sure that the Type is set to Sum, which will tell Cosmos to sum up all values for a specified field based on the filters specified. The user would then navigate to the Sales Transactions table, click on it to expand it, and then click on the Sales field.

Cosmos will then insert a function in cell E2 and a zero will be shown in that cell to represent that the field that will be returned is numeric. Any number formatting that the user wishes to apply to the field can be set using standard Excel formatting. In the example below the Accounting format was selected for U.S. Dollars.

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

Function Section

Name: The name of the function. This should be a short, descriptive name to enable the user or other users to know what the function does.

Operator: This defines the type of aggregate function that the field function will return. The options are:

• Sum: Will sum up a numeric field specified by the user matching the specified filter criteria
• Count Records: Will count the number of records in a table matching the specified filter criteria
• Count Unique Values: Will count the number of distinct values for a specific field in a table matching the specified filter criteria

Table: Represents the table that the function will perform the aggregate function from. For our example, this will be the table that the function is summing records from.

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.

Filters Section

Filter Field: The field in the table that the filter will be applied to. The user can filter on any field in the existing table as well as any field in any related table. More details on working with filters can be found in the article below:

Working with Report Filters

Operator: The operator that will be applied to the filter. Possible options include things like =, <>, >, <, etc.

Filter Value: This is the filter value that the user wants to apply to the filter. The user can enter a value manually and press Enter, the user can click the dropdown to select a Range List to dynamically filter on, or the user can click the dropdown and select "Empty" to tell the function to only return results where the field is empty.

Deleting a Sum Function

To delete a Sum 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.