Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Drill Down Techniques in SSRS with the Visibility Property

MSSQLTips author Rob Fisch By:   |   Read Comments (26)   |   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:

SELECT ProductCode, ProductName, ProductColor, QuantityAvailable, SupplierName, SupplierRegion
FROM VISIBILITY_EXAMPLE_TABLE

Setup a standard table. When you are done it should look something like this:

setup a standard table in ssrs

The preview will look like:

the report preview will look like this

Next add a grouping for Region.

add a grouping to the report in ssrs

Select the Supplier Region field from the dropdown.

write ssrs expression for grouping

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:

ssrs output with grouping

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).

setup ssrs drilldown in report

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.

toggle visibility setting for grouping and sorting in ssrs

When you are done your preview should look like this:

report will look like this with collapsed rows

Expand some of the times:

expanded rows in ssrs report

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 (-).

expand collapse expression in ssrs

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.

=CountRows("table1_Group1")

This will display the number of rows for each group.

Conditional Initial Visibility in ssrs report

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).

=IIF(CountRows("table1_Group1")<=4,False,True)

*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:

=IIF(CountRows("table1_Group1")<=4,True,False)

expand and collapse grouping in report

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
MSSQLTips author Rob Fisch
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
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,
Ravi kiran.


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
-------------------------------------------
+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.

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:

 

  1. Add Group as Parent
  2. Select the entire row, right click, and Insert Row above
  3. Literally copy and paste the new group from the left side to the row directly over the details row (you can reposition and play with adding columns and merging cells later)
  4. then select the entire new (group) column and delete it...right click (Delete Columns).
 
Poof, there you have it....then add (normal) columns, repostion cells and merge as desired.
Have a good holiday.
 
 
-Rob

Sunday, June 02, 2013 - 7:57:34 PM - Ned Bakelman Read The Tip

 

This is awesome. Thank you very much.

 

Ned


Monday, August 12, 2013 - 8:42:09 AM - Phyllis Collins Read The Tip

Thanks for the wonderful explaination of expand and collapse. I have read many things on it, but this is the first time I actually understood how to do it.

My Question is: What would I need to do to be able to sort the total numbers on the right of the header rows. I would also like to know if there is a way to have a secondary sort. I am currently using version 2005.

Thanks, looking forward to more tips from Rob..


Monday, August 12, 2013 - 12:42:13 PM - Rob Read The Tip

 

Hi Phyllis

What you want to do is add totals into the header row over your value column and then sort by the =sum(field) in the group 'properties'  

Hope that helps 

Rob


Tuesday, August 13, 2013 - 11:58:09 AM - Bucky Read The Tip

Hi,

Do you know how I can have a toggle item show/hide two or more items in SSRS 2008?  I have a report with three tablix items and the user wants to see one while hiding the other two.


Wednesday, August 14, 2013 - 8:53:43 AM - Phyllis Collins Read The Tip

Someone has written a report and it makes a new page for each branch. I know where I would usually go into layout and select new page before or after, but it does not seem to be checked. Are there any other places that could be used to set it to page between? I have been asked to edit the report and take out the page breaks so each branch runs right after the other and not broken out by page. What am I missing?

Thanks for your help


Monday, September 23, 2013 - 3:23:51 PM - Steve Read The Tip

Rob,

   While you're at it, do you know where I left my car keys?

   (sorry, couldn't resist - I always get a kick out of the random barely-related questions on these public forums)

 

   Seriously, good article - nicely concise.


Thursday, November 14, 2013 - 10:08:32 PM - puanani Read The Tip

HI,

I am using SQL 2008 and here is my results after steps 1 and 2.  The following is my output.  I cannot get the #3 item to work.  Can you tell me where the code to fix the +/-?


Wednesday, June 11, 2014 - 10:32:16 AM - sreehari Read The Tip

Hi,

 

How do i hide and unhide the column based on row toggle... 

My report looks something like this

 

Category       Product        OrderId     Amount

Bevrages       Cooldrinks    ORD123    120

 

So if i group on Category and display the Sum of amount, on hide of row product and OrderID will be blank. So in that case when it is + i want ti display Category and Amount and when it is - i want to display all columns

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.