The SSRS Count Function is an aggregate function that counts all the items (records) from the given field and returns the number. This article explains how to use the SSRS Count function to find the total items in a particular group or complete table with an example.
The syntax of the SSRS Count Function to find the count of total items in a Default scope is as shown below.
Count(Fields!SalesAmount.Value)
Count(Fields!EmployeeName.Value)
To Find the Count value for each Group, Table, or Data Set, replace the name with the Group, Tablix, or Data Set name.
Count(Fields!EmployeeName.Value, "Name")
Count(Fields!EmployeeName.Value, "Name", Recursive)
To demonstrate the SSRS Count Function, we use the Employee table below, which has 15 records grouped by Occupation. The image shows the records in the Table report.
SSRS Count Function Example
Right-click on any field of the Details row, choose Insert a Row, and select the Outside Group—Below option to add a new row. Next, Merge the first two columns and add Count Total Items Per Group as the text. To understand the report, I suggest you refer to the Charts, Table, Grouping, and Format Table articles in SSRS.
There are multiple ways to write the SSRS Count function. The first approach is to click the cell and choose the YearlyIncome field. It will assign the Sum function to it because it is the default aggregate function. So, select the Text (Sum(YearlyIncome)), choose Summarize by, and click the Count function. It internally writes the below expression:
=Count(Fields!YearlyIncome.Value)
The second approach is to right-click the textbox and choose the Expression option. This opens the following expression window to write the more complex SSRS Count function along with the scope. The code below returns the Count of total sales items (rows).
=Count(Fields!Sales.Value)
Similarly, let me add the below expression to count the string columns. For more functions >> Click Here!
=Count(Fields!Education.Value)
Please click the preview tab for the Count of Employee Education, yearly income, and Sales. If you observe the report, by default, it displays the Count value in each Occupation group because it is the default scope.
SSRS Count Function Scope Demonstration
For this, let me add a new column to the right side of the Sales and name the header as the CNT Sale. Next, right-click the textbox and choose the Expression.
The below SSRS Count function expression returns the same result as shown by the Count Total Items Per Group row. However, you have more control because we manually changed the Count function scope.
=Count(Fields!Education.Value, "Occupation")
The expression below multiplies the individual employee sales by the number of sales records in each occupation group. Remember, “Occupation” is the group name.
=Count(Fields!Education.Value, "Occupation") * Fields!Sales.Value
If you check the report preview, CNT Sale of Christy Mehta: 4 (Clerical Count) * 24.99 (Sale) = 99.96.
As mentioned earlier, to find the count value of the whole table or all the employees, you must replace the group name (“Occupation”) with the table name (“Tablix1”). Let me add an extra row outside the Group and name it as Count Total Items Per Table. Next, add the two SSRS Count function expressions for Education, Yearly Income, and Sales below.
=Count(Fields!Education.Value, "Tablix1")
=Count(Fields!YearlyIncome.Value, "Tablix1")
=Count(Fields!Sales.Value, "Tablix1")
The report below shows the count of total items or records in each group and table. Please replace Tablix1 with the Dataset name to get the Sales Count regardless of the filters applied to the report.
