Hide multiple rows or columns in SSRS report with same Expression at same time

Question

I have a report containing a Tablix/table with sets of rows and columns which I am hiding conditionally using Expressions, via the Column/Row Visibility dialog.

If for example I have 3 columns which I want to all have the same Expression to determine their visibility, is there any way for me to achieve this without opening the Column Visibility dialog for each Column and entering the appropriate Expression?

I've tried selecting multiple columns, but then the right-click context menu no longer offers the Column Visibility option (it's greyed out) and I have also tried out the Hidden property of the columns (which can be amended in bulk by selecting multiple columns), but this only stops the columns from being rendered, leaving a blank space where they used to be (rather than how Column Visibility works, where columns to the right of the hidden columns are moved across to fill the gap, like hiding columns in an Excel sheet).

Is it possible to achieve what I'm after, or will I have to continue opening the Column/Row Visibility dialog for each column/row I wish to conditionally hide/show?

Answer 1

If the columns you want to control the visibility for are next to each other, you can create a Column Group for them and manage the visibility via the Group.

• Add a new column group at the appropriate level so that only the relevant columns will be included.
  • If you already have Column Groups this will likely be a Child Group or an Adjacent Group.
  • If not, you can create an initial Column Group by dragging a Dataset field from the Report Data into the Groups pane.
• Insert new columns inside the new group, and move the data from your existing relevant columns into the group.
• The new group does not need to repeat, so in it's properties window set Group on: to a plain text value, e.g. "1".
• Input the desired visibility settings into the Visibility page of the properties window.
• For each of the individual columns inside the group set the Hidden property to False, to remove any other expressions or settings that might interfere.

Answer 2

Not exactly what you are looking for but perhaps a midway point - and this can apply to many things other than visibility (e.g. background colour, tooltip) - edit the RDL directly using View Code. This has the added benefit of being able to implement changes that are almost identical per column instead of identical with a minimum of mouse usage.

Typically I have set one column up the way I like it in the designer and then copy/paste under the other columns. In the case of visibility look for <TablixColumnHierarchy><TablixMemebers>. You will see a list of empty <TablixMember /> items along with the expanded <TablixMember><Visibility><Hidden>=... block where you have set up one column. Simply copy paste that block over the empty items at the position of interest (you will need to count it out unfortunately as there are no identifiers).

Bonus Tip: If you make a mistake or want to change something later, you can do a replace on all expressions at once (optional regex allowed).

Remember to backup or check in your work first because the designer may not open that report again correctly if you make a mistake :-)

Answer 3

Select the columns you want to conditionally hide and press F4 to see the Properties Window. Look for the Visibility node and use the Hidden property to set the conditionally expression.

The expression should evaluate to True for hiding and False for showing.

Let me know if this helps.