CODEWITHSUNDEEP
reporting-servicesgroup-byfilteringaggregatessrs-2012
Leslie
Leslie

Reputation: 3644

how to group and filter on aggregate in SSRS

I have a requirement to report the number of people who have more than one record in the dataset for my SSRS report and I can't quite get how to filter on the grouping.

So if the dataset results are:

ID     PersonID    FileID
1      1234         abc
2      7890         ade
3      5647         aer
4      1234         xyz

I would like to report 1. There is one person who has more than 1 record.

Is there an expression or something I can use to do this?

Thank you.

Upvotes: 0

Views: 4094

Answers (2)

alejandro zuleta
alejandro zuleta

Reputation: 14108

You can use LookupSet and CountDistinct function to get the required count, however you will need the textbox used to show the calculation be in a scope.

If you want to show the number of persons with more than one record as a total in your table use this expression:

=CountDistinct(
IIF(
  LookupSet(
    Fields!PersonID.Value,Fields!PersonID.Value,
    Fields!ID.Value,"DataSetName"
  ).Length>1,Fields!PersonID.Value,Nothing)
)

Set it outside any group scope:

enter image description here

However if you want to show the number of persons with more than one record outside your tablix in a textbox, you can add an additional tablix and delete the necessary rows and columns to leave only one textbox then set the dataset property to the dataset name you are using and use the same expression.

enter image description here

It should produce:

enter image description here

Note my dataset has more rows to ilustrate the functionality. In the right side there is only one textbox with the count.

Let me know if this helps.

Upvotes: 1

Pintu Kawar
Pintu Kawar

Reputation: 2156

If you want the result to be something like shown below.

Steps:

  1. Create a group on Person ID
  2. Right Click on Group > Add Total > Before
  3. Add a column and put =Count(Fields!PersonID.Value)
  4. If you want to display only Persons having more than one, set the visibility property of the tablix row.

enter image description here

Upvotes: 0

Related Questions