[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamically Remove a Group without removing data

Posted on 2011-04-22
13
Medium Priority
?
2,815 Views
Last Modified: 2012-06-22
My data has a column for Sales Person and one for Customer.  I need to be able to dynamically group the report by just Customer (alphabetically) or by Sales Person and then Customer.  I added 2 groups, a parent group for Sales Person and Child group for Customer.  If I hide the Sales Person group with a parameter, nothing shows on the report when it should just show the list of Customers.  

How can I achieve this?  (I'm using SSRS 2008)


Sales Person    Customer
-------------------------------------------
Person A            Customer 1
Person A            Customer 3

Person B            Customer 2

- or -

Customer
---------------------
Customer 1
Customer 2
Customer 3


0
Comment
Question by:CarlaGibson
  • 7
  • 5
13 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35450010
The trick is not to hide the Sales Person group based on the parameter, but to change the grouping based on the parameter. So for the "Group on" property of the Sales Person group you use an expression instead of just the Sales person field. The expression would be something like this:
=iif(Parameters!GrouponSP.Value ,Fields!SalesPerson.Value,1)
I haven't tested it so it might need a little tweaking, but I hope you get the idea. I assumed the parameter is GrouponSP and the Salesperson field is SalesPerson. Based on the value of the parameter this expression will return the Sales person or always 1, that last resulting in no grouping.
Next step is to hide the SalesPerson column based on the parameter, but since you know how to hide the group, I assume you know how to hide the column.

This technique can even be expanded so the user can select from a list of fields, the field to group on.
0
 

Author Comment

by:CarlaGibson
ID: 35450176
Thanks for your help!!

I already tried that.  The grouping works fine like that, but the 'Column Visibility' option is disabled.  So when I do that, I have a blank column.  Is there another way to hide the column if 'Column Visibility' is disabled on the right click menu?  (I'm new to SSRS.)  I'm using a tablix.

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35451736
1. Create GRoup for Sales Person (Order by Sales Person)
2. Details have Sales Person, Customer (Report order by Customer)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

by:CarlaGibson
ID: 35451799
The report has to group by customer as well as sales person since it summarizes other items.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35451943
Please first try PatelAlpesh's suggestion. But if that doesn't help take a look at the example in this post. I think this does exactly what you asked for.
The example doesn't need a table in the database, but you need to change the data source of the data set of course.
I don't know why your 'Column Visibility' option is disabled. Might be because you have merged cells. Or because I used 2008 R2. Or it is because of this:
http://www.sqldev.org/sql-server-reporting-services/how-to-disableremove-a-group-based-on-a-parameter-18635.shtml
(search for the part that starts with: "This was easier in SSRS 2005")
If you can't hide the column. Maybe you can set the width to 0 and get almost the same result.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35451949
Sorry forgot the attachement. It's not allowed to upload an rdl. So I renamed it to .xml. Please rename it back to .rdl
DynamicGroup.xml
0
 

Author Comment

by:CarlaGibson
ID: 35460298
Nicobo, That is exactly what I am looking for.  But in your rdl file, you use the 'Column Visibility' option to hide SalesPerson.  My 'Column Visibility' is disabled for all columns.  This report will be exported to Excel.

Here's a screenshot of my report.  

Is it possible that I set up the tablix wrong?  Although I'm not a novice programmer, this is my first time using SSRS.  I have plenty of experience using Crystal Reports 8.5.
ReportScreenshot.JPG
0
 

Author Comment

by:CarlaGibson
ID: 35462136
Nicobo, I think I figured out why your tablix is different than mine.  I have dynamic columns in the details as well as dynamic rows.  When I added a dynamic column to your report, it disabled the 'Column Visibility' option.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35462262
Did you try already to use an expression for the width of the column. You could set this to 0 to hide the column. Might not be 100% what your are looking for, but might be good enough.
0
 

Author Comment

by:CarlaGibson
ID: 35462581
Where do I find the column width expression?  All I could find was a size and there was no expression option.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35465013
I think the key to your answer lies in this post:
http://www.sqldev.org/sql-server-reporting-services/how-to-disableremove-a-group-based-on-a-parameter-18635.shtml
And then the part:
This was easier in SSRS 2005 and you have to dig a little deeper in the design environment to make this work in SSRS 2008. In order to change the visibility of a column, ithas to be defined as a static tablix member, within the grouped area of the data region. This means that only columns on the right side of the double-dashed vertical line in design view may be hidden. If you add a new column for each row group to the columns in the group area, you can set the textbox for each to HideDuplicates - then delete the original row group header columns. It helps to enable Advanced Mode (small down arrow to the right of the group pane, below the designer window.)
I had to read it 10 times to understand it. But you see the double-dashed lines in your screenshot. Right of column 3 and below row 2.
Right click the 4th column of your report and select Insert column, Outside Group - Left. This new column will be inside of the double-dashed lines, and you can set the column visibility of this column. Copy the controls from your third column into this new column and delete the old column (only the column not the grouping). Then use the column visibility to hide the column if there is no grouping on sales person.
0
 

Author Comment

by:CarlaGibson
ID: 35466493
It worked!!  Thank you!

I had read that paragraph several times and never really understood what it was saying.  Thank you for clarifying.
0
 

Author Closing Comment

by:CarlaGibson
ID: 35466498
Thank you so much!!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
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…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

825 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