By: Rob Fisch | Comments (29) | Related: > Reporting Services Development
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.
About the author
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
