Special characters in formula for Base report

[PhilipK]

In formatting a report in Base, I want to concatenate two fields in a single space, provided that they both contain data, and I want to place them on separate lines. The point is that, if I define two report fields, one above the other, these will always occupy two lines in the report, since they do not shrink to zero height when empty.
I thought that it might be possible to place a formula in the single field something like this:
= [Field1] & (IF IsBLANK([Field2]);; CRLF & [Field2])

I have tried CHR(13) for this, but the formula does not seem to recognize the CHR function. Is there a way of doing this?

[erAck]

That’s because the function’s name is CHAR instead, so CHAR(13) and to actually get CrLf you also want it be followed by & CHAR(10)

[PhilipK]

Unfortunately that made no difference (I did try both CHR and CHAR). My latest experiment - simplified as far as possible - used the formula
"Line 1"&char(13)&char(10)&"Line 2"
Without the special characters, the output, as expected, was “Line 1Line2”. With them added, the output was blank.

[Villeroy]

Use separate report controls for “Line 1” and “Line 2”.
Alternatively, you can concatenate the strings in a query:
SELECT "Line 1" || CHAR(10) || "Line 2" AS "Concatenation" FROM "somewhere". If the report control is tall enough, the 2 lines are displayed correctly. I’ve just tested.
Any operation with a Null value returns Null.
SELECT COALESCE("Line 1" || CHAR(10). '') || COALESCE("Line 2", '') AS "Concatenation" FROM "somewhere" should take care of this, showing either one string or none.

[PhilipK]

I am sorry, I cannot get that to work, but maybe I am not understanding you correctly. I am aware of the issue with nulls and fields with zero-length strings (not the same, and it seems that both have to be allowed for); but it does not seem to me that the data source for a report control accepts an SQL statement.
In a previous incarnation of this database (in the late lamented Paradox) an empty field in a report could shrink to zero height, but this seems not to be the case in LibreOffice Base (hence using separate controls one above the other does not achieve what I want).

[Villeroy]

Store the SELECT statement as a query and make that query source of your report.
persons_concat.odb (17.2 KB)

[RobertG]

Auto Grow of a field in a report will work like automatic row height in a Writer table. Min-height will be the height of the font. So you couldn’t create a field with a defined font and shrink it, if there is no content.

Your report depends of a datasource. Might be a table, a query or a view. I would only use tables or views for a report, because Report Builder might have problems to analyze the code of a query. In a view you could concatenate two fields to one with a linebreak like @Villeroy has shown. The code depends on the type of database you use.

Do you use internal HSQLDB, Firebird or which database do you use?

[cpb]

PhilipK,
another user had the same issue, checkout this thread:

Base report: is is possible to have more than one detail section

I am trying to create a roster style report. Occasionally, a given person will have a “backup” person, indicated by additional fields on the query record not being blank. Unfortunately, I don’t have enough space to simply add the additional name on the end of the line. What I would like would be to simply have a second line in the output for this case. I can do conditional printing on fields, but even if the field is not printed, it still takes up space. So if I have fields for the backup i…

[PhilipK]

The source of my report is a table, held externally to LibreOffice in an SQLite database, which is itself the answer to a complex query. HOWEVER, the solution to my problem was revealed by the link to the other thread on a similar issue. While neither ‘CHR(13)’ nor ‘CHAR(13)’ was recognized in the formula for the report control, '[Field1]&UNICHAR(13)&[Field2]' is and generates the desired second line (without UNICHAR(10))!
Thank you, cpb, for that link!