cancel
Showing results for 
Search instead for 
Did you mean: 

Decimal point/comma issue in Crystal Reports

0 Kudos

I'm calling a function in the command protocol of Crystal Reports. It does not give the correct string, as the output is a varchar2. The problem is the decimal point handling.

Seems like Crystal Reports (2016 SP7 with OraClient12Home) does not use the regional (language/country) setting of the operating system (English UK, Windows 7).

When i use the following in the command:

SELECT TO_CHAR(5/2) FROM DUAL

it results: 2,5 while it needs to be 2.5.

I even set the 'Product Locale' and the ' Preferred Viewing Locale' to English in Crystal Reports, but nothing seems to help. The Oracle Database NLS setting is also correct (UK).

Kind regards,

Scubapro66.

Accepted Solutions (0)

Answers (2)

Answers (2)

DellSC
Active Contributor
0 Kudos

CStr is not valid for Oracle.

The issue you're running into is an Oracle issue, not a Crystal issue because you're converting the number to a string in the database, not in the report.

So, you have a couple of options:

1. Return a number from the database without converting it to a string. Crystal will then format the number according to its settings.

2. Use a format string with To_Char(field, format). You can find information about valid format strings here.

-Dell

0 Kudos

Hi Dell,

Thanks for your ideas. Alas they won't work.

1. I cannot change the function in Oracle, because other procedures and actions depend on this function and on the result being a varchar2. Also the unit of measurement is included in the result.

2. This will do no good. For instance, I'm getting back '0,10 mmol/l' while it should be '9.90 mmol-l'.

Only option I see is changing the NLS setting of the session, but that is not allowed in Crystal Reports.

-Marc.

DellSC
Active Contributor
0 Kudos

Can you select the fields the function is using and do the calculation in the report in a formula or a SQL Expression instead of using the function from the database?

If the issue is just that the comma should be a period, you could also do this:

Replace({my field}, ',', '.')

This will replace the comma with a period. However, it won't give you a different result in the number if the Oracle function isn't returning the correct number.

-Dell

venkateswaran_k
Active Contributor
0 Kudos

Dear Marc

You may try either of this formula in Crystal - that solves your number format issue.

CStr (yourvariable, "#,##,##,##,##,###.00")

totext({yourvariable},2,"")

Regards,

Venkat

0 Kudos

Hi Venkat,

Thanks for your answer.

Unfortunately, this will do no good.

For instance, I'm getting back '0,10 mmol/l' while it should be '9.90 mmol-l'.

-Marc.