Problem with text formatting in Report.

users
#1

Greetings,

I am using LibreOffice 5.3.4.2 on Slackware Linux 14.2 (K4.4.75).

I have a database with a text field which may or may not be null. What I want to do is to prefix a dash "-" before the text if the text field is not null. At first, I tried this format:

;;;"-"@

Somewhere, not by me, it got changed to:

[>0]"";[<0]"";"";"-"@

However, this does not work. I get the text when not null, but not the dash in either case.

Is there another way to do this?

Thanks.

Girvin Herr

#2

Greetings,

I tried this in calc (which I am assuming you are using), and it seems
to work for what you describe:

[<>""]-@

The interesting thing is that calc changed it afterwards to

[<>0]-@;Standard

but it still seems to work.

I hope this helps.

Rémy.

#3

Remy,

Greetings.

I tried your suggestion, but it didn't work as expected. LO Report Builder did replace "[<>""]-@" with
"[<>0]-@;General", similar to what you experienced. However, the result is strange and almost indeterminate. Sometimes if the text field is alpha-numeric, the result is "-" without the text. Other times it is the text without the "-" prefix. Sometimes if the text field is numeric-only, the result is the text without the "-". Strange.

I then tried changing the format to "[<>0]"-"@;General" (quotes around dash) with the same results.

Then I tried "[<>0]"-"@;Standard" as your Calc experiment changed, and LO changed it to
"[<>0]"-"@;StanDarD", with the same results.

So, I am not sure what is going on. It almost looks like a bug with the unexpected results.

Thanks.
Girvin

#4

Greetings,

I am using LibreOffice 5.3.4.2 on Slackware Linux 14.2 (K4.4.75).

I have a database with a text field which may or may not be null. What I
want to do is to prefix a dash "-" before the text if the text field is
not null. At first, I tried this format:

;;;"-"@

Somewhere, not by me, it got changed to:

[>0]"";[<0]"";"";"-"@

However, this does not work. I get the text when not null, but not the
dash in either case.

If I format a cell like your first example and enter a letter in that cell,
let's say ”k”, I get:
-k

If I enter a number, the cell remains blank. Is this the behaviour you are
looking for?

The format string doesn't change if I don't change it.

If I set it to [>0]"";[<0]"";"";"-"@ I get the same result, and I should
since the two means exactly the same thing.

Can you give an example that doesn't work?

Kind regards

Johnny Rosenberg

#5

Hi Girvin,

the Report-Builder should better get this information from the view,
which is datasource of the report. All what could be done through SQL
before submitting the data to the report will work faster (and mostly
better).

You have to write in SQL
SELECT '-'||"text" FROM "table"
This will submit all content of the field "Text", which contains
characters (or empty text) with a '-'. All fields, which are NULL, we be
NULL without '-'.

Regards

Robert

#6

Johnny,

Thanks for your response.

I should have been more clear in my original posting. I am not trying to do this in Calc. I am trying to do it in Base Report Generator.

I have a text field in the database which I want to prefix with a dash (-) if, and only if, there is text (not NULL) in that field.

None of the examples you gave will work as I expect (and as the number formatting help implies) in my context (with Base Report Generator). No matter how I format it, the result is strange and almost indeterminate. Sometimes if the text field is alpha-numeric, the result is "-" without the text. Other times it is the text without the "-" prefix. Sometimes when the text field is numeric-only, the result is the text without the "-". Sometimes not one of these. As I said, indeterminate results.

Not getting a number when encountered is not what I am looking for. In all cases, if there is text there, I want it to be in the report. Also, I do not want the dash prefix, if there is no text to be in the report.

So, I am not sure what is going on. It almost looks like a bug with the unexpected results. It also appears the formatting for the Report Generator behaves differently than for Calc.

Thanks.

Girvin

#7

Greetings,

I think I have a solution. I did some research on the Pentaho report generator that is used in the LO Base Report Generator with emphasis on their formula syntax. The following formula placed in the report field's properties "Data" tab in "Data field..." seems to do what I need:

IF(LEN([Suffix])>0;"-"&[Suffix] ;"")

This tests the length of the text string (Suffix) and if not 0 then outputs the string prefixed with a dash. The ampersand (&) concatenates text strings. If the string length is zero, it outputs the NULL string. I tried outputting the string here without the dash to be sure I got all data, as in:,

IF(LEN([Suffix])>0;"-"&[Suffix] ;[Suffix])

but it fails for some reason, outputting "false"  when the string length is 0 instead.

As I said, it seems to be working, unless I breathe on it!

Thanks for all the suggestions.
Girvin