Report formatted with names&addresses

[madhg]

Hello, I'm newish to building databases. I've got a database with first names, surnames, addresses and other data for a couple of hundred people. I'm trying to build a report in the form

Edith Wharton
34, First Avenue
Jamesville
CA 234323
USA

Henry James
Green Mansions
Hope Street
Broad Green
Manchester
Lancashire
M21 1AA
UK
.....

I'm using the Sun Report Builder on OO 3.01, on WinXP. I'm part way there, BUT:

(1) I need to do something (with concatenation I guess) to get the first and second names arranged with just one space between them.

(2) There are several fields for different parts of the address, and the report has blank lines when an address field is empty. Can I get round that?

Any pointers would be appreciated.

[TheGurkha]

If you don't mind using Writer, you can do what you describe in Mail Merge from your database very easily. You can also suppress blank lines.

This is a good tutorial: FSM Mail merge Tutorial

[madhg]

If you don't mind using Writer, you can do what you describe in Mail Merge from your database very easily. You can also suppress blank lines.

I thought it would be neater and easier for the end-user to have reports built into Base, so that there's just one Base file to deal with.

[TheGurkha]

OK, well hopefully someone will be through here soon with a Base-centric solution for you.

[r4zoli]

(1) I need to do something (with concatenation I guess) to get the first and second names arranged with just one space between them.

Code: Select all

SELECT "FistName" ||' '|| "LastName"  AS "Name" FROM "Table1"

(2) There are several fields for different parts of the address, and the report has blank lines when an address field is empty. Can I get round that?

For hiding empty fields, insert into fields which can be empty, into Conditional Print expression property, on General property tab:

Code: Select all

NOT(ISBLANK([Fieldname]))

From SRB documentation:

Conditional Print Expression: A conditional print expression allows the user to specify whether an element or section should or should not be visible when executing the report. If the print expression evaluates to true, then the element will be printed in the report. The syntax is the same as for functions.

[madhg]

Thanks for pointing me to the report builder doc wiki - I hadn't found that, I was using the SRB User Guide which has less detail.

Haven't got it to work yet. When I put NOT(ISBLANK(Address3)) into the conditional print expression box, it prevents that field from being printed even when it is not blank. But more important...

...because each field in my report is a box in a fixed location, I don't see how I can prevent blank fields from leaving a gap in the formatted address, like this

name
street

.        gap where in other addresses a suburb etc. may appear

city

Perhaps I need the <<fieldname>> [new paragraph] <<fieldname>>... structure provided by mailmerge.

[r4zoli]

I think it is not possible in SRB, you can not move dynamically fields, and not see any solution, in planned SRB features.
You can issue an enhancement, [Tutorial] Reporting bugs or suggestions, put RPT into subject.

[madhg]

I think it is not possible in SRB, you can not move dynamically fields, and not see any solution, in planned SRB features.

OK, thanks for this answer. It seems that Base does not do what I need. I could get it via mail merge using Writer. But I think mail merge is more complicated in OpenOffice than in MS Word (which needs fewer clicks, and hides blank fields automatically, without having to insert special code). So I will stick with the system as I inherited it, holding data in Excel and extracting it via mail-merge using Word.

It would have been nice to abandon MS Office and use OO instead. Maybe some time in the future.

[fmartagong]

(1) I need to do something (with concatenation I guess) to get the first and second names arranged with just one space between them.

Code: Select all

SELECT "FistName" ||' '|| "LastName"  AS "Name" FROM "Table1"

(2) There are several fields for different parts of the address, and the report has blank lines when an address field is empty. Can I get round that?

Hello:
My english is very poor, sorry.

I'm trying to do this, but I have a problem. I try to concatenate date fields a query. The data appear, but in american format, when it is in spanish format.
For example: I want to appear
John was born on November 5
So I wrote:

Code: Select all

SELECT "FistName" ||' '|| "was born on "||' '|| "BirthDate"  AS "Text1" FROM "Table1"

However to appear:
John was born on 2009-11-5

Can anyone help me? Thanks.

[eremmel]

You might split your date into three parts and join them together again. For possible function names see HSQLDB SQL syntax. An example is:

Code: Select all

MONTHNAME(date) || ' ' || DAY(date) || ' ' || YEAR(date)

. You might also try to change the date format of the date field in the table.

[Sliderule]

fmartagong:

You asked:

For example: I want to appear
John was born on November 5

Another way to do this, is to 'format' the date the way you want, with the SQL function TO_CHAR . . . you can read about it at:

http://wiki.services.openoffice.org/wik ... _Functions

So, since you want the date to only show a MONTH and a Day of the Month . . . you could use: 'MONTH d' . . . ( what is between the single quotes is the the format to display the date ) . . . for example:

Code: Select all

SELECT "FistName" || ' was born on ' || TO_CHAR("BirthDate", 'MONTH d') AS "Text1" FROM "Table1"

I hope this helps, please be sure to let me / us know.

Sliderule

[fmartagong]

So, since you want the date to only show a MONTH and a Day of the Month . . . you could use: 'MONTH d' . . . ( what is between the single quotes is the the format to display the date ) . . . for example:

Code: Select all

SELECT "FistName" || ' was born on ' || TO_CHAR("BirthDate", 'MONTH d') AS "Text1" FROM "Table1"

I hope this helps, please be sure to let me / us know.

Yes, it's help me. Thanks.