[Solved] AddressLine1 AddressLine2 line Field Suppression

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] AddressLine1 AddressLine2 line Field Suppression

Postby Minderbinder » Sun Aug 02, 2015 9:22 am

Sorry, but I am very new at this, and I'm not sure where (or where) I would be able to suppress and empty AddressLine2 and also remove the vertical space that had been allotted for it (on a report?). I understand how to, in queries, take out horizontal space between fields, and then use the new "field" in reports I create in Report Builder. Beyond that, I understand very little about shrinking up spaces that are not occupied.
Thanks for any help.
Last edited by Hagar Delest on Sat Sep 05, 2015 12:09 pm, edited 1 time in total.
Reason: tagged [Solved].
Love/Hate Technology
OOo 4.1.1
Windows 7
Minderbinder
 
Posts: 4
Joined: Sun Aug 02, 2015 8:41 am

Re: AddressLine1 AddressLine2 line Field Suppression

Postby Villeroy » Sun Aug 02, 2015 12:23 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24584
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: AddressLine1 AddressLine2 line Field Suppression

Postby Minderbinder » Sun Aug 02, 2015 12:47 pm

Sorry, I should have made it clear that I was creating a report in Base - not a mail merge.
Love/Hate Technology
OOo 4.1.1
Windows 7
Minderbinder
 
Posts: 4
Joined: Sun Aug 02, 2015 8:41 am

Re: AddressLine1 AddressLine2 line Field Suppression

Postby Villeroy » Sun Aug 02, 2015 1:23 pm

Sorry, my bad. I did not even read your posting to the end after reading "AddressLine2 suppression". I don't know much about the report builder.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24584
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: AddressLine1 AddressLine2 line Field Suppression

Postby chrisb » Thu Aug 06, 2015 6:42 pm

Hello Minderbinder,

the area occupied by a text box is permanent & cannot be suppressed.

if your objective is to remove the white space created by an empty address field then this can be achieved by concatenating your data into a single string.

assuming you have the fields 'Name', 'AddressLine1', 'AddressLine2', 'PostCode' & that 'AddressLine2' may contain a null value then the query on which the report is based could contain the code:-
Code: Select all   Expand viewCollapse view
select
"Name"
|| char(10)
|| "AddressLine1"
|| char(10)
||
case when "AddressLine2" is not null then "AddressLine2" || char(10) else '' end
||
"PostCode"

as "Name&Address"

from "MyTableName"
the case function checks whether or not the field "AddressLine2" contains a value.
if it does then the contents + char(10) (line feed) are appended.
if it does not then an empty string is appended.

your text box must be deep enough to display the maximum number of lines possible (in this example that would be 4).
open office 4.1.4 & LibreOffice 5.4.2.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.0 (Split) on Windows 10
chrisb
 
Posts: 148
Joined: Mon Jun 07, 2010 4:16 pm

Re: AddressLine1 AddressLine2 line Field Suppression

Postby Villeroy » Thu Aug 06, 2015 9:00 pm

Thank you for pointing out this simple solution.
Alternatively:
Code: Select all   Expand viewCollapse view
select
"Name"
|| char(10)
||  "AddressLine1"
|| coalesce(char(10)|| "AddressLine2", '')
|| char(10)
|| "PostCode" as "Name&Address"
from "MyTableName"

The concatenation of a char with Null gives Null (like any other operation with Null) and coalesce returns the empty string ('' two single quotes) in this case.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24584
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: AddressLine1 AddressLine2 line Field Suppression

Postby Minderbinder » Fri Aug 07, 2015 7:28 am

That definitely improved things - thanks to you both! I'd previously tried something like what chrisb suggested, but I had been using CHR(10) - not CHAR(10); and I had been using the Oracle Report Builder interface (not sql) to build the query. This produced errors. I am trying to interpret Villeroy's char and coalesce remarks (doing online research) to see the advantages, but may have follow-up questions. I am assuming there is not a way to make the report's "text box" conform to three lines when there are only three - again this is in the report builder extension, with which I understand you may not be completely familiar. Again - really appreciate the help!
Love/Hate Technology
OOo 4.1.1
Windows 7
Minderbinder
 
Posts: 4
Joined: Sun Aug 02, 2015 8:41 am

Re: AddressLine1 AddressLine2 line Field Suppression

Postby chrisb » Fri Aug 07, 2015 3:10 pm

Hello Minderbinder,

the suggestion of Villeroy in using the 'coalesce' function is the most appropriate solution.

the sole purpose of 'coalesce' is to eliminate nulls.
if the field contents are valid (not null) then they are selected.
if the field contents are unknown (null) then a predetermined value is selected.

as Villeroy said any operation which involves a null value will result in null.
so the result of char(10) (a known value) when concatenated with an unknown value (null) is null.

with name & address fields (which can vary enormously) i set every field a default value of '' (2 single quotes, empty string).
for labels, envelopes & occasionally display purposes i use the combination of char(10) & concatenation.
every field is tested using 'case when "MyFieldName" > '' then'................. function.
i suspected that you may have null values but posted without thinking it through.
open office 4.1.4 & LibreOffice 5.4.2.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.0 (Split) on Windows 10
chrisb
 
Posts: 148
Joined: Mon Jun 07, 2010 4:16 pm

[SOLVED] AddressLine1 AddressLine2 line Field Suppression

Postby Minderbinder » Fri Aug 07, 2015 6:01 pm

You have been immensely helpful. Thanks for the guidance.
Love/Hate Technology
OOo 4.1.1
Windows 7
Minderbinder
 
Posts: 4
Joined: Sun Aug 02, 2015 8:41 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 2 guests