Hide Row based on Multiple Blank Column Fields - SSRS

Question

Hide Row based on Multiple Blank Column Fields - SSRS

bhutchin

Mr or Mrs. 500

Points: 590
More actions
March 1, 2011 at 12:35 pm

#388947

I am trying to hide a row based on multiple (2) column fields having blank values. This is the syntax I am using.
=iif(Fields!ObsCatItemDecript.Value = " "& Fields!DocValue.Value =" ",TRUE,FALSE)
When I try to render the report I get a syntax error. Any help would be appreciated. Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 1

It looks like you may be trying to concatenate with the & sign, bit if so, you don't need the first [= " "]. I'm sort of guessing here, but you could try

=iif(Fields!ObsCatItemDecript.Value & Fields!DocValue.Value =" ",TRUE,FALSE)

Or maybe the ampersand should be a simple +

=iif(Fields!ObsCatItemDecript.Value + Fields!DocValue.Value =" ",TRUE,FALSE)

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

bhutchin Mr or Mrs. 500 Points: 590 More actions · Answer 2

Thank you very much

this is the statement that worked for me:

=iif(Fields!ObsCatItemDecript.Value + Fields!DocValue.Value =" ",TRUE,FALSE)

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 3

You're welcome - glad I could help.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

vvsut SSC-Addicted Points: 467 More actions · Answer 4

Hi,

I have the same issue and when I try

=IIF(Fields!Column1.Value + Fields!Column2.Value = " ",TRUE,FALSE)

While previewing am getting

"The Hidden expression for the tablix 'Tablix1' contains an error: Input string was not in a correct format."

Where as while trying

=IIF(Fields!Column1.Value & Fields!Column2.Value = " ",TRUE,FALSE)

am not getting the error and the row is still visible...I currently use SSRS 2008 R2.

Can you suggest anything please?

Thanks

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 5

vvignesh86 (3/3/2011)
Hi,
I have the same issue and when I try
=IIF(Fields!Column1.Value + Fields!Column2.Value = " ",TRUE,FALSE)
While previewing am getting
"The Hidden expression for the tablix 'Tablix1' contains an error: Input string was not in a correct format."
Where as while trying
=IIF(Fields!Column1.Value & Fields!Column2.Value = " ",TRUE,FALSE)
am not getting the error and the row is still visible...I currently use SSRS 2008 R2.
Can you suggest anything please?
Thanks

You are probably getting the error since one of the fields is numeric. If that's the case, you can't use that code, but you can see if it evaluates two expressions separated by an AND. One would be for the numeric one: ...Value = 0, the other for the character: ...Value = "". (Don't forget to separate the two statements with an "AND".) Sorry, but it's been a while since I've used SSRS. Maybe someone else will have a better answer.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

vvsut SSC-Addicted Points: 467 More actions · Answer 6

Actually one column is a date and another is a string. I have tried your suggestion by passing blank value as well as NULL and in both cases blank row is still visible. I really appreciate your suggestion. Thank you.

w.plymesser Old Hand Points: 390 More actions · Answer 7

Are the fields you're using for the conditional hiding blank or NULL? If they're NULL, you can use this:

=iif(IsNothing(Fields!ObsCatItemDecript.Value) AND IsNothing(Fields!DocValue.Value),TRUE,FALSE)

You can always make it as complicated as you want, and even do something like this to check multiple states of each field:

=iif((IsNothing(Fields!ObsCatItemDecript.Value) OR Fields!ObsCatItemDecript.Value = zero_blank_or_whatever) AND (IsNothing(Fields!DocValue.Value) OR Fields!DocValue.Value = zero_blank_or_whatever),TRUE,FALSE)