[Solved] Removing "NaN" from Report

[ewtay]

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

IF([1] = "NaN";"0"; )

Code: Select all

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.....

[Villeroy]

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

IF(ISBLANK( [field] );"-";[field])

[chrisb]

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

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

=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

[ewtay]

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...

[Villeroy]

Create a query:

Code: Select all

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

SELECT *, coalesce("Your Fieldname", '0') as "Your Fieldname" FROM "Your Tablename"

or with any other text in single quotes instead of the '0'.

[chrisb]

hello ewtay,

the code failed because you enclosed the else value in double quotes.
please follow these instructions & try again.

Code: Select all

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.

[ewtay]

hello ewtay,

the code failed because you enclosed the else value in double quotes.
please follow these instructions & try again.

Code: Select all

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..?

[chrisb]

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:- 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.

[ewtay]

Sure.

I'll do that.

Thanks!