Custom Visibility Toggling in SSRS
I can see clearly now
The visibility property of Report items can be a little gem. You can set the initial visibility of an item to hidden/visible , use another report item (a parent group in a table or matrix) and set the initial appearance of the toggle icon (+/-) either absolutely or using expressions. By using expressions, you can dynamically determine whether or not the item should be hidden (or collapsed in the case of a table/matrix grouping), and subsequently set the initial appearance of the icon.
In the example below, textbox8 is my toggle item for the group “Priority“. When the report loads I want to show the “critical” items in an expanded state, and all other priorities in a collapsed state. By right-clicking ontextbox8 and choosing properties I get the window below. Select Expression for the Initial appearance of the icon and enter a formula that will determine whether the initial appearance of the icon is expanded (’true‘) or collapsed (’false‘). What you use in your expression to evaluate whether or not the toggle item is expanded or collapsed is up to you.
Note. The above assumes that the Group “Priority” has already had it’s visibility set using an appropriate expression to show the “Critical” items expanded and all other collapsed when the report is run. It also assumes that the visibility of the group “Priority” is toggled by another item (textbox8).
Fig 1. Text Box Property dialog, showing the Visibility Tab
The result is shown in Fig 2.
- Fig 2. Result of setting custom visibility for a table group
[...] this is going to confuse your users, and needs sorting. Well, thanks to a tip from http://www.lukehayler.com/2009/07/custom-visibility-toggling-in-ssrs its [...]
Hi Luke,
I would like to have a color change after clicking on a hyperlink in the master report, so you know that you have already clicked that item to see the detail report.
any idea would be appriciated.
Thanks,
Mona
Hi Mona,
That’s a great question. I’m afraid that I don’t the answer off-hand. I expect that you could place some custom code (an OnClick Event?) to the report and then reference that code in the colour expression of your links. But I’d be guessing.
Your best bet is to log this question with the folks over at StackOverflow, if you haven’t already. It’s a great resource.
I’ll dig a little in the menatime and see what I can find out.
Luke
Thank you Luke.
Hi ,
I a m designing a ssrs 2005 report.
Problem is : I have a field in group header and if its vale =22 then I would display my report data else I should dispaly “Cannot be processed”
Please help me out
Mahesh
Hi Mahesh,
I’m afraid you’ll have to expand on what exactly you’re after. It is not clear if the group header is part of a table or a matrix object. Secondly, if the specified group header <>22 then do you want to display the object (table/matrix) with “Cannot be displayed” in the data cells, or do you want to hide the object completely?
If you can be more specific I am sure that I’ll be able to help you out.
-Luke
Hello,
thank you very much for your explanation. I was a little bit confused, because I’ve not that field at the visibility tab for the specified textbox. But I found the solution.
Click the textbox where the toggle-item is shown and open the “Properties”-Box for the textbox.
There you find an entry called “InitialToggleState” which you can set to “true”,”false” or an expression. Thats it. *THUMBS UP*
Have fun.
Greetings from germany.
Thomas
Hi i got a requirement on this, can any one try this out
i am using a tablix(ssrs 2008) in which i am setting the toggling property (row visibility) for the second row based on a field in the first cell in the first row.
its all working good, but now i need to hide the “+” sign when there are no records to be shown. is this possible
Hi Akshar,
I don’t think you can hide the ‘+’ sign outright, but you can change its state (‘+’ or ‘-’).
Thanks for the write up. Question, I’m using SSRS 2008 and want to defaul my matrix to open or (Expanded) upon initial startup. Any thoughts on how to do this in 2008
Hi Justin,
Set the Visibility>Hidden property of the Cell/group to False. You’ll need to change the ToggleItem’s InitialAppearance property to Expanded though, otherwise the view won’t match the toggle (‘+’ / ‘-’) state. Note – the group properties can be accessed using the tab at the bottom of the design screen (SSRS 2008).
I’m busy preparing another article on Visibility settings in SSRS 2008, so check back Monday or follow @lukehayler for blog updates.
Justin,
If you were still looking;
http://www.lukehayler.com/2010/02/custom-visibility-toggling-ssrs-2008/
[...] 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 [...]
Hi Luke,
I have 2 list control and each list I put a table control inside it . How can I hide and show the list and it’s table base onthe value of a parameter?
This is the expression I put in the “show or hide based on expressionion” under visiblity property of both the first list and the table inside it:
=IIf (Parameters!Segment.Value = 1, “True”,”False”)
Here the Segment is a parameter with 1 and 2 values..
When I execute the SSRS 2008, the error I got is:
“An Error Occured during local report processing. The Hidden expressopn used in the tablix ‘Tablix 6′ return a data type that is not valid”
“Tablix 6″ here is the name of my first list control.
This report works fine when I didn’t apply the expression in the show/hide expression. Did I missed something?
Your help will be appreciated.
Thanks, Ness
Hi ness,
True and false are key words, so there’s no need to enclose them in quotes.
=IIF(parameters!myparam.value=1, True, False)
Should work.