A while back I wrote a post about how to toggle the visibility of rows/columns in tables/matrices for SSRS 2005. In the comments of that article someone asked how this was done in SSRS 2008. The short answer was that the method is the same. However, setting all the properties can be confusing as their whereabouts is a little different. Hopefully this post will clear things up.
Let’s do this by way of example:
I created a project with a single report. The Data source is AdventureWorks2008 and we are looking at Total Sales figures by Year & Month (keep it simple right?). I have set up a matrix object and added the required fields and set subtotals for Month & Year. See Fig 1. below:
Objective: Enable Toggling of the Month group, using the Year Group as the Toggle Item
At this point I want to point out two key areas, as this is where we will be concentrating our efforts:
- The Row/Column Groups section at the bottom of the designer
- The Visibility Properties at the bottom right corner (Last item in the Properties list – but only when an object is selected)
Now, select the drop-down arrow for the Month Row group (See Fig 2.), choose Properties and then select the Visibility tab.
The objective is to enable toggling of the Month rows in the Matrix. We could choose to Show or Hide the Month rows when the report is initially run, or to make this decision based on an expression. Using an expression will determine which rows are expanded and which are collapsed when the report is run initially. I have chosen to use an expression:
=IIF(Fields!YEAR.value=2001, False, True)
This says that if the Year group value is ‘2001’ then show the Month Sales Total values (‘Expanded’), else Hide the Month Sales Total values (‘Collapsed’). See Fig 3. below:
Also note (in Fig 3. above) that I have set the YEAR cell as the Toggle Item. We’ll configure this next.
Once you have set the above properties go back to the designer and highlight the YEAR cell in order to view its properties. These should be on the bottom right, otherwise right-click the cell to bring up the properties window. Find the Visibility > InitialToggleState property and set it using the following Expression:
=IIF(Fields!YEAR.Value = 2001, True, False)
This says that if the Year group value is ‘2001’ then show the toggle icon as ‘Expanded’ (True), else show it as ‘Collapsed’ (False). This allows us to ensure that we are in sync with the expression we used to set the initial visibility of the Month rows. Fig 4. shows the end result of all this hard work:
The Visibility Properties
Understanding what each of the three visibility properties does will help to use them effectively.
Hidden – Can be set to either ‘True’ or ‘False’. This can be done by selecting True or False from the drop down, or by using an expression to set either of those values after evaluating a condition. True equates to the object being hidden and False to the object being shown (tricky eh?).
InitialToggleState – This is only applicable if the object that you have selected can toggle the visibility of other objects. It refers to the state of the toggle icon and can either be ‘Collapsed’ (‘+’) or ‘Expanded’ (‘-‘). As explained by way of example above, setting this to ‘’True’ sets the icon to the ‘Expanded’ state, and False to the ‘Collapsed’ state.
ToggleItem – Use this property to set the object that will enable the user to toggle the visibility of another item. In the example above, I have chosen YEAR as my toggle item for the Month Group.
Beware the Gremlins
You need to be aware of setting properties for the right objects or groups, or you may find that your report does not display correctly:
Fig 4. shows what happens when you set the Visibility properties for the Month & Data CELLS in the report instead of the Month Group properties. Setting the Month Group visibility properties means that you won’t get that nasty white space, and there’s no need to set the visibility of the cells (in this instance).
I have placed the example project in a nice little zip file for all you folks that might want such a thing.
How can I select more than one field in expression? If I want to disply 2002 year same as with 2001 (only two)
Thanks
Sorry correction “Value” not “Field” in above msg
Hi,
I hav gone through you example and ur code but not able understand how could you able to manage mutile selected parameters to show in the matrix.
Please Solve the query..
Hi Ramesh,
I’m not sure I understand the question? Could you perhaps give me some more detail?
Got it
Thanks
Ha! That’s what I get for not being able to respond to comments right away! (pesky day job….)
Glad you got it figured out.
haha..thanks Luke
Hi Luke,
My report can have multiple levels to it. eg
Parent
-Sub1
-Sub1Sub
-Sub2
-Sub2Sub
-Sub3
How can I toggle so just Parent and Sub1,2 & 3 are showing, not anything under it?
Regards
Ash
I should add to the above that I want to toggle to those sub groups – just have the groups collapsed to Parent and subs. I played around and tried to set the initial toggle state for the row on my group to =IIF(Level()>1,True,False) however that didn’t work
Hi Ash,
You’ll have to set the Visibility properties of each of your groups and subgroups according to the state that you want it to be in.
So, for Parent/Sub1/Sub2/Sub3 you should set the ‘When this report initially runs’ option to ‘Show’. For the Sub1Sub/Sub2Sub set this same option to ‘Hide’.
Be sure that the ‘ToggleItem’ is set correctly too. I.e. Parent will control the toggling of the Sub1/Sub2/Sub3 Level, Sub1 will control the toggling of Sub1Sub, etc.
Thanks for that Luke. I am using a hierarchy node id in the report, so there is only 1 group, that’s why I was hoping to use a level() command in there somewhere. The visibility works where I use =IIF(Level()>1,True,False) however I cannot drill down past Sub1,Sub2 etc.
Another question – when you collapse a level/s and go to print layout, all levels are expanded. Can we print when we collapse groups?
Thanks for the assistance.
I have resolved this. I was using a HirearchyNodeId – I upgrade SQL to R2 and it fixed it. Off topic, is there a way to resize columns based on longer values? I know it can be done on rows, however not columns?
Cheers
Thank you! You have helped me to get a rid of white spaces on the report. Thank you very much
Hi,
I have implemented the hierarchy in my report for three levels..but i want the levels to be displayed based on a parameter that i choose.I have given row visibilty for each level. Everything works fine except that the last level is not displayed. Does anyone know wat the problem is??????
Saranya,
Is this the problem in report or in your query? did you test the query? will you please explain more?
Luke,
Thanks for the follow up to my question. Still working through a few issues but am on the right track now.
Thanks again.
When this report initially runs’ option to ‘Show’.
means what? where do I set this property? please help
Hello,
I’m having issues getting my toggles to work correctly. The following example i’ve provided, I built in a matrix and I’m using SSRS 2008 R2.
I have a field in my dataset that assigns whether I want the data to show initially. So in the the Group visibility on the State group, i put in the following expression: =IIF(Fields!isDetail.Value=0,False, True) This is saying if the state is in Washington or New York show it else, hide the value.
Then in the Contry textbox property of the initially toggle state, I the following expressions: =IIF(Fields!isDetail.Value =0,True, False)
But the toggle isn’t working exactly how I want it to. When I expand the toggle , it only shows Denver.
What I really want it to do is when I expand the toggle, i want it to show all the states. Only when it is initially run the report, I only want it to show Washington and New York. Do you know what I’m missing to get this to work.
Collapsed View:
Country State Count
“+”USA Washington 5
New York 7
Expanded:
Country State Count
“-”USA Washington 5
Denver 4
New York
Thank you! It really helps me, especially for “Beware the Gremlins” part. Exactly what I need…
Nice post. My question is this. In your example, if you wanted to hide by default the Month column (The entire column as you are simply either displaying month or “Total”) and then to expand Month out when the user expands out year, how would that happen?
Thanks,
Nathan
Hi Nathan,
You can set the Column visibility just as you would for rows. At the bottom right of Fig 2. you’ll see a drop down for the Column group. Click on that and then on Group Properties, then follow the same steps as per the row group visibility.
Hi Luke,
In 2008 how can I display the toggling button in other format than + and -,
Thanks
Short answer: you can’t.