CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2012ssrs-expression
Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

SSRS. How to hide blank/empty column via expression?

I need to hide column if all rows in column is empty (blank).

In this case col3 should be hidden, because no values in column.

col1 col2 col3
 v1   v4
 v2
 v3

I'm using following expression on columns Hidden property:

=IIF(Fields!Test5.Value = "",TRUE,FALSE)

This expression working, but It hidding each blank(empty) field, even all column isn't empty. It should hide column only when there is no values at all.

Upvotes: 4

Views: 28564

Answers (4)

irina
irina

Reputation: 1

Here is what worked for me - combination of CountDistinct aggregate function and check that the first value is Nothing.

Example of the condition for a column visibility:

=iif(
      (
        CountDistinct
                     (
                      Fields!Settlement_Manager.Value, "dsDetails"
                    ) = 1 
                   Or CountDistinct
                                  (
                                   Fields!Settlement_Manager.Value,     
                                   "dsDetails"
                                  ) = 0
                   ) 
                 AND IsNothing
                             (first
                                   (
                                    Fields!Settlement_Manager.Value, 
                                    "dsDetails"
                                   )
                            ), true, false
                    )

Upvotes: 0

Gurpreet Singh
Gurpreet Singh

Reputation: 1

For me this worked

= IIF(Max(Fields!Test5.Value)= "",TRUE,FALSE)

Notice that I have not added Dataset name in here.

Upvotes: 0

niks
niks

Reputation: 125

in case of an SSRS report, right click on the column and click Textbox Properties, choose Column Visibility and write down the below expression on "Show or hide based on an expression"

You can use below if the column value is Null:

=IsNothing(Fields!Column.Value)

Upvotes: 4

sdrzymala
sdrzymala

Reputation: 387

You can use:

=IIF(Max(Field, Dataset)= "",TRUE,FALSE)

If the maximum value is empty it means that there is nothing, and make your hide expression on column.

Upvotes: 8

Related Questions