Count the SSRS Dataset rows after applying the filter

Question

So I have a dataset in SSRS called "CRMDecisions". I am counting the number of rows for this Dataset using the following expression in a text-box outside of the tablix:

=CountRows("CRMDecisions")

Now, this dataset also has a number of duplicate records and i want to toggle between the unique counts and everything including the duplicate counts. I have used the below filter to hide the duplicate rows I don't want to see:

=IIF(Fields!Company.Value = Previous(Fields!Company.Value) AND 
Fields!DaysAway.Value = Previous(Fields!DaysAway.Value) AND 
Parameters!ShowPortfolio.Value = "False", True, False)

Right now, I get a count of all the rows no matter what filter view I am using. How can I get the count of the dataset after removing the duplicate rows?

Have you just hidden the rows in the table/matrix or have you filtered them in the dataset properties? — Alan Schofield
You can use SUM(IIF(Fields!Company.Value = Previous(Fields!Company.Value),1,0) — Tony Dong
@AlanSchofield I have hidden them in the "Row Visibility" >> "Show or hide based on an expression", where the first two conditions are being used to remove duplicates and the third one is based on boolean parameter — himanshu malik
@TonyDong this gives me an error: The Value expression for the textrun ‘Textbox180.Paragraphs[0].TextRuns[0]’ uses a Previous function in an outer aggregate. Previous functions cannot be specified as nested aggregates. — himanshu malik
@himanshumalik Could you add extra column to has this number 1 or 0 so you can sum it easily? — Tony Dong

Strawberryshrub Strawberryshrub · Accepted Answer · 2018-11-07T05:42:33

For counting all the rows you can use (like you already did):

=CountRows("CRMDecisions")

For counting all the unique rows (without duplicate) you can use the following expression (note that you need put the field into the expression which holds the duplicates):

=CountDistinct(Fields!CompanyWithDuplicates.Value)

If you want some special counts you can always use this expression:

=Sum(IIF(       'Your True condition'      , 1, 0))
=Sum(IIF(Fields!Company.Value = "MyCompany", 1, 0))

Count the SSRS Dataset rows after applying the filter

1 Answers