The functionality to collapse and expand groups of data, known as drill down within SQL Server Reporting Services, is very useful. This post discusses a few handy things to know when working with Visibility settings. It assumes the reader has a basic familiarity with visibility and drill down concepts.
What is Visibility?
The visibility settings within SSRS are one way of making a report interactive. It allows the report developer to set which data is shown and which data is hidden when the report is initially run. The toggle settings then allow the end user to click the +/- sign to drill down to further detail within the same report. Visibility can be used to nest several groups of drill down data. Here's a very simple example with just one parent group (Area Name) and one child group (School Name):
The + sign: referred to as collapsed.
The - sign: referred to as expanded.
Where to Set Visibility
It can be easy to get the placement a bit backwards, especially if it’s a complex report you’re not terribly familiar with. Here’s how I think about it:
- Visibility: Set on the group/row/column/textbox which will be shown or hidden. I think of this as “inside” the area to be shown/hidden. In the screen shot shown above, the visibility is set on the School Name detail group.
- Toggle: Set on the textbox which will have the +/- displayed. I think of this as “outside” the area to be shown/hidden (in fact, it must be outside or you’ll get a recursive error). In the screen shot shown above, the toggle is set within the School Name detail group, and points to a textbox called Area name which is part of the Area Name parent group.
Most often you will want to set visibility within Group Properties – usually on Row Groups but Column Groups work very well too, especially if the columns are time-oriented.
If your group has just one row or column (ex: just a detail row without a group header or group footer), using the Row Visibility will get the same result as using Group Properties. However, I typically use the Group Properties which will ensure that the entire group will show and hide together. You can also set visibility on individual textboxes if that makes sense for your report (which isn’t very often).
Initial Toggle State
If you select the Hide radio button for “When the report is initially run” then the +/- symbols will appear as you expect them to. However, if you choose Show, or an expression which will make some or all groups show, then there’s an additional step to take in order for the +/- symbol to appear properly. Here’s an example of where we set it to initially Show the data rows, which it is doing properly, but the problem is we really want to see a - sign instead of a + sign:
To get the + signs shown above to display as - signs instead, the Initial Toggle State property must be set. This is done on the textbox properties for the toggle report item being used. In this example that’d be the Area Name textbox.
You want to change the default InitialToggleState property from False to True.
- True: will show the image initially as expanded (- symbol)
- False: shows it initially as collapsed (+ symbol)
Note: the Initial Toggle State is not available in the Property Pages (i.e., the right-click menus); it is only available in the Property Pane.
Expanding the First Group Shown
Recently our team delivered a dashboard where, within one report viewer web part, the customer wanted to see the first group of data expanded, but the rest of the groups collapsed. This allowed us to show the user something without requiring an initial click, yet saved some space which is particularly a premium on a dashboard. I handled this in three steps:
1. First, in the stored procedure which serves up the dataset, I added a Rank() function. The only rank we are concerned with is the #1 value, grouped by the same set of data we’ll be showing or hiding in the report. This derived field must be available to the dataset in SSRS, so we can use it in step 2.
RANK() OVER (ORDER BY A.DisplayName) AS 'ClassGrouping'
2. Back to the report. In the Visibility settings, use an expression which utilizes our rank field to know whether it should conditionally show or hide when the report is initially run.
IIF(Fields!ClassGrouping.Value = 1, false, true)
- False = Do not hide when initially run. Will apply to our rank 1 group of data only.
- True = Hide when initially run. Will apply to all ranks except for 1.
3. Set the Initial Toggle State for the textbox which serves as the toggle. The tricky part here: notice how the IIF statement here in step 3 is the opposite of the one used in step 2 for the visibility settings.
IIF(Fields!ClassGrouping.Value = 1, true, false)
- True: Will show the image initially as expanded (- symbol). Will apply to all ranks except for 1.
- False: Shows it initially as collapsed (+ symbol). Will apply to our rank 1 group of data only.
Just for grins, one more example of the conditional Initial Toggle State:
Conditionally Expanding or Collapsing Groups
Several ideas are conditionally controlling visibility, such as on row counts, are listed in this MSDN article: Expression Examples.
Toggle Image
The + /- is the only symbol supported by SSRS for visibility functionality. It would be fantastic to be able to have additional choices (for example, a chevron which is popular these days for show/hide functionality) in a future version of SQL Server.
Toggle Report Item
The only report item that can be used as a toggle is a textbox. Using an image, or another type of item from the toolbox isn’t supported.
Expanding All Groups With One Click
Expanding more than one group in a single click isn’t supported, but there’s a workaround which may be helpful, depending on how your reports are delivered. I discussed this workaround in a previous entry called “Expand or Collapse All Grouped Items on SSRS Report.”
Naming Conventions
Can’t resist the urge to leave with one last thought … it’s a lot more self-documenting to set the toggle on a textbox named “AreaName” or “txt_AreaName” or whatever – anything that isn’t along the lines of textbox1, textbox2, etc. SQL Server 2008 R2 has taken one more step forward in helping to name textboxes and groups well, but I still like to keep an eye on them especially if they’re referred to by another report item.
Finding More Information
MSDN: Hiding Report Items Conditionally
MSDN: How to Hide an Item