ToNumber() gives #Error

Hi All,

I have read many topics but could not figure out the solution.
Can someone of you help you know the fix for this.
I have a measure object(which is number column from database) coming from Universe. When i try to use ToNumber() in the report variable i get #Error. If i dont use ToNumber() function the data is displayed as text and left aligned. The number format too does not apply on that. Why do i get #Error?

Is there any way i can convert the text display in the report to number and apply the Format Number on the column?

Please help.


Achilies :india: (BOB member since 2008-12-04)

Hi,

Can you Please define which database u r using?

Regards,
Atm


atulm (BOB member since 2012-06-30)

So it’s a measure object on a character field? Something is messed up…

First check the actual format of the field in the database. Then check the format of the universe object. If there is a mismatch (i.e., database field is VARCHAR2 but object is a Number), that could cause your problem.


joepeters :us: (BOB member since 2002-08-29)

How is it defined in the universe?
It needs to be defined as a number. Then you wouldn’t need ToNumber

Thank you all for the responses.

Atulm,
I am using oracle database.

Mark,
In Designer it is defined as a measure object and tyep as ‘Number’ Also, it has a formula as below:

sum
(case when @select(folder/object)='10' THEN @select(folder2/object2) ELSE 0 END)

In the above formula i saw object2 defined from database column and is a Number datatype and a measure object

Jeopeters,
I have checked in ‘Insert Tables’ list where i found the object2 to as a measure object and the column on which it is defined is as a Number column.

Thanks again.

[Moderator Edit: Added code formatting - Andreas]


Achilies :india: (BOB member since 2008-12-04)

Change ‘10’ to 10

If everything is defined as a number, then the ToNumber() function won’t work. You should be able to just set the cell format as you desire, or set the format of the object in the universe to make the setting apply to future reports.

Joe


joepeters :us: (BOB member since 2002-08-29)

Hi Mark,
Why you suggesting Change ‘10’ to 10…is it that’10’ is making entire column as Chrater. Becuase else is anyways defined as 0, without ‘’


niks5687 (BOB member since 2011-01-03)

Yes.
‘10’ does not equal 10. ‘10’ is character, 10 is number.

That’s true if @select(folder/object) is a numeric field.


joepeters :us: (BOB member since 2002-08-29)

I’m looking at the case statement Joe. CASE WHEN x then ‘10’ else 0 END will give a char output. I’d imagine that if the object is set to numeric in the universe it gets a warning when parsed.

It doesn’t, at least in the test I just did. It’s just comparing an @select() against a static value, which wouldn’t affect the data type of the CASE WHEN itself.

I’ll bet that you’re right, in that folder\object is probably numeric and thus the value should be 10, but if it really is a character object, then ‘10’ is correct.


joepeters :us: (BOB member since 2002-08-29)

Thanks you all.

But i have a strange scenario. In the column only a few values show #Error and other numbers display correct.


Achilies :india: (BOB member since 2008-12-04)

What do you see if you don’t use ToNumber()?


joepeters :us: (BOB member since 2002-08-29)

Hello I was wondering if anyone ever resolved this last issue, I have a string which I have converted using ToNumber but where my number is greater than 1000 I get a #ERROR, all values under 1000 output correctly.

I would really appreciate some help with this as it has taken me days trying to find a post that has related to my issue.

Thanks
Claire


chart68 :uk: (BOB member since 2012-02-22)

I would guess that numbers over a thousand have a comma in them, which is causing the tonumber function to error. If so, simply replace the comma in the string with a null and then apply the tonumber function,e.g.:

=ToNumber(Replace([Your String];",";""))

HTH
NMG


mcnelson :uk: (BOB member since 2008-10-09)

This works fantastic thank you so much for your help :smiley:


chart68 :uk: (BOB member since 2012-02-22)