Hide a column conditionally based on the values of a group that display on a single tab sheet

Question

This question is about how to hide a column conditionally based on the values of a group that display on a single tab sheet. It is a follow on to this question, but it is really independent of. I am using SSRS 2008 R2.

My dataset is powered by a stored procedure and returns event-related data.

EventID | Event Owner | Event Type | Location
E001    | Bob         | Meeting    | Conf Rm A
E002    | Jane        | eTraining  | 
E003    | Bob         | Training   | Conf Rm B
E004    | Phil        | eTraining  | 
E005    | Jane        | Meeting    | Conf Rm B
E006    | Phil        | Meeting    | Conf Rm A
E007    | Jane        | eTraining  |

In my report I am using a table, not a matrix. I've got an innermost Detail row with details about the event, including the EventID and Location as columns. There are several attributes by which I group the data. The detail row and header row have a single row group assigned which groups by Event Owner and Event Type (GrpbyOwnerandType). There is a page break between each instance of the group, which results in the results appearing on separate pages on the report server and on separate tabs when downloaded to Excel (the ultimate goal). Therefore, the output in Excel is like this:

Tab 1 has E001 (Bob, Meeting) 
Tab 2 has E002 & E007 (Jane, eTraining)
Tab 3 has E003 (Bob, Training)
Tab 4 has E004 (Phil, eTraining)
Tab 5 has E005 (Jane, Meeting)
Tab 6 has E006 (Phil, Meeting)

On Tabs 2 and 4 I would like to hide the Location column because I feel silly displaying a completely blank column that is meaningless given then Event Type. I try to do this by setting column visibility to

=IIF(CountDistinct(Fields!EventLocation.Value, "GrpbyOwnerandType")= 0, True, False)

You can see that I'm specifying the scope here to just the group (GrpbyOwnerandType). If I do not specify the scope, then the test will clearly fail because there is at least one row in the dataset where the EventLocation is not NULL. However, when I specify the group in the scope of the IIF condition, the report preview fails with this error:

The Hidden expression for the tablix 'X' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

I used this same expression on the hidden attribute of the column and in that case, the report does not error out, but I'm left with an ugly white column on Tab 2 and Tab 4 because there is no data.

So, is there any way for me to hide the Location column on some but not all of the sheets?

Answer 1

If you tweak your expression to:

= IIF(CountDistinct(Fields!EventLocation.Value)= 0, True, False)

... then the scope will be automatically evaluated and should give you what you are after.