SSRS 2008, summary section


I have a report with a parameter to display detailed or summary section.

The detailed look like:

ID. Name.   Address.  Work_place.    Amount
1.   Sara.     11 back st.     X.                 120
1.    Sara.     11 back st.    Y.                   200
2.    Jason.    34 north st.   Y.                   30
3.    Mike.      56 south st.   Y.                  159
3.    Mike.      56 north st.    Y.                  200

When I select summary I expect to see:
ID. Name.   Address.  Work_place.    Amount
1.   Sara.     11 back st.                      320
2.    Jason.  34 north st.   Y.                   30
3      Mike                           Y.                 359

As you see, I like to make changes in the report so that I summarize data for a given ID (which I have already done) but display columns only when it is identical for all records of that ID, if not it doesn't display it.
Like mike doesn't have a address or Sara doesn't have a workplace displayed.

No changes I want to make to query so please dont ask for query.
I like to do it within report itself, perhaps by Expressions??? I am basically stuck on how to check if multiple record per column for a given ID is identical=> display it and if not do not display

Many thanks in advance
Who is Participating?
ValentinoVConnect With a Mentor BI ConsultantCommented:
You can use CountDistinct to hide/show data dependent on the number of different values in the group.  In your case that would be something like this:

=IIF(CountDistinct(Fields!Work_Place.Value) > 1, Nothing, First(Fields!Work_Place.Value))

Use the above expression for the "grouped version" of that Work_Place column.
Koen Van WielinkBusiness Intelligence SpecialistCommented:
The easiest way around this is to include a group which summarizes the information. Below that you display the details. The summary parameter would hide these details. If it's not selected, all the lines PLUS the summary group will be visible. If you for instance use a bold font for the group summary, and indent the line details a bit, it should be quite clear that the group header is the summary of the details below it. You can set the visibility expression on the detail line properties (set the expression to your parameter value). I believe true = Hidden, false = Visible, but it could be the other way around. I always forget.
shmzAuthor Commented:
I have already set this up however how does table and grouping knows that for example in case ID:1 there are 2 different work_place and it should be hidden?
Koen Van WielinkBusiness Intelligence SpecialistCommented:
No, you can only summarize details in the group header/footer that are the same for all records. So in this case, I'd leave off the work_place for all ID's, not just the ones where there are multiple values for the same ID. The workplace can still be displayed in the detail lines.
shmzAuthor Commented:
Thank you
Worked beautifully
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.