Writing a report in Business Objects Web Intellignce Client How do you conditionally suppress a row for empty records like Address 3 & 4

RUA Volunteer2?
RUA Volunteer2? used Ask the Experts™
on
How do you hide a cell when there is no data in it so it doesn’t display a blank row.  Example, I’m creating a letter with address at the top.  I don’t know which address lines 1 – 4 will be populated so I have to include all 4 on the letter.

John Doe
Address Line 1
Address Line 2
Address Line 3
Address Line 4
City, Sate Zip

I don’t want blank rows if there is not a Address Line 3 and Address Line 4.  What I get is

John Doe
Address Line 1
Address Line 2


City, Sate Zip

what I want in the case of two missing Address values like Address Line 3 & 4 is this.

John Doe
Address Line 1
Address Line 2
City, Sate Zip

What will I have to do to make this happen in a formula or some conditional suppression like Crystal Reports. Man if you could just merge those two products you would have the Reporting Tool of all time.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
In Crystal you would RIGHT CLICK the section and use the SECTION EXPERT to suppress blank rows.

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Use the PROPERTIES TAB
Uncheck SHOW WHEN EMPTY

mlmcc
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.

Author

Commented:
That does not do it.
The values still hold the blank space...
John Doe
Address Line 1
Address Line 2


City, Sate Zip

I wonder if this will have to be put in a formula.
Can you request attention to the question so that we can get other people looking at this.
Because unless you tried it for yourself and it worked and I did something wrong it is not working.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
There are 2 ways to do this.  It depends on how the fields are set in the report.  The methods are similar.

Method 1 - Assumes you have 1 section with the fields in a column like
    Name
    Address1
    Address2
    City,  State  Zip

Assume you always have data in Name, Address1, City, State, and Zip.  If not these same steps will allow you to "suppress" that field.

Click on the Address2 Field
Click the PROPERTIES TAB
Set AUTOFIT HEIGHT to YES (checked)
Set MINIMUM HEIGHT to 0
Click the 3 ... by RELATIVE POSITION
Set the TOP OF FIELD (lower boxes) to 0 px
   FROM THE BOTTOM OF =[Address1]

Repeat the relative position for City, State, and Zip fields as 0 from the bottom of ADDRESS2
Assume you always have data in Name, Address1, City, State, and Zip.  If not these same steps will allow you to "suppress" that field.

Click on the Address2 Field
Click the PROPERTIES TAB
Set AUTOFIT HEIGHT to YES (checked)
Set MINIMUM HEIGHT to 0
Set SHOW WHEN EMPTY to NO (unchecked)
Click the 3 ... by RELATIVE POSITION
Set the TOP OF FIELD (lower boxes) to 0 px
    FROM BOTTOM OF   =[ADDRESS1]

Method 2 - Assmes you have each field in its own section
Again assume it is ADDRESS2 and its section that need to be suppressed
Click on the Address2 Field
Click the PROPERTIES TAB
Set AUTOFIT HEIGHT to YES (checked)
Set MINIMUM HEIGHT to 0
Click the 3 ... by RELATIVE POSITION
Set the TOP OF FIELD (lower boxes) to 0 px
Select the Address2 Section
Set Minimum Height to 0
Set SHOW WHEN EMPTY to NO (unchecked)
Click the 3 ... by RELATIVE POSITION
Set the TOP OF FIELD (lower boxes) to 0 px
   FROM TOP OF     ADDRESS1 SECTION

Run the report

mlmcc
You might consider creating a variable that concatenates the addressee, the address lines, and the city/state/ZIP into a single address block.

=[Name] + Char(13) + [Address1]+ Char(13) + If([Address2] > ""; [Address2] + Char(13); "") + If([Address3] > ""; [Address3] + Char(13); "") + If([Address4]>""; [Address4] + Char(13); "") + [CityStateZip]

Basically, this builds up a single string made up of only those address lines that are not blank.  You may need to adjust it for your dimension names, but it works in my mockup document.  

Hope that helps
russell richter
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.

Author

Commented:
MLMCC's solution worked

Russel
Yours works in part but I am not sure what is going wrong only the City State Zip shows up. The Fields were spot on. Here is what I changed...and yours also worked.
Var_Full_Name
=[Name Prefix]+" "+[First Name]+" "+[Middle Name]+" "+[Last Name]+" "+[Name Suffix]
Var_City_State_Zip
=[City]+", "+[State]+"  "+[Postal]
Var_Letter_Address
=[var_Full_Name] + Char(13) + [Address1]+ Char(13) + If([Address2] = ""; [Address2] + Char(13); "") + If([Address3] = ""; [Address3] + Char(13); "") + If([Address4]=""; [Address4] + Char(13); "") +[var_City_St_Zip]
Thank you for your help gentlemen very much.
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.

Author

Commented:
That worked well. Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial