Hide Column if all Rows are empty

Question

I have a a Tablix in SSRS 2008 and I have a Column that sometimes has data and sometimes doesn't. I want to hide the column if NO rows have data.

So this would hide Column 2:

Column 1     Column 2     Column 3     Column 4
1                            3            4
2                            3            4

This would NOT hide Column 2:

Column 1     Column 2     Column 3     Column 4
1                            3            4
2               2            3            4

Is there a way to do this in SSRS 2008?

Thanks!

Solution 1

I suspect you'll have to get inventive. For example run a query to get a count of non empty rows for the column. Then use the count result as part of an expression on the visibility property of the column. I.e. If count greater than zero... show.

This might help

Reporting Services - hide table column based upon report parameter

Solution 2

Very old post, but I figured out a better solution for this when using an SSAS cube. Since all of the aggregation has already occurred within SSAS, you can simply check if the parent level of the hierarchy has a value.

This is done accordingly:

=IsNothing(Fields!Field.Value)

No summation or if statements necessary with SSRS. Because the function evaluates to true or false, and because the expression is evaluating whether or not to hide the column (i.e. True hides it) that is all you need in the formula.

Solution 3

If you have fields that contain values and not numbers then the following should work to hide columns that have only NULL values for each row in the column.

Place this code as an expression in the Column Visbility object for each column that you want to evaluate

=IIF(Count(Fields!<NAMEofCOLUMN>.Value) = Cint(0), True, False)

Solution 4

In design,

Go to the column, right click and select 'Column Visibility`

Select show or hide based on expression and give the expression as:

=iif(Fields!column_name.Value=Nothing,True,False)

Solution 5

Select all Columns in the Tablix and set Visibility - Hidden properties as:

=IIF(Fields!ColumnSample.Value = Nothing, True, False)

View more solutions

Comments

[esp]

I used Column Visibility with =IsNothing(Fields!Field.Value) from the post above, setting the Hidden property on the column left me with a blank space where the column would have been.

[MindLoggedOut]

This is an awesome answer. Thanks a ton bud.

[Just Fair]

Works like a charm! Thanx!