Kbase P103978: Crystal Reports: How to suppress the default rounding of a decimal field longer than 15 digits?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/29/2008 |
|
Status: Verified
GOAL:
Crystal Reports: How to suppress the default rounding of a decimal field longer than 15 digits?
GOAL:
Crystal Reports: How to report the true value of a decimal field without loss of accuracy due to Crystal Reports default rounding process.
GOAL:
Crystal Reports: How to create an 'SQL Expression Field' in a Crystal Reports report?
GOAL:
How to use the SQL-92 TO_CHAR Function to override the default Crystal Reports behavior of rounding decimal fields with more than 15 digits.
FACT(s) (Environment):
Windows
Progress 8.0x
Progress 9.x
OpenEdge 10.x
FIX:
By default, many applications including EXCEL, ACCESS and Crystal Reports force round database table decimal fields having than 15 digits in their integer and their decimal parts. This creates a problem for applications that must list the true value of such long decimal fields.
To preempt this default Crystal Reports rounding behavior and display the value of long decimal fields accurately in the report, one must create and use an 'SQL Expression Field' instead of the field itself. SQL expressions are like formulas, but they are written in SQL.
The following is a step by step procedure to demonstrate the use of an 'SQL Expression Field' to retrieve and report the values of a long database field named "FieldName" from a table named "TableName":
1. On the Standard toolbar, click Field Explorer. (Or you can select View-> Field Explorer). The Field Explorer dialog box appears.
2. Right click on the SQL Expression Fields and click New. The SQL Expression Name dialog box appears.
3. Enter a name in the Name box, and then click OK. The Formula Workshop appears with the SQL Expression Editor active.
4. Type the SQL expression:
TO_CHAR("TableName"."FieldName")
in the SQL Expression Editor.
5. Click Save.
6. Drag the newly created field from the Field Explorer dialog box and drop it where desired on the report.
7. Select the Preview tab. Observe that the long decimal field is displayed without any rounding.