cancel
Showing results for 
Search instead for 
Did you mean: 

If condition in crystal report to handle Null Values

former_member309127
Participant
0 Kudos

Hello, I am using Crystal Version SAP BusinessObjects Crystal Reports 2013 Support Pack 4 Version 14.1.4.1327 CR Developer.

I want to display address of an employee in one string. If any of the field is Null then it should skip that field and fetch the next field.

I am trying to achieve this in multiple if condition in crystal report formula editor. The formula displays address string if all fields are not null. If either of the field is null then it does not displays the address.

My formula is as follows:

stringVar paddress := "";

If IsNull({PrimaryAddressLine1}) = False OR {PrimaryAddressLine1} <> "" Then

(If IsNull(PrimaryAddressLine1}) = True OR {PrimaryAddressLine1} = "" Then

paddress := ""

Else

paddress := ProperCase({PrimaryAddressLine1})

);

If IsNull({PrimaryAddressLine2}) = False OR {PrimaryAddressLine2} <> "" Then

(If IsNull({PrimaryAddressLine2}) = True OR {PrimaryAddressLine2} = "" Then

paddress := paddress + ""

Else

paddress := paddress + ", " + ProperCase({PrimaryAddressLine2})

);

If IsNull({PrimaryAddressLine3}) = False OR {PrimaryAddressLine3} <> "" Then

(If Isnull({PrimaryAddressLine3}) = True OR {PrimaryAddressLine3} = "" Then

paddress := paddress + ""

Else

paddress := paddress + ", " + ProperCase({PrimaryAddressLine3})

);

If IsNull({PrimaryTown}) = False OR {PrimaryTown} <> "" Then

(If IsNull({PrimaryTown}) = True OR {PrimaryTown} = "" Then

paddress := paddress + ""

Else

paddress := paddress + ", " + Propercase({PrimaryTown})

);

If IsNull({PrimaryRegion}) = False OR {PrimaryRegion} <> "" Then

(If IsNull({PrimaryRegion}) = True OR {PrimaryRegion} = "" Then

paddress := paddress + ""

Else

paddress := paddress + ", " + Propercase({PrimaryRegion})

);

If IsNull({PrimaryCountry}) = False OR {PrimaryCountry} <> "" Then

(If IsNull({PrimaryCountry}) = True OR {PrimaryCountry} = "" Then

paddress := paddress + ""

Else

paddress := paddress + ", " + Propercase({PrimaryCountry})

);

If IsNull({PrimaryPostCode}) = False OR {PrimaryPostCode} <> "" Then

(If IsNull({PrimaryPostCode}) = True OR {PrimaryPostCode} = "" Then

paddress := paddress + ""

Else

paddress := paddress + ", " + Propercase({PrimaryPostCode})

);

If IsNull({PrimaryPhoneNumber}) = False OR {PrimaryPhoneNumber} <> "" Then

(If IsNull({PrimaryPhoneNumber}) = True OR {PrimaryPhoneNumber} = "" Then

paddress := paddress + ""

Else

paddress := paddress + ", Tel.: " + Propercase({PrimaryPhoneNumber})

);

paddress

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

<soapbox>If the value of an expression is true or false, then using "=true" or "=false" is redundant and can make processing take longer. </soapbox>

You also want to include and punctuation or additional text in the formula as well. Following this rule and Ian's suggestion to break things down into separate formulas, your formula for {@Address1} would be something like this:

if IsNull({PrimaryAddressLine1}) or {PrimaryAddressLine1} = "" then "" else ProperCase({PrimaryAddressLine1}) + " "

You would do the same thing for the other two address lines.

{@Town}

if IsNull({PrimaryTown}) or {PrimaryTown} = "" then "" else ProperCase({PrimaryTown}) + ", "

{@Region}, {@Country}, and {@PostalCode} follow the same pattern.

{@Phone}

if IsNull({PrimaryPhoneNumber}) or {PrimaryPhoneNumber} = "" then "" else "Tel.: " + {PrimaryPhoneNumber}

You can then either create a formula called {@Address} that would be this:

{@Address1}+{@Address2}+{@Address3}+{@Town}+{@Region}+{@Country}+{@PostalCode}+{@Phone}

Or you would place a text box on your report and pull each of the formulas into it in order.

You could also set your formulas to "Use default values for null" which would give you an empty string when a value is null, then you can take out the IsNull() check in the formulas.

-Dell

former_member309127
Participant
0 Kudos

Thank You Christy.

Even you solution worked.

-Sailee

Answers (1)

Answers (1)

Former Member
0 Kudos

I find the easiest way to do this is use a formula for each address field and then either join them together in a text box or another formula

eg

@Address1

If IsNull({PrimaryRegion}) = False OR {PrimaryRegion} <> "" Then {PrimaryRegion}&' ' else ''

This creates a field plus space or nothing.

Repeat for each field and then concatenate without spaces

Ian

former_member309127
Participant
0 Kudos

Thank You Ian.

The solution worked.

-Sailee