[Solved] Removing "NaN" from Report

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

[Solved] Removing "NaN" from Report

Postby ewtay » Mon May 18, 2015 11:00 am

Hi Guys,
Good day to you all.

I'm currently using Oracle Report Builder to generate a Report for my Data.
A lot of the existing field is Empty which will reflect as "NaN" on the report...

I've looked into Conditional Print Expression and uses the following

Code: Select all   Expand viewCollapse view
IF([1] = "NaN";"0"; )


Code: Select all   Expand viewCollapse view
IF([1] = "";"0"; )


Where [1] is my column.
But no luck so far....

How do i reflect the Empty fields on the Report with Blank... or " - "....or "0"...
Please help.....
Last edited by ewtay on Fri May 22, 2015 2:32 am, edited 1 time in total.
openoffice 4.1 on windows 7
ewtay
 
Posts: 44
Joined: Wed Jul 02, 2014 8:44 am

Re: Removing "NaN" from Report

Postby Villeroy » Mon May 18, 2015 1:31 pm

I don't use the report builder but a null (empty) value is not equivalent to the displayed "NaN" text.
You should try:
Code: Select all   Expand viewCollapse view
IF(ISBLANK( [field] );"-";[field])
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26635
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Removing "NaN" from Report

Postby chrisb » Tue May 19, 2015 12:51 am

hello ewtay,

'NaN' stands for 'not a number'.
i have only ever seen this when a text box formatted as a number contains a null value.
if your report is based on a query then you could edit that query & use the 'COALESCE' function as shown below which will replace those null values with a single zero.
Code: Select all   Expand viewCollapse view
coalesce("Your Fieldname", 0) as "Your Fieldname"


if your report is based on a table (also works when based on a query) then edit the report & in the appropriate text box use the following:-
Code: Select all   Expand viewCollapse view
=if(isnumber([Your Fieldname]); [Your Fieldname]; 0)


a text box which is formatted as a number must show a number .i.e. can not be blank
open office 4.1.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 177
Joined: Mon Jun 07, 2010 4:16 pm

Re: Removing "NaN" from Report

Postby ewtay » Tue May 19, 2015 5:50 am

Hi Villeroy & Chrisb,

i have tried both of your codes but the report still return "NaN".
I have confirmed the Formatted field box to be in Number Format "1235" on the report...

pls let me show u the printscreen...
Attachments
33.jpg
22.jpg
11.jpg
openoffice 4.1 on windows 7
ewtay
 
Posts: 44
Joined: Wed Jul 02, 2014 8:44 am

Re: Removing "NaN" from Report

Postby Villeroy » Tue May 19, 2015 12:33 pm

Create a query:
Code: Select all   Expand viewCollapse view
SELECT *, coalesce("Your Fieldname", 0) as "Your Fieldname" FROM "Your Tablename"

and use this query as report source.

If "Your Fieldname" in "Your Tablename" is a text field:
Code: Select all   Expand viewCollapse view
SELECT *, coalesce("Your Fieldname", '0') as "Your Fieldname" FROM "Your Tablename"

or with any other text in single quotes instead of the '0'.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26635
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Removing "NaN" from Report

Postby chrisb » Tue May 19, 2015 11:07 pm

hello ewtay,

the code failed because you enclosed the else value in double quotes.
please follow these instructions & try again.
Code: Select all   Expand viewCollapse view
if(isnumber([1]); [1]; 0)

1 copy the code.
2 open report for edit.
3 if necessary hit F4 to show preference pane.
4 drag out a text box in the detail pane.
5 hit the data tab in preference pane.
6 'Data Field Type' = 'Field or Formula'.
7 'Data field' = paste the code here & hit enter.
8 execute report & hey presto no more 'NaN' regardless of data source (query or table).

Villeroy has given you an alternative solution in the form of a query which again is guaranteed to work.

Looking at your print out it would appear that your label & data fields are both in the Detail Pane.
i suggest you create a header by:-
1 press (ctrl + G) to bring up the sorting & grouping window.
2 click on the list box arrow under 'Field/Expression'.
3 click on the list box to the left of the arrow.
4 drag your labels from the detail pane into the header you have just created.
open office 4.1.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 177
Joined: Mon Jun 07, 2010 4:16 pm

Re: Removing "NaN" from Report

Postby ewtay » Thu May 21, 2015 3:21 am

chrisb wrote:hello ewtay,

the code failed because you enclosed the else value in double quotes.
please follow these instructions & try again.
Code: Select all   Expand viewCollapse view
if(isnumber([1]); [1]; 0)

1 copy the code.
2 open report for edit.
3 if necessary hit F4 to show preference pane.
4 drag out a text box in the detail pane.
5 hit the data tab in preference pane.
6 'Data Field Type' = 'Field or Formula'.
7 'Data field' = paste the code here & hit enter.
8 execute report & hey presto no more 'NaN' regardless of data source (query or table).


Hi Chrisb,
It Work!!!
Thanks so much!

By the way... can i have one more request...?
Can i also set color example grey to the box with "0" in the report..?
openoffice 4.1 on windows 7
ewtay
 
Posts: 44
Joined: Wed Jul 02, 2014 8:44 am

Re: Removing "NaN" from Report

Postby chrisb » Thu May 21, 2015 11:00 pm

hello ewtay,

now that this topic has been solved could you please edit the title of your first post and add the [Solved] tag at the beginning.
if you are unsure as how to do this then hit the following link:- https://forum.openoffice.org/en/forum/viewtopic.php?p=717#p717
ewtay asked
Can i also set color example grey to the box with "0" in the report..?

the answer is yes but as this is way off topic i must ask you to please restate the question in the form of a new thread the reason being that if another user is searching for a solution to this very same issue then we would like them to find it.
open office 4.1.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 177
Joined: Mon Jun 07, 2010 4:16 pm

Re: [SOLVED]Removing "NaN" from Report

Postby ewtay » Fri May 22, 2015 2:33 am

Sure.

I'll do that.

Thanks!
openoffice 4.1 on windows 7
ewtay
 
Posts: 44
Joined: Wed Jul 02, 2014 8:44 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest