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!

score 10 · Answer 1 · edited Jan 06 '16 at 19:33

10

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.

edited Jan 06 '16 at 19:33

Jonnus

2,988
2
24
33

answered Aug 22 '14 at 15:29

Mikuana

584
5
12

This is an awesome answer. Thanks a ton bud. – MindLoggedOut May 29 '19 at 16:19

score 9 · Answer 2 · edited Jan 06 '16 at 19:32

9

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)

edited Jan 06 '16 at 19:32

Jonnus

2,988
2
24
33

answered Sep 11 '13 at 21:00

Ulysses

407
5
5

score 3 · Answer 3 · edited Jan 06 '16 at 19:31

3

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)

edited Jan 06 '16 at 19:31

Jonnus

2,988
2
24
33

answered Jan 06 '16 at 11:37

snehal

31
1

1

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. – esp Sep 22 '16 at 06:37

score 1 · Accepted Answer · edited May 23 '17 at 12:34

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

edited May 23 '17 at 12:34

Community

1
1

answered May 11 '12 at 20:24

Chris Moutray

18,029
7
45
66

score 1 · Answer 5 · edited Jan 06 '16 at 19:32

1

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

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

edited Jan 06 '16 at 19:32

Jonnus

2,988
2
24
33

answered May 14 '12 at 20:02

Tihomir Budic

309
4
13

score 0 · Answer 6 · edited May 23 '17 at 11:54

0

You can explore column visibilty property of a tablix based on expression: If its nothing then evaluate the condition to just Nothing.

Follow the link:

SSRS 2008 Column Visibility Expression Evaluates to true, column not visible

edited May 23 '17 at 11:54

Community

1
1

answered May 14 '12 at 12:31

Pratik

1,472
7
20
36

Hide Column if all Rows are empty

6 Answers6