on 08-01-2017 7:30 AM
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
<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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.