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
Pingback: Beakersoft Blog » Show/Hide Groups at run time
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 ‘-’).
Akshar,
If I’m understanding you correctly, this can be done with a filter on your group. I’m doing this with the following filter:
=Fields!YourGroup.Value+CountRows().ToString()
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/
Pingback: Visibility Toggling in SSRS 2008 | Luke Hayler - SQL Server developer
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.
How can I check the toggle state of a textbox in report? Is there a textbox property that I can point to? So far all I see is ReportItems!textbox1.Value.
‘S’,
I have had a hunt around for this very property some time ago – without much success.
Your best bet to use evaluate the condition that is being used to set the Toggle state of the text box and base your actions on that result. It’s a bit of a hack, but then most things are to get Reporting Services to work the way you want it.
Hi Luke, I have a matrix report and i want to implement toggle using hyperlinked text instead of the (+) plus sign that appears next to the text. Is that possible???
I couldnt find a direct solution to the above mentioned problem, I am wondering if there is a workaround to it?
Hi Saurabh,
As far as I am aware you cannot substitute the (+) sign for hyperlinked text – hyperlinked text already has a function (navigation). Am also not aware of a work-around to do what you are attempting. May I ask what it is that you are trying to achieve (and why)?
Luke
Hi Luke,
thanks for the reply.
I pretty much had an idea that this would not be possible, but the client wanted a hyperlinked toggle to make it more user friendly, as the report has 8 levels of drill down in the matrix and its inconvenient to click on the small + sign so many times. Thanks.
Saurabh
Hi Luke,
I am using this ‘trick’ of yours and it works well :)
Any idea about changing the position of plus sign if possible? I am creating a tree view style report but damn plus signs always are at start of the line. Can the be indented too? What am I missing?
Hi Sudarshan,
Glad it’s helping.
With regards to a tree view using the toggle, have a look at this article by sqlserverdatamining.com.
Hope that’s what you are looking for!
Hi Sudarshan
Have you tried to use a table with some more columns like the following example?
Header1|Header2|Header3|Header4|Header5|
+ | | | | |
|+ | | | |
| |+ | | |
| | |+ | |
I think with this little (dirty?) trick you should be able to create your tree view.
:)
Hey Luke,
Great article and info! I am looking into the MS Reports for a reporting solution I am creating in VS2010. Unfortunately for me, it looks like MS got rid of the toggle settings on the visibility tab of the text box properties. I wanted to be able to use a checkbox to set a “ViewAll” parameter and expand all the various report toggles with one button press so my clients will have the option of collapsing or viewing all data on the report.
I am going to keep looking into a possible parameter solution so I don’t have to create new reports to show expanded data for each of my current reports, and I will let you know if I figure something out.
Figured it out! They removed it from the textbox properties menu options and now you can select “Expression” in the InitialToggleState options of the text box in the Properties window. Should be a breeze now, I can use your example! Thanks again!
Hmm, think I found a glitch in the new reports. I used your example and created a boolean parameter in my report that looks for my checkbox checked value and then sets the InitialToggleState to true or false. The toggle changes to a minus as I would expect, but the report doesn’t expand as it should. I can then click the minus and it turns into a plus and the data expands like is should. Looks like I will be writing MSDN. Darn.
Hi AShkar,
There is a workaround for hiding the toggle sign for the empty groups: Don’t use the group cell as toggle item, but create a small column to the left of the group column in your tablix and use that as a toggle item.
If you bring in your dataset the number of group members, than you can set the visibility of the new toggle cell based on the number of group members. This will hide the “+” sign if the group is empty. It only works for white backgrounds, otherwise you’ll end up having some white spaces.
cheers,
andrei