Control Column Visibility using DataSet values

Question

My client wants to be able to show/hide columns in a report based on a Parameter (ReportType). The report columns are static, they will just be controlling which ones are hidden/shown which creates a different "View" of it. Currently there are only 2 views, but we are about to add several more.

At the moment column visibility is controlled by simple expression: =Parameters!ReportType.Value = "SOMEVALUE"

and only 1 report type hides columns so this expression is fine.

Now we're moving into a situation where a column may be hidden in multiple ReportTypes and i want to avoid getting into: IF report type = VAL1 or VAL2 or VAL3 THEN HIDE as it will make it very hard to see for any given ReportType what columns are meant to be shown (as all the logic is in each column visibility expression)

I found a post and used the basic elements of it: http://sql-bi-dev.blogspot.co.uk/2010/10/displaying-dynamic-columns-in-ssrs.html

What im trying to do is define a Dataset, something like:

SELECT * FROM
(
select 'Rpt Type1' ReportType, 'Units' ColumnName UNION
select 'Rpt Type1' ReportType, 'Price' ColumnName UNION
select 'Rpt Type2' ReportType, 'Units' ColumnName
) ReportColumns
WHERE ReportType = @ReportType

And then in the Column Visibility expression check to see if the column name exists in the dataset. This way visible columns for a report type are defined in a single place and are easy to manage/maintain.

In the post i linked, hes getting the user to select the Column names in a parameter. I want to lookup the Names in my dataset based off the ReportType param thats selected.

Im stuck on getting the column names into a parameter and then using that in the column visibility expression. Any help would be much apreciated :)

Tom Tom · Accepted Answer · 2012-10-12T16:43:09

Doh, i was pretty much there.

I created a parameter and set its default values to "Get From Query" and selected my dataset and ColumnName field... job done!

Then i was just writing visibility expressions for each column using the code in the article i was following.

Control Column Visibility using DataSet values

1 Answers