Expand – Reporting Services

In one of previous post I wrote about expand, collapse groups and how useful feature for Tablix component it is. User can expand data of each individual group member to its detail. But in case user would like to expand all group members, the solution becomes inconvenient for fast work with reporting. User must go and click on each member of group to expand data. In case that child groups are set to have the same toggle functionality, collapsed by default, it could become a nightmare for user to work with such a report. Fortunately, you can use parameters to handle Expand/Collapse state of groups. In this post I will show you approach I use in my projects.

Steps in short:

Define report parameter with member correspondent to groups in Tablix
Define report variables connected to the parameter and returning true or false for visibility of each groups
Set group visibility property based on expression
Set toggle item default state based on expression

Create report parameter first. Let’s name it Level, as group level user would like to expand. Go to Parameters Pane, right click above Parameters item and click Add new parameter. In Report Parameter Properties dialog set name and Integer as Parameter Type in general tab. Then go to Available Values tab and set Specify values checkbox – type values user can select. I set values that mean group levels – for our report: Region, Province, Name with values from 0 to 2. The Integer values mean level of group hierarchy. You will see their usage later in this post.

As default values type 0 to choose Region group level by default. It means that our report will be Collapsed by default.

Picture 3 – Report Parameters, Specify values

Right click in Report Builder design surface near to the Tablix component and select Report Properties from popup. Then go to Variables tab and Add 3 variables corresponding to parameter values I set in previous step: Region, Province, Name. I would prefer to use variables that it can be easily manageable on one place, but solution could be implemented directly through expressions. Furthermore, solution with variables could help performance of the report rendering too (see in next posts). To Value items set to them expression which returns true or false value, based on level returned from selected parameter Level. This means when user selects parameter value, these variables calculate visibility for correspondent group.

Here are expressions you put to variable values:

Region

=IIF(Parameters!Level.Value<=0,true,false)

Province

=IIF(Parameters!Level.Value<=1,true,false)

Name

=IIF(Parameters!Level.Value<=2,true,false)

On picture bellow there are defined each variables.

To define expression for each variable, click on expression icon on the right side of input box for Variable and define expression.

Picture 6 – Report Properies, Variables, Expression

Now we set visibility for each group. Because EnglishCountryRegionName name group is default group, it will be visible when user runs report by default. We set Visibility setting for this group to Show. Go to the next group, StateProvinceName group, click to its group area, and set Region variable value to the group visibility property. You can get it by two ways:

Right click on the group StateProvinceName name in the Grouping pane, Visibility section and Show or hide based on expression.

Or you can do it directly in the Properties Pane. When you click on the group in Grouping pane, Hidden property on Properties Pane and set expression value Variables!Region.Value.

Picture 7 – Group Properties, Visibility

I set Region variable value to Show or hide based on expression of StateProvinceName group.

Let’s go to the Level parameter and choose the Region value. On the picture bellow you can see that all groups that are under the Region group in the hierarchy, are expanded. Now you can do the same steps for LastName group.

But there is one thing which should be resolved. Look at picture bellow at Expand/Collapse mark which is set to +. But the state of the group is expanded. Follow next steps to fix it.

On the picture bellow there is highlighted to which toggle items put bellow mentioned expressions in the Tablix. Each toggle item has its own InitialToggleState since it is standard report item.

Picture 10 – Toggle Items in Report Tablix

For the StateProvinceName group, there was set EnglishCountryRegionName Textbox as toggle item. (it was described in post Expand collapse groups) Click on EnglishCountryRegionName textbox, Go to Properties Pane, Visibility section and set to InitialToggleState expression =CBool(1+CInt(Variables!Region.Value)).

Picture 11 – InitialToggleState, Expression

After confirmation you should see the same in Properties Pane of EnglishCountryRegionName textbox, as on picture bellow.

Now you should see fixed Expand/Collapse mark on picture bellow.

Do the same steps for LastName group on StateProvinceName textbox with following expression =CBool(1+CInt(Variables!Province.Value)). On LastName textbox we do not need to set expression, since it is not toggle item of any groups.

Let’s try report now, change Level parameter values. See pictures bellow how report layout changes.

Expand, collapse functionality is nice feature of Reporting Services. In this post we use this functionality in connection with Tablix component. Be aware that more SSRS components support this functionality and it is good approach how to make reports and dashboards more user-friendly. It’s also a way how to make top to bottom analysis, since user can get data from totals to details by few clicks.

For practice you can use any of your report where you have Tablix or Matrix component with few groups. Or you go to one of my previous posts where you can download sample grouped report.

Lets have report or similar report as on picture bellow. So we have groups and detail data and our task is to prepare user view per Region Name by default with posibility to go to data on lower group level. In short we have to do 2 steps:

Create toggle item in Tablix, which allow user to do Expand/Collapse action
Hide groups at lower level of group hierarchy to see only Region Name when runing report by default

Lets have report or similar report as on picture bellow.

To set toggle item, click on group from which you would like to Expand/Collapse data. As you see on picture bellow, we would like to toggle EnglishCountryRegionName, so we have to go to StateProvinceName group property and there set toggle item. It is important that toggle item is textbox outside of the group we would like to Expand/Collapse (otherwise SSRS displays an error when rendering).

Right click on StateProvinceName group in Grouping pane and Properties from popup menu.

Go to Visibility tab in Group Properties dialog and here we check Display can be toggled by this report item.

On picture bellow there are shown possible options you can set when managing Expand/Collapse functionality.

For Expand/Collapse lets define toggle item. From select box lists EnlishCountryRegionName, it is text box we will use for Expand/Collapse functionality.
Show – if we would like to have group expanded by default let’s leave it checked
Hide – hides group by default.
Show or hide based on an expression – I will explain in more detail in next posts.

Group Properties - Group visibility — Picture 5 Group Properties – Group visibility

For our scenario, Group Property dialog should look as on picture bellow. Check Hide, check Display can be toggled by this report item and select Report Item.

Group visibility and toggle item can be set also through Properties

Pane as shown on picture below. Click on Grouping Pane on StateProvinceName group. In Properties Pane set properties in Visibility section, Hidden and ToggleItem.

TIP: toggle item sign has “+” character by default. If you would like to change it go to your report item, which you set as toggle. In our case it is textbox EnglishCountryRegionName in the Tablix. In Properties

Pane you can change the default initial state sign trough InitialToggleState property.

Finally, you should get your Tablix design looks like on the report bellow. Data are collapsed at Region name level. User can go the detail by expand toggle item situated on the left side of the report.

After clicking on Australia item you expand all data hidden under Australia region group.

In next post we extend our solution by parametrized Collapse/Expand group.

Tag: Expand

Parametrized group Expand/Collapse Tablix Report

Expand collapse groups