SSRS 2008, summary section

Hi,

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
shmzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
shmzAuthor Commented:
Hi
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?
0
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.
0
ValentinoVBI 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shmzAuthor Commented:
Thank you
Worked beautifully
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.