The SSRS CountRows Function is an aggregate function that counts the total rows from the given field and returns the number. This article explains using the SSRS CountRows function to find the total rows in a particular group or complete table with an example.
The syntax of the SSRS CountRows Function to count the total rows in a Default scope is as shown below.
=CountRows()
To count the total rows for each group, table, or data set, replace the name with the group name, tablix name, or data set name.
=CountRows("Name")
=CountRows("Name", Recursive)
To demonstrate the SSRS CountRows Function, we use the Employee table below, which has 15 records grouped by Occupation. The image shows the records in the Table report.
SSRS CountRows 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 Rows Per Group as the text. To understand the report, I suggest you refer to the Charts, Table, Grouping, and Format Table articles in SSRS.
To write the SSRS CountRows function, right-click the textbox and choose the Expression option. It opens the following expression window to write the CountRows function along with the scope. The code below Counts the total rows in the Yearly Income column.
=CountRows("Occupation")
Do the same for the Sales Column. For more functions >> Click Here!
=CountRows("Occupation")
Similarly, let me add the below SSRS CountRows Function expression to count the Education string column rows. Here, we haven’t used the Group name, and it takes the default scope, “Occupation.”
=CountRows()
Please click the preview tab to see the Count of total rows in Employee Education, Yearly Income, and Sales. The report shows the count of total rows in each Occupation group by default because it is the default scope.
CountRows Scope Example
Next, add a new column to the right side of the Sales and name the header as the Cnt Row Sale. Next, right-click the textbox and choose the Expression.
The below SSRS CountRows Function expression multiplies the individual employee sales with the total rows in each occupation group. Remember, “Occupation” is the group name.
=CountRows("Occupation") * Fields!Sales.Value
If you check the report preview, Cnt Row Sale of Christy Mehta: 4 (Clerical Row Count) * 24.99 (Sale) = 99.96.
As mentioned earlier, to count the total rows 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 Count Rows Per Table. Next, add the four SSRS CountRows function expressions below for the Education, Yearly Income, Sales, and Cnt Row Sale.
=CountRows()
=CountRows("Tablix1")
=CountRows("Tablix1")
=CountRows("DSet")
The report below shows the count of total rows in each group, the whole table, and the DataSet. Using the Dataset name preserves (does not change) the row count value irrespective of the filters applied to the report.
