Solved

SSRS 2008, summary section

Posted on 2014-12-11
5
140 Views
Last Modified: 2014-12-12
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
0
Comment
Question by:shmz
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40495400
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
 

Author Comment

by:shmz
ID: 40495454
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40495468
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 40495754
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
 

Author Closing Comment

by:shmz
ID: 40496153
Thank you
Worked beautifully
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now