Overview

The Count Records function enables the user to quickly count the number of records in a table that matches the filter criteria. 

An example of this would be a Range List that has customer numbers listed out going down rows, and a Count Records function that could then count the number of records for each customer for a specified date range.

Details

Count Records 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 Count Records function will automatically be copied down or across for all the Range List values that are returned. The Count Records function will also inherit any Report Options that are related to the table that the records are being counted from if the Report Option was added to the report prior to the Count Records function being added. If the Report Option is added later the user will need to manually add the Report Option filter to the Count Records function.

Inserting a Count Records 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 Count Records function, the user should click the cell for the function to be placed in. For this example, we will want to insert a Count Records function to count the transactions 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 count the number of transactions for the customer, so the user will want to make sure that the Type is set to Count Records, which will tell Cosmos to count the records in the table 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 Record ID 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 Comma Style format was selected with no decimals.

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 counting records from.

Field: Since the Count Records function will count all records in the table matching specified filters, and not related to any specific field, the Field will not be displayed when Count Records is selected, unlike the other aggregate functions.

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 Count Records Function

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