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. 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? Thanks, in advance, for your help. 

Answer 1

Hi JeniQ,

Based on your description, if seems that you specify the visibility by click the whole column which display “location”.

If the column header does not repeat on each page, you can just specify the textbox visibility: right-click the textbox which display the “location” values in the table and open the textbox properties dialog box, in the visibility page, specify the display optional with the expression.

However, when the column header repeats on each page, we cannot hide the column pager based on the expression. If you want to display header information on each page, you can try to insert a new row inside group which used to display the header information, and specify the visibility of the textbox in “location” with above expression.

If I have any misunderstanding, please let me know.

Regards,
Fanny Liu

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Fanny Liu
TechNet Community Support

Answer 2

Fanny Liu,

Thank you for your time and attention. I believe you are right; it is impossible to do what I was trying to do. I ended up creating two different tables, one with the columns I want and one without the columns, and I filter the data in each table to include the supported records. 

Thank you. 

Jeni Q