Skip to Content
0

If condition in crystal report to handle Null Values

Aug 01, 2017 at 06:30 AM

75

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Dell Stinnett-Christy Aug 01, 2017 at 05:37 PM
0

<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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank You Christy.

Even you solution worked.

-Sailee

0
Ian Waterman Aug 01, 2017 at 08:04 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank You Ian.

The solution worked.

-Sailee

0