Solved

SSRS 2008, summary section

Posted on 2014-12-11
5
146 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

687 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