Solved

SSRS 2008, summary section

Posted on 2014-12-11
5
143 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 13

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 13

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 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

11 Experts available now in Live!

Get 1:1 Help Now