Modifying the expression

Question

Modifying the expression

VSSGeorge

SSCrazy Eights

Points: 8146
More actions
October 23, 2014 at 12:50 am

#294413

Hi
I have a SSRS expression like
=Fields!internalPartnerName.Value & "
" & Fields!addressLine1.Value & "
" & Fields!addressLine2.Value & "
" & Fields!addressLine3.Value & "
" & Fields!zipCode.Value & "
" & Fields!city.Value & "
" & Fields!countryName.Value
Here if addressLine2 & addressLine3 fields in database are empty, an empty space will be shown in the SSRS report.
How to avoid that empty space, by modifying the expression?

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

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Empty as in NULL or empty as in an empty string?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

VSSGeorge SSCrazy Eights Points: 8146 More actions · Answer 2

In both cases, ie. If DB field is NULL or DB field is empty, it shows an empty space in the report.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

You can check for NULL values with the IsEmpty funtion. For empty strings you can simply check the length of the string. If it is 0, you don't need to add a space.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

VSSGeorge SSCrazy Eights Points: 8146 More actions · Answer 4

VSSGeorge

SSCrazy Eights

Points: 8146

October 23, 2014 at 2:18 am

#1754261

Please modify my code so that I can get the idea.

David Burrows SSC Guru Points: 65138 More actions · Answer 5

See answer

Far away is close at hand in the images of elsewhere.
Anon.

VSSGeorge SSCrazy Eights Points: 8146 More actions · Answer 6

Thanks for the help.

This was the existing code which I wanted to change for two textboxes.

=Fields!internalPartnerName.Value & "

" & Fields!addressLine1.Value & "

" & Fields!addressLine2.Value & "

" & Fields!addressLine3.Value & "

" & Fields!zipCode.Value & "

" & Fields!city.Value & "

" & Fields!countryName.Value

= Fields!internalPartnerName.Value & VbCrLf &

Fields!addressLine1.Value & VbCrLf &

Fields!addressLine2.Value & VbCrLf &

Fields!addressLine3.Value & VbCrLf &

Fields!zipCode.Value & VbCrLf &

Fields!city.Value & VbCrLf &

Fields!countryName.Value

I changed the code this way & it worked.

=Fields!internalPartnerName.Value & "

"

& Fields!addressLine1.Value & "

"

& IIF(IsNothing(Fields!addressLine2.Value) OR Fields!addressLine2.Value = "", Nothing, Fields!addressLine2.Value & "

")

& IIF(IsNothing(Fields!addressLine3.Value) OR Fields!addressLine3.Value = "", Nothing, Fields!addressLine3.Value & "

")

& Fields!zipCode.Value & "

"

& Fields!city.Value & "

"

& Fields!countryName.Value

=Fields!internalPartnerName.Value & VbCrLf & Fields!addressLine1.Value & VbCrLf & IIF(IsNothing(Fields!addressLine2.Value) OR Fields!addressLine2.Value = "", Nothing, Fields!addressLine2.Value & VbCrLf ) &

IIF(IsNothing(Fields!addressLine3.Value) OR Fields!addressLine3.Value = "", Nothing, Fields!addressLine3.Value & VbCrLf ) &

Fields!zipCode.Value & VbCrLf &

Fields!city.Value & VbCrLf &

Fields!countryName.Value

Steven.Howes SSCertifiable Points: 6230 More actions · Answer 7

Just to be safe you should do the same for the other fields as well. I used to get caught but that all the time. Enough modifications so it works, then move to the next fire. Then a user leaves a different field blank, and I'm back fighting the same fire...