 |
|
Drill Down Techniques in SSRS with the Visibility Property
|
By: Rob Fisch | Read Comments (18) | Related Tips: > Reporting Services Drill Through |
Problem
Reporting Services offers many standard solutions out-of-the-box. However, controlling how data is displayed can be frustrating without understanding some of the subtleties of the Reporting Services report designer. Users can get confused and turned off, if the presentation of data is less than perfect. You can create a fairly nice drilldown using the Report Wizard, but how about conditionally opening some groupings and not others? What about starting a drill down report with all the groups expanded? How do you change the behavior of the +/- signs (which is not always intuitive)?
Solution
The solution to these challenges is to understand some subtle techniques when using the Reporting Services report designer. This discussion will demonstration three specific techniques. All of the materials needed for this demonstration are included so you can try this yourself. The techniques are the following:
- Technique #1 – The Standard “Drilldown”
- Technique #2 – The Reverse “Drilldown”
- Technique #3 - Conditional Initial Visibility
Follow along and you'll be a drill down Pro in no time at all.
Sample Data
The following examples use a fictitious table about the availability of fruit from different suppliers. If you want to follow along with your own system, create a database called zVisibilityLab. Once created use this script to create the table.
Technique #1 – The Standard “Drilldown”
Setup a new report to use the VISIBILITY_EXAMPLE_TABLE as a datasource. Use the following query:
FROM VISIBILITY_EXAMPLE_TABLE
Setup a standard table. When you are done it should look something like this:
The preview will look like:
Next add a grouping for Region.
Select the Supplier Region field from the dropdown.
I have also merged all cells in the group header, deleted the group footer and added border styles of "solid" for everything. When you are done it may look something like this:
Now for the Drilldown
Check the properties of the group header textbox. Note the "Name" property. It should be something like "SupplierRegion_1" (or something close to that).
Now we have to get to the Details Grouping properties. (There's something about that that doesn't make sense, but go with me here.) There are two ways (that I know of) to get there. 1) Right click on the details line and select Edit Group (even though it's a details line), or 2) select the entire table, right-click, select Properties, then the Groups tab, then Details Grouping. Both these methods bring you to the same place.
Then click on the Visibility tab. Click Hidden, check the Visibility can be toggled... checkbox and select the SupplierRegion_1 textbox.
When you are done your preview should look like this:
Expand some of the times:
Technique #2 – The Reverse “Drilldown”
But let's say this is a small table and you want display the records expanded to open by default. There are two additional things to add to the above technique to make this work.
1) On your Visibility tab (of the Details Grouping), change the Initial Visibility to Visible.
After this, try out a preview. You will notice that the +/- signs do not work the way you would expect or want.
2) To reverse this, right click on the SupplierRegion_1 (group header, not the group details anymore) textbox properties. Select the Visibility tab, then change the Initial appearance of the toggle... to Expanded (-).
This will correct the +/- sign and you are all set to provide all details by default with the ability to collapse groupings for readability.
Technique #3 - Conditional Initial Visibility
Now let's say you want to open some groups but not others based on a certain condition. (How about by the number of records?). For the purposes of demonstration, I have added the following expression to the last cell in the group heading line.
This will display the number of rows for each group.
For the sake of this discussion, let's say that we want to display the records of a group only if the number is 4 or less.
In the Visibility tab of the Group Details section, under Initial Visibility instead of selecting "Visible" or "Hidden", select Expression and enter the following in the Expression field (or use the Expression Editor button).
*NOTE: Important - Make sure you use the "False" and "True" keywords. In many other cases, the IIF statement uses the keywords from the normal options (i.e. 'solid' & 'none' for BorderStyle). In this case, you cannot use "hidden" & "visible".
Once done, the report operates fine, but we have to enter a similar (but opposite) expression to fix the +/- signs again. To fix this, right click on the SupplierRegion_1 (group header, not the group details anymore) textbox properties. Select the Visibility tab, then change the Initial appearance of the toggle... to Expression and enter the following:
You see, the table conditionally expands where row counts are <=4. You have to click the "+" sign to expand groups of 5 or more.
Next Steps
- You can use a similar technique (technique #1) to show and hide columns as needed. For the toggle item, you can use an existing column header (but it can't be one of the hidden ones), or try adding an extra textbox or even an image (i.e. like a button) right above the table.
- Take a look at these other Reporting Services tips.
Last Update: 9/13/2007
About the author
View all my tips
Print
Comments and Feedback:
| Wednesday, June 03, 2009 - 11:56:20 AM - JENIQ | Read The Tip |
|
How do I do The Reverse “Drilldown” in SQL Server 2008 SSRS? I cannot find the same option in 2008. Thanks, Jennifer |
|
| Tuesday, June 01, 2010 - 10:31:37 AM - sqlboughen | Read The Tip |
|
Wow! Thanks! I have been trying to figure out how to do this for months...It's so easy in Crystal, but couldn't figure out how to do in SQL....this is great!!!! |
|
| Saturday, September 04, 2010 - 12:46:37 AM - Irshad | Read The Tip |
|
Hi Jeniffer, The reverse drilldown in SSRS 2008, is tied to the cell that you are using as the toggle item. As per the example, click on the textbox where the region name is displayed. On the properties, you would notice the InitialToggleState is set to false. Change it to true. Regards |
|
| Thursday, April 21, 2011 - 11:39:08 AM - Ravi | Read The Tip |
|
Very good article. I learn it very easily. Please keep on providing such articles with easy to understand and implement. Thank you, |
|
| Friday, January 20, 2012 - 6:21:04 AM - Imran Mohammad | Read The Tip |
|
Hi, Very good article. It worked like a charm in 2005. But we have the same requirement in 2008 and I couln't find the same toggle options for Technique #3. Can You plz suggest how to do this? |
|
| Friday, January 20, 2012 - 6:59:25 AM - Rob Fisch | Read The Tip |
|
Hi Imran, I believe the same functions exist in 2008, but they are in a different spot. In 2008 you control them from the "Row Groups" section after clicking on the "Advanced Mode" button. Here's a web page on a different topic, but it illustrates where the new controls are: http://remicaron.wordpress.com/2010/01/05/how-to-get-ssrs-to-repeat-the-row-headers-tablix-2008/
-Rob |
|
| Friday, February 17, 2012 - 2:03:09 PM - laxman | Read The Tip |
| hi how to get each row in one color in ssrs | |
| Thursday, March 08, 2012 - 10:53:02 AM - Jim | Read The Tip |
|
any way to do the conditional toggle if you are doing the report as a subscription? so when the user goes into SSRS it collapses with the +/- but if we set up a subscription to email them I would like it not collapsed and to get all of the detail |
|
| Thursday, March 08, 2012 - 11:38:55 AM - Rob Fisch | Read The Tip |
|
I don't think so, but I am not 100% positive. My recommendation would be to create a separate report for subscription purposes... |
|
| Tuesday, April 03, 2012 - 9:52:44 AM - Valarie Hollars | Read The Tip |
|
Can you condtionally ignore that the group was even set up and just display the details while also changing the sort?
BTW article was great!
Val |
|
| Wednesday, June 20, 2012 - 12:45:59 AM - AbdulRehman | Read The Tip |
|
SO easy way to make Group and Collapse View... THANKS !!~~~ |
|
| Friday, June 29, 2012 - 9:44:25 AM - Reena | Read The Tip |
|
Thank you for posting this! Great article! Solved my problem :) |
|
| Thursday, July 05, 2012 - 2:08:01 AM - Sanjay | Read The Tip |
|
From the post I understand that groupings has been done based on "Supplier Region". I was looking for similar thing with slight modification. I would depict my requirement using the same above example. At first go the report should look like this Supplier Region Quantity Available Drilling on "North East", data should look like this Supplier Region Product Name Quantity Available Drilling on "Apple", data should look like this Supplier Region Product Name Product Color Quantity Available Drilling on "Green", data should look like this Supplier Region Product Name Product Color Supplier Name Quantity Available Please do let me know if above is achievable using SSRS 2008. Thanks. Sanjay.
|
|
| Wednesday, July 11, 2012 - 7:09:35 AM - Ranjth | Read The Tip |
|
From the post I understand that groupings has been done based on "Supplier Region". I was looking for similar thing with slight modification. I would depict my requirement using the same above example.
At first go the report should look like this
Supplier Region Quantity Available ------------------------------------------- +North East 58308 +North West 603455 +South East 40000
Drilling on "North East", data should look like this
Supplier Region Product Name Quantity Available ----------------------------------------------------------- -North East +Apple 53758 +Pear 4550 +North West 603455 +South East 40000
Drilling on "Apple", data should look like this
Supplier Region Product Name Product Color Quantity Available ------------------------------------------------------------------------------------------- -North East -Apple +Red 500 +Green 53258 +Pear 4550 +North West 603455 +South East 40000
Drilling on "Green", data should look like this
Supplier Region Product Name Product Color Supplier Name Quantity Available ----------------------------------------------------------------------------------------------------------------------------------------- -North East -Apple +Red 500 -Green Abundant 1000 Green Apple 52258 +Pear 4550 +North West 603455 +South East 40000
Please do let me know if above is achievable using SSRS 2008.
Thanks |
|
| Thursday, October 18, 2012 - 8:03:02 AM - Dhamodaran S | Read The Tip |
|
Format looks very bad while Exporting drill down reports to Microsoft excel. Is there any other way ? or option to export this kind of reports? |
|
| Friday, November 16, 2012 - 4:44:47 PM - Sasi | Read The Tip |
|
I am using sql server 2008 R2 and am new to reporting. I am able to create a report as mentioned and able to insert a group with expression as specified. Can someone point me how to merge all cells in the group header? |
|
| Thursday, December 20, 2012 - 11:19:41 AM - Edward | Read The Tip |
|
Hi Rob; I don't know if you are still monitoring these comments since there is nothing from you since March of this year. If you are then I am having a difficulty with an aspect of the report that you did not go into very deeply. I am working with SSRS 2008 R2. I am trying to merge cells in the group header. However, while I can merge cells for most of the data cells in the header, I can't do this with the cell containing the group defining fields. In addition, the first details row always shows up even when the group is collapsed. And a column shows up for the group defining field even when I delete it from the tablix form. If Rob is not monitoring this, could someone else enlighten me on how to fix these issues? |
|
| Thursday, December 20, 2012 - 3:29:18 PM - Rob Fisch | Read The Tip |
|
Hi Edward, I know what you mean. To get groups to appear more like SSRS 2005, here's what I do:
Poof, there you have it....then add (normal) columns, repostion cells and merge as desired.
Have a good holiday.
-Rob
|
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |