Drill Down Techniques in SQL Server Reporting Services with the Visibility Property

Drill Down Techniques in SQL Server Reporting Services with the Visibility Property

By:Rob Fisch-- 9/13/2007

Rating: |Comments (3)|Print| / / Share / Tweet

We're giving away another Kindle loaded with SQL Server eBooks

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 usethis scriptto 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:

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 theDetails Groupingproperties. (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 selectEdit Group(even though it's a details line), or 2) select the entire table, right-click, selectProperties, then theGroupstab, thenDetails Grouping. Both these methods bring you to the same place.
Then click on theVisibilitytab. ClickHidden, check theVisibility can be toggled...checkbox and select theSupplierRegion_1textbox.

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 yourVisibilitytab (of theDetails Grouping), change theInitial VisibilitytoVisible.
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 theSupplierRegion_1(group header, not the group details anymore) textbox properties. Select theVisibilitytab, then change theInitial appearance of the toggle...toExpanded (-).

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.

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 theVisibilitytab of theGroup Detailssection, underInitial Visibilityinstead of selecting "Visible" or "Hidden", selectExpressionand 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 theSupplierRegion_1(group header, not the group details anymore) textbox properties. Select theVisibilitytab, then change theInitial appearance of the toggle...toExpressionand enter the following:
=IIF(CountRows("table1_Group1")<=4,True,False)

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 otherReporting Services tips.