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 click on the Aggregates tab of the Cosmos Task Pane and click the New 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. The user would then navigate to the Sales Transactions table, click on it to expand it, and then click on SUM for 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 Task Pane. The following fields can be seen and updated by the user:

Aggregates 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.

General

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

Data

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.

Reverse Sign: Checking this box will reverse the sign of the Sum. Making positive numbers negative or negative numbers positive. 

Filters Section

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. First select the type of filter that should be used. There are three options: Static, Dynamic, and Special. Then select the value to be used in the filter. 

• Static: This allows the user to type in the specific value they want to filter by
• Dynamic: This allows the user to filter on any Sheet Lists, Range List Sets, or Excel Links in the report
• Special: This allows the user to filter using Empty Values

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 upper-right hand corner of the Aggregates tab in the Cosmos Task Pane. 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.