This SSRS article explains how to calculate the distinct running count or unique running count using RunningValue in a particular group or complete table with an example.
To demonstrate the SSRS Distinct Running Count calculation, we use the Employee table below, which has 15 records grouped by Occupation. Next, we used the CountDistinct function to display the Distinct Count of Education and Income columns per group and table. The image below shows the records in the Table report.
The CountDistinct expressions that we used in the below report are:
Distinct Count Per Group
=CountDistinct(Fields!Education.Value)
=CountDistinct(Fields!YearlyIncome.Value)
Distinct Count Per Table
=CountDistinct(Fields!Education.Value, "Tablix1")
=CountDistinct(Fields!YearlyIncome.Value, "Tablix1")
SSRS Distinct Running Count Example
For this, let me add a new column to the right side of the Education and name the headers as the Dcnt Edu. Next, right-click the textbox under the Dcnt Edu and choose the Expression. To understand the report, I suggest you refer to the CountDistinct, Charts, Table, Grouping, and Format Table articles in SSRS.
It opens the following expression window to calculate the distinct running count. To get the same, we have to use the aggregate CountDistinct function. The below SSRS RunningValue code calculates the distinct running count of the Education for each Occupation group.
=RunningValue(Fields!Education.Value, CountDistinct, "Occupation")
Similarly, add a new column to the Income right and name it Dcnt Inc. Next, right-click the textbox under Dcnt Inc and write the expression below. The code below will find the distinct income running count for each Occupation group.
=RunningValue(Fields!YearlyIncome.Value, CountDistinct, "Occupation")
Please click the preview tab to see each occupation group’s distinct running count for employee education and yearly income. For more functions >> Click Here!
Let me create two more new SSRS columns and name them FDC Edu and FDC Inc to calculate the distinct running count for the whole table. To do so, you must replace the group name (“Occupation”) with the table name (“Tablix1”) or DataSet name (“DSet”). First, add the RunningValue expression below for the textbox under the FDC Edu header.
=RunningValue(Fields!Education.Value, CountDistinct, "DSet")
Similarly, write the expression below for the textbox under FDC Inc to find the distinct running count of the income in a table.
=RunningValue(Fields!YearlyIncome.Value, CountDistinct, "DSet")
The SSRS report below calculates the distinct running count of the education and yearly income for the individual occupation group and the complete employee table.
