The SSRS CountDistinct Function is an aggregate function that counts the distinct or unique items (records) from the given field and returns the number. This article explains how to use the SSRS CountDistinct function to find the unique distinct items in a particular group or complete table with an example.
The syntax of the SSRS CountDistinct Function to find the distinct count of items in a Default scope is as shown below.
CountDistinct(Fields!SalesAmount.Value)
CountDistinct(Fields!EmployeeName.Value)
To Find the Distinct Count value for each Group, Table, or Data Set, replace the name with the Group, Tablix, or Data Set name.
CountDistinct(Fields!EmployeeName.Value, "Name")
CountDistinct(Fields!EmployeeName.Value, "Name", Recursive)
To demonstrate the SSRS CountDistinct Function, we use the Employee table below, which has 15 records grouped by Occupation. The image shows the records in the Table report.
SSRS CountDistinct 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 Distinct Count 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 CountDistinct 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 CountDistinct function. It internally writes the below expression:
=CountDistinct(Fields!YearlyIncome.Value)
The second approach is to right-click the textbox and choose the Expression option. It opens the following expression window to write the more complex SSRS CountDistinct function along with the scope. The code below returns the Distinct Count of total sales items (rows).
=CountDistinct(Fields!Sales.Value)
Similarly, let me add the expression below to count the string columns uniquely.
=CountDistinct(Fields!Education.Value)
Please click the preview tab for the Distinct Count of Employee Education, yearly income, and Sales. If you observe the report, by default, it displays the count of unique values in each Occupation group because it is the default scope.
SSRS CountDistinct Function Scope Example
For this, let me add a new column to the right side of the Sales and name the header as the DCNT Sale. Next, right-click the textbox and choose the Expression. For more functions >> Click Here!
The below SSRS CountDistinct Function expression returns the same result as shown by the Distinct Count Per Group row. However, you have more control because we manually changed the CountDistinct function scope.
=CountDistinct(Fields!Education.Value, "Occupation")
The expression below multiplies the individual employee sales by the count of unique (distinct) education records in each occupation group. Remember, “Occupation” is the group name.
=CountDistinct(Fields!Education.Value, "Occupation") * Fields!Sales.Value
From the report preview, DCNT Sale of Christy Mehta: 2 (Clerical Distinct Education Count) * 24.99 (Sale) = 49.98.
As mentioned earlier, you must replace the group (“Occupation”) with the table name to find the distinct count of the table or all the employees. Let me add an extra row outside the Group and name it Distinct Count Per Table. Next, add the five SSRS CountDistinct function expressions for the First Name, Last Name, Education, Yearly Income, and Sales below.
=CountDistinct(Fields!FirstName.Value, "Tablix1")
=CountDistinct(Fields!LastName.Value, "Tablix1")
=CountDistinct(Fields!Education.Value, "Tablix1")
=CountDistinct(Fields!YearlyIncome.Value, "Tablix1")
=CountDistinct(Fields!Sales.Value, "Tablix1")
The report below shows the distinct count of total items or records in each group and the whole table. Please replace Tablix1 with the Dataset name to get the Distinct Sales Count irrespective of the report filters.
