hiding rows that contain no values

Question

I would like to hide rows in my report that contain no values in a certain set of fields.

This question is helpful

How to filter rows with null values in any of its columns in SSRS

i do not understand how to check whether multiple fields are null.

currently, i am doing this:

enter image description here

and under the visibility:

enter image description here

and i typed this expression:

=iif(fields!Jan.Value
+fields!Feb.Value
+fields!Mar.Value
+fields!April.Value
+fields!May.Value
+fields!June.Value
+fields!July.Value
+Fields!Aug.Value
+Fields!Sept.Value
+Fields!Oct.Value
+Fields!Nov.Value
+Fields!Dec.Value="",TRUE,FALSE)

but i am getting this error:

enter image description here

how do i hide a row if the values are null or blank?

please let me know if anything requires clarification – Alex Gordon Apr 09 '13 at 17:22 — Alex Gordon, Apr 09 '13 at 17:22

score 4 · Answer 1 · answered Apr 09 '13 at 17:39

4

You need to use AND (if all of the values need to be 0 to hide the row), and try using LEN()

=iif(len(Fields!Jan.Value) = 0 AND len(Fields!Feb.Value) = 0 AND len(Fields!Mar.Value) = 0,True,False)

I would also use the Visibility property accessed by highlighting the row, and using the Properties window rather than through the Right-Click menu.

answered Apr 09 '13 at 17:39

D.S.

1,413
2
16
26

if its blank/null the len would be 0 – D.S. Apr 10 '13 at 18:07
sure but if it has spaces then its not 0 – Alex Gordon Apr 10 '13 at 18:08
You said below they are all ints. How are you going to have an empty space in an int? If you are attempting to use this method for something similar, then you will need to sanitize the data. Which I would do in the SQL, so the dataset you have to work with in the report is how you want it. – D.S. Apr 10 '13 at 18:14

score 2 · Accepted Answer · edited May 03 '16 at 15:24

I know this solution may not be the best, but it works:

=iif(fields!Jan.Value
    +fields!Feb.Value
    +fields!Mar.Value
    +fields!April.Value
    +fields!May.Value
    +fields!June.Value
    +fields!July.Value
    +Fields!Aug.Value
    +Fields!Sept.Value
    +Fields!Oct.Value
    +Fields!Nov.Value
    +Fields!Dec.Value=0,TRUE,FALSE)

I realized since the fields are all int, i can just add them together and check whether they add up to 0.

score 0 · Answer 3 · answered Apr 09 '13 at 17:38

Does the qualifier have to be any row? I would use a 'Switch' statement then if those are all OR statements and change ISNOTHING(). "" is something according to SSRS. Looks like you got the answer you needed but I generally would do this if this comes up in the future:

=switch(
 Isnothing(Fields!Jan.Value), TRUE
 Isnothing(Fields!Feb.Value, TRUE
 etc.
 , FALSE)

The key to switch is the columns must be different, not the same column with different expressions on it.

thanks so much! i need the row to be hidden if and only if ALL of the mentioned fields are 0 or isnothing — Alex Gordon, Apr 09 '13 at 17:40

hiding rows that contain no values

3 Answers3