Tonumber function Error

[Former Member]

Hi Every one,

I have a column called ATV  in my sql database.It is a string type.i want to apply rounding for this in report level only.For that I am trying to convert this to number using to number function.After converting if i place this formula filed in my report it is giving error like

The string is non numeric

I need to apply rounding for the field, please suggest me an approach.

The formula written for this field in database is

(SUM(c.sale)/SUM(c.transaction_count)),'-') AS ATV

Here some times transaction_count is zero

[abhilash_kumar · Accepted solution]

Hi Divya,

You get that error because the string field might have something else other than numbers too.

For e.g.: A string field with this value : 12345Abhilash*^%, cannot be converted to a number.

You would need to extract the numbers from the string.

Use this instead of tonumber():

val({string_field})

Hope this helps!

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thank you it is working fine.

[Former Member · Accepted solution]

Hi Abhilash,

I got the result with val function but the problem is according to query if any value isnull then it should dispaly '-' symbol.But after using val function i am getting zeros instead of '-'.But i need to show '-' when the value isnull.

query level  :      IFNULL((SUM(c.sale)/SUM(c.transaction_count)),'-') AS ATV

For that i removed isnull in query level  then immediately it is showing the error in formula that i wrote using val function.

formula:         val({DailyCategoryHourGroupTarget.ATV})

error:              A string is required here

Now i removed the formula from report and placed the ATV field directly.Now it is showing data type as number and i am getting rounding and decimal options.But my problem is for null values it is not giving '-' just displaying empty space as below.

ATV

12

23

empty space

45

56

Now for this  i created a formula like this.

if isnull({DailyCategoryHourGroupTarget.ATV}) then '-' else {DailyCategoryHourGroupTarget.ATV}

It is giving error like   A string is required here.

If i give totext ({DailyCategoryHourGroupTarget.ATV}) it is working but then it is becoming string type.Can you please guide me how to get my result.

[Former Member · Accepted solution]

Hi, check the answer to this on the separate thread that you posted.

Thanks