Monday, January 20, 2014

Column Visibility Based on Group Value


I recently received a question on how to hide the column of a matrix based on the value of a group.  To illustrate this, I'll use a picture:

The First Attempt 

The logical approach for any developer is to create a table with the appropriate groupings like so: 

Then the developer thinks to go to the 'RAM' column and set its visibility like so:

The formula for hiding/showing the column is set to: =Iif(Fields!Category.Value = "Laptop",False,True). This should work, right? WRONG!!

My example shows the following when I run the report:

Notice how the table now hides the 'RAM' column no matter what the value for Category is.  The reason is because my first record in the data set happens to be a Television category, so SSRS pulls that first value, and evaluates the Show/Hide expression and determines to hide the whole column.  If my first record were a Laptop, then the 'RAM' column would permanently be shown.

The reason why SSRS behaves this way is because of the way the report is scoped.  In order to get the results we want, we have to place the Column we want to control within the scope of the Category.  As it stands now, the 'RAM' column of the table lies outside of the scope of the Category grouping.

The Second Attempt

To achieve the desired results, we need to scope the controls appropriately.  I'll demonstrate one way to do this.  The way I'll manage the scoping in this example is with a List Control.  I'll place a list control on the report, and then place my table inside of that List Control as follows:

The table inside of the list needs to be modified.  We need to scope the Category grouping above the table so we can control the columns of the table with the value of the Category group.  To do this, I remove the category grouping from the table and its associated labels.  I go to the List control and add the Category grouping to the List Control, the same way I had it in the table.  Then, I go inside of the list control and add a text box above the table to display the Category group.  When it is all done, the final design looks like this:

When I run my report, I see the magic!  Notice how the 'RAM' column is hidden in my Television group. 

The lesson to learn here is to remember that controls must be scoped properly if we want to manage them based on dynamic values.  Happy reporting!