I have a report with a variable number of columns. I can hide columns with the
ismissing function based on an expression like
Iif(Fields!Site.ismissing, true, false). However, the hidden columns still show when it's exported to Excel and the main purpose of this report is to be mailed out monthly via SharePoint.
My thought was to create a multivalue column whose value depends on a switch statement:
=Switch( Fields!State.IsMissing = true, Fields!Region.Value , Fields!Site.IsMissing = true, Fields!State.Value , Fields!Site.IsMissing = False, Fields!Site.Value)
The idea is:
- if the state is missing show the region field
- if the site is missing show the state field
- if the site is present show the site field
It works if I run the report at the maximum drill down, which is to say if I run it for a specific state the sites in the state show. But, if I go up levels in the hierarchy I get
#error returned. Any help would be appreciated.