SSRS expression based on visibility of a textbox or column

Question

Is it possible to inspect the visibility (Collapsed/Expanded) of a textbox in SSRS and use this in an expression?

For example :

=iif(Fields!column1 Is Visible,"Use this label","Use this other label")

What I want to accomplish to have the initial value of a column header display some text, when user clicks a particular value the details row are displayed (this is a Tablix), and then the header would change to something else (E.g. initial report state is to hide detail rows and only show Tablix member totals so column displays "... total", when user expands to view details column header changes to "... number".

In MS Access through VBA I could inspect the visible property and create code based on this. Till now haven't found a similar way of doing this in SSRS 2016.

Tried setting expressions for visibility as well as setting expressions for changing value of a textbox.

=IIF(Fields!accountnum.Value IS NOTHING OR Fields!accountnum.Value = TRUE,"Station #", "Station Count")

No error messages. My results should be to change header (textbox) value based on whether detail rows are collapsed (hidden) or expanded (visible).

Answer 1

To answer your first question:

Well of course this is possible, just not they way you want to do it. Here is a smimple example. Lets say your visibilty criteria is the following expression:

'Visibility expression
=IIF(Fields!Category.Value = "Visible", False, True)

Now you dont check on the visibilty with the Is Visible criteria, you just use the same expression again. Like this:

=IIF(
     IIF(Fields!Category.Value = "Visible", 
         Fale, 
         True) = True, 
 "This one is not visible", 
 "This one is visible")

To answer you problem:

They way you want to do it, it is not possible. But you can do this with a workaround. Add a user defined parameter to your report with two options:

• Display details
• Hide details

Then add two tablixes to your report. One tablix without the details and one tablix with the details you want to show. Then go to the first tablix Tablix Properties > Visibility and add the following expression:

=IIF(Parameters!VisibilityParameter.Value = "Display details", True, False)

And the expression for the other tablix visibility:

=IIF(Parameters!VisibilityParameter.Value = "Hide details", True, False)  

Now based on the parameter result the talbix with or without the details will be displayed.

Answer 2

You can do this, maybe it won't look too elegant but I'll show you what I did and you can see if it's useful.

Note: Using this method, you can't change the actual column header directly as it's out of scope so this may not meet requirements but you may be able to adapt it. I just thought it was a nice challenge ! :)

First I got some sample data (WideWorldImports sample database) that just gave me a CountryID, CountryName and Continent.

Once you have your data, add a table, drag CountryName into the first column and remove the remaining two columns.

Then right-click the Details row group and do Add Group => Parent Group Choose Continent as the Group By field and check the Add Group Header option.

The table design should look like this, nice and simple.

Next we will set the toggle on the detail rows. Right-Click the "Details" row group and chose "Group Properties". On visibility, click 'Hide' and check the "Display Can be toggled by the report item" to Continent.

We should now have a report that expands and collapses as expected.

Next vertically expand the 'blank cell' just above the countryname as we'll need a bit of room to work with. Make it about 4 times the normal height, we'll change it back later so the size is not important.

In the blank cell, right-click and do Insert => Rectangle. In this rectangle, right-click and do Insert => TextBox. Double click the text box and type your "collapsed state" column header caption. In my example I just used "Collapsed Header". Repeat and add another textbox (It must be within the rectangle again). Set this text box to your "expanded state" caption. In this example I just used "Expanded Header".

Next, right click the "Collapsed header" textbox, then properties then set the visibility to Show and the toggle item to Continent.

Repeat for your "Expanded Header" but this time set visibilty to Hide, like this..

Finally, position both text boxes at location 0,0 so they overlap and shrink the cell back down to it's original size.

The final design will look something like this...

When we first run the report we see this.

If we expand a node, we get this.

Answer 3

I do not think this is possible in SSRS. You could change column header text depending on your condition, For Ex: IF (A) then Header A else Header B. But you wish to change Column Header based on some clicks (expand / collapse) on report and this is not possible. I mean we need some data to set condition not clicks.

But maybe someone with more Experience on SSRS would throw some more insight.

Answer 4

This is what InScope() is for. See here for more information