IF function

[Nocton]

I am using the Oracle Report Builder and I cannot get the IF function to work properly. I have two fields in the table:
[Gender] which can be 1 or 2 for Male or Female
and
[MailingList] which is Boolean and can be True or False

The data field formula: IF([Gender]=1;"Female";"Male") correctly returns Male or Female on the report
but the formula IF([MailingList];"Mail";"No Mail") returns only TRUE or FALSE
I have also tried IF([MailingList]=TRUE();"Mail";"No Mail") but that does not work either.

What am I doing wrong?

Nocton

[chrisb]

HI,

I have come across this problem previously myself and was unable to find a suitable workaround.
Best advice I can give to anyone using Oracle Report Builder is to only use boolean fields where you want to view the contents as TRUE or FALSE.

If this was my database I would be inclined to make a new table containing just 2 fields and create a relationship with the main table.
This way queries and therefore reports based on queries will always display meaningful data.
'ID' [Integar] and Primary Key. 'MName' [VARCHAR (7)].
It would only contain 2 records where ID = 0 MName = 'No Mail' & ID = 1 MName = 'Mail'

However the easy answer which I suspect you already know is that if you edit your table and change field type to [SMALLINT] then your formula will work as expected.
Make a backup of your table first by dragging it down a little into a clear space and hit 'Create'.

Anyone who may use ORB should ensure that database records are never left empty when using boolean, date or time fields.

[r4zoli]

You both makes the mistake, what I did when submitted issue 112721.

Possibly you both created report with wizard, and it recognize the fields correctly and set their format to Boolean.
When you try to print it you get boolean value, against what you expected text, and it is natural to get boolean a boolean formatted report field.

Solution: On report field property browser "General" tab, change "Format" property to "Text" format.

[chrisb]

Hello r4zoli,

First of all my field was set to Text. I assumed that Nocton also set his field likewise due to the fact that his other field 'Gender' produced the expected result.

However after reading your post I have taken a look at issue 112721 and downloaded your attachment.

I used the formula
IF([Mail]=1;"Mail";"No Mail")
And for comparison you used the formula
IF([Mail];"Mail";"No Mail")
The difference is I inserted '=1'
This resulted in every row returning 'No Mail' and was the reason my formula failed (BIG Surprise).
After deleting the '=1' the formula now works as it should.

It just goes to show that sometimes we go about things the same old way without really thinking and the smallest detail can make a big difference.
I would like to thank you for your help and support. You guys provide a valuable service without which Open Office would be a real struggle.

[Nocton]

Thank you both for the replies.
In fact I did use the wizard and did indeed initially have the format set to TRUE as a result. However, setting the format to 'Text' did not solve the problem - I just got a blank output or 0 and 1. The string in the Format box always shows '1234.57' even though in the format window it shows @.
I have already got round the problem more or less in the way chrisb mentioned, making separate tables for each of the alternatives and running a query first to produce the correct output for the report.

One final point, I find that Base is very slow on reporting - much slower than Access with which I am very familiar. For this report I am outputting about 200 records with 22 text boxes. It takes many minutes (3-5) to produce the report. In fact the initial report appears after a minute or so, but one cannot scroll up and down the 200 pages of output until several minutes later, i.e. the whole document takes that long to be created. Any ideas? Or is this slowness normal in Base?

[r4zoli]

Thank you both for the replies.
In fact I did use the wizard and did indeed initially have the format set to TRUE as a result. However, setting the format to 'Text' did not solve the problem - I just got a blank output or 0 and 1. The string in the Format box always shows '1234.57' even though in the format window it shows @.

It is known bug in OOo 3.1 and SRB 1.1.0 which solved in later versions.

One final point, I find that Base is very slow on reporting - much slower than Access with which I am very familiar. For this report I am outputting about 200 records with 22 text boxes. It takes many minutes (3-5) to produce the report. In fact the initial report appears after a minute or so, but one cannot scroll up and down the 200 pages of output until several minutes later, i.e. the whole document takes that long to be created. Any ideas? Or is this slowness normal in Base?

It is normal, reports creates in java that is slower than other part of OOo, mostly when you use complicate queries.
The OOo 3.3 with ORB 1.2.1 will be faster a little.