Dynamically Remove a Group without removing data

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


CarlaGibsonAsked:
Who is Participating?
 
Nico BontenbalCommented:
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
 
Nico BontenbalCommented:
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
 
CarlaGibsonAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Alpesh PatelAssistant ConsultantCommented:
1. Create GRoup for Sales Person (Order by Sales Person)
2. Details have Sales Person, Customer (Report order by Customer)
0
 
CarlaGibsonAuthor Commented:
The report has to group by customer as well as sales person since it summarizes other items.
0
 
Nico BontenbalCommented:
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
 
Nico BontenbalCommented:
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
 
CarlaGibsonAuthor Commented:
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
 
CarlaGibsonAuthor Commented:
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
 
Nico BontenbalCommented:
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
 
CarlaGibsonAuthor Commented:
Where do I find the column width expression?  All I could find was a size and there was no expression option.
0
 
CarlaGibsonAuthor Commented:
It worked!!  Thank you!

I had read that paragraph several times and never really understood what it was saying.  Thank you for clarifying.
0
 
CarlaGibsonAuthor Commented:
Thank you so much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.