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 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 count the number of transactions for the customer, so the user would then navigate to the Sales Transactions table, click on it to expand it, and then click on the dropdown arrow for the Record ID field. Finally they'd click on the Count Records option. 

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 Task Pane. The following fields can be seen and updated by the user:

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

Field: Represents the field that the function will perform the aggregate function from. For our example, this will be the Record ID field. 

Reverse Sign: This checkbox will allow you to reverse the sign on field being used to perform the aggregate function. 

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