Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

<b>Changing the Format of decimal numbers per Report</b>

Status
Not open for further replies.
Jimdeb03

Jimdeb03

IS-IT--Management
Dec 10, 2002
89
US
My current report in CR9 requires all number fields to display the same number of decimal places according to a number value held in an Access DB. Values in the DB currently by default have eight places to the right of the decimal and any generated fields within the report itself will have the same number of decimal places as well.

What's the best method to format all fields in the report to have the same number of decimal places?

As an example, a database table will list a field as 12.99980000 where eight places exist by default but only four are required to display in the report like 12.9998. The next report may have a requirement of five places and so on.

No rounding must occur.

The actual number of decimal places for each report will be brought into the report by use of a Parameter Field.

I've played around with formulas trying to format certain fields but have been unable to discover a method to format a decimal field differently than it's default setting to say nothing about formatting all fields simultaneously.

Any suggestions?
 
Sort by date Sort by votes
If your report has a parameter to capture the number of decimal places, then create a formula for each decimal field that will be placed on the report e.g.

Database field = {table.field}
Parameter = {?decimal.places}

Create a formula for each decimal field like;

ToText({table.field},{?decimal.places})



Peter Shirley
 
Upvote 0 Downvote
Hmmm - ignore my last post, I just noticed in your original post that 'no rounding must occur'. So, change the formula to truncate instead e.g.

Truncate({table.field},{?decimal.places})

In addition, you'll then need to conditionally format the number of decimals that is displayed - right click on the formula field in your report, select Format Field, and on the number tab, click on the X2 button next to the Decimals (you may have to click on Customize depending on your version of Crystal). Add the {?decimal.places} parameter to the format editor.


Peter Shirley
 
Upvote 0 Downvote
Thanks for the advice but answer me this...
I added my parameter '{?paramNUM_DEC}' to the format editor as you've described above via Format Field / Number tab / X2 button next to Decimals.

I've removed all referernce to the Truncate(,) function from the entire report yet am finding the fields are still being truncated without the above mentioned Truncate function. How is this possible? How is the inclusion of the parameter in the format editor by itself able to perform any truncating?

thanks
Jim
 
Upvote 0 Downvote
I created a Formula Field with the Formula, 'Truncate ({Samp_Data.SD_3}, {?paramNUM_DEC});' and under Decimals is {?paramNUM_DEC});

Record in Access database 12.98760000 10 decimal places

Under Custom Style...
Decimals set to 1.0000000000
Rounding set to 0.0000000001

Number of Decimals to display 4
Displayed in Report as 12.9876 ok

Number of Decimals to display 5
Displayed in Report as 12.98759 NOT OK No rounding allowed
Displayed value should be 12.98760

Number of Decimals to display 3
Displayed in Report as 12.987 OK

I was under the impression that Truncate does not round but apparently it will under the right condition.

If the Database field has under Decimals {?paramNUM_DEC}, the end result will be rounded under the right circumstances. Adding the Formula 'Truncate' as described above returns a 'Bad Formula Result'.

Is there any way to completely turn off Rounding while at the same time dicate how many decimal places to show for a field?
 
Upvote 0 Downvote
I hate to doubt anyone - but you're telling me the database value is 12.98760000 and Crystal somehow 'unrounded' this to 12.98759? Please confirm this, it doesn't seem right...

Peter Shirley
 
Upvote 0 Downvote
Hi,
I confirmed my findings and using Truncate will round down instead of leaving the value alone. Maybe the Database field itself was a factor so the Truncate function was tested against a parameter field with the same result.

My only hunch is having the {?paramNUM_DEC} parameter field in Decimal in the formula must be conflicting with the result. But take out {?paramNUM_DEC} from Decimal and no truncating at all occurs.

I must be missing something but can't figure out where to go from here

thanks
Jim
 
Upvote 0 Downvote
Jim - OK, now you've got me intrigued - post the field type, size, no. of decimals etc. EXACTLY as it is in your MSAccess database. Also confirm the version of MSAccess, how you're connecting to it and Crystal version you're using). I'll whip up a quick database with some sample values and come up with an answer.

Peter Shirley
 
Upvote 0 Downvote
You don't need database access for I'm able to duplicate the problem by use of Parameter fields. Below are results of the test. It's important to note that ?paramSAMPNum is entered precisely as '12.98760000' with the trailing zeros.

I'm using CR ver 9.2.0.448
thanks for your help

Code: 
[COLOR=#ff0000]?paramSAMPNum[/color]                  Parameter Field
Value Type		Number
Custom Style 	 
                Decimal: 1.0000000000
		Rounding: 0.0000000001
		Negatives: None

[COLOR=#ff0000]?paramNUM_DEC[/color]                  Parameter Field
Value Type		Number
Style			(1123)

[COLOR=#ff0000]@DivisionSampleNumber_Formula[/color]   Formula Field
Custom Style 	 Decimal: 1.0000000000
		Rounding: 0.0000000001
		Negatives: None
		Decimals x-2: {?paramNUM_DEC}
Formula Editor
NumberVar Tcate:= Truncate({?paramSAMPNum},{?paramNUM_DEC});
Tcate;

[ul]
[li]Ex 1[/li]
Enter:
?paramNUM_DEC 3
?paramSAMPNum 12.98760000

Results:
?paramSAMPNum 12.98760000
@DivisionSampleNumber_Formula 12.987 OK

[li]Ex 2[/li]
Enter:
?paramNUM_DEC 4
?paramSAMPNum 12.98760000

Results:
?paramSAMPNum 12.98760000
@DivisionSampleNumber_Formula 12.9876 OK

[li]Ex 3[/li]
Enter:
?paramNUM_DEC 5
?paramSAMPNum 12.98760000

Results:
?paramSAMPNum 12.98760000
@DivisionSampleNumber_Formula 12.98759 NOT ok

[li]Ex 4[/li]
Enter:
?paramNUM_DEC 6
?paramSAMPNum 12.98760000

Results:
?paramSAMPNum 12.98760000
@DivisionSampleNumber_Formula 12.987599 NOT ok
[/ul]
 
Upvote 0 Downvote
Hmmmm - I tested that, and you are correct, it does give those results. To me, this is a bug as my idea of truncate is that it should never round (up or down) the original value. I tried this in 8.5 with the same result. Someone out there that's smarter than me will likely come up with a simple solution. I found one, but it's kinda ugly and dependent on there ALWAYS being a decimal place holder in your number:

Left(ToText({?Test Number},10,""),(Instr(ToText({?Test Number},10,""),".")-1)) +
Mid(ToText({?Test Number},10,""),Instr(ToText({?Test Number},10,""),"."),(Instr(ToText({?Test Number},10,""),".") -2 + {?Number of Decimals}))

Where ?Test Number is the number to format (12.98760000) and
?Number of Decimals is the no. of decimals to display (2,5 etc.).

Sorry I don't have a more elegant answer...

Peter Shirley
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

inkbird01
  • Locked
  • Question
Filters > Record Selection for dates between
Replies
4
Views
351
pmax9999
pmax9999
socalvelo
  • Locked
  • Question
Extract Phone Numbers from Text Field
Replies
0
Views
270
socalvelo
socalvelo
LauraW
  • Locked
  • Question
Format Numbers in a String
Replies
4
Views
497
LauraW
LauraW
Brian S
  • Locked
  • Question
Splitting names that have variable numbers of spaces 1
Replies
5
Views
405
Brian S
Brian S
jchewsmith
  • Locked
  • Question
Report filter to run report yeartodate through yesterday
Replies
0
Views
303
jchewsmith
jchewsmith

Part and Inventory Search

Sponsor

Back
Top