CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008ssrs-2012
himanshu malik
himanshu malik

Reputation: 25

Count the SSRS Dataset rows after applying the filter

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?

Upvotes: 1

Views: 4992

Answers (1)

Strawberryshrub
Strawberryshrub

Reputation: 3389

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))

Upvotes: 3

Related Questions