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

1. Define report parameter with member correspondent to groups in Tablix
2. Define report variables connected to the parameter and returning true or false for visibility of each groups
3. Set group visibility property based on expression
4. 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.

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.

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.

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.

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

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.