Skip to Content

If condition in crystal report to handle Null Values

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Aug 01, 2017 at 05:37 PM

    <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

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 01, 2017 at 08:04 AM

    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

    Add comment
    10|10000 characters needed characters exceeded