Crystal Report number reformat

Posted on 2009-04-09
Last Modified: 2012-08-14
I have a crystal Report which displays a dollar amount from a database field (which is defined in the database as (NUMBER 19, 2).  Instead of displaying the field with edits, i need to display as and unedited field (no decimal point, no dollar sign) with leading zeroes.  I checked the normal field edits for a numerical field and you can remove the dollar sign, but can only replace the decimal with another character (I don't want any anything in the "decimal point" character.  Also, i don't see any way to place leading zeroes in the field.  I would assume that i need to use a formula to do this.
thank you
Question by:mbroad02
  • 2
LVL 100

Assisted Solution

mlmcc earned 100 total points
ID: 24111008
Yes you will need to use a formula

Something like this

Right('000000000000000' & Replace(CStr({YouNumberField},2,''),'.',''),15)

LVL 34

Accepted Solution

James0628 earned 400 total points
ID: 24113235
FWIW, you can remove the decimal point in a numeric field format.  Instead of typing a character in the box, click on the formula (X+2) button next to "Decimal Separator" and enter "" (two quotes with nothing between them).  That makes the decimal separator an empty string.

 But there is no way in the regular field format options to get leading zeroes.  For that, you'll need to convert the number to a string, as mlmcc suggested.

 FWIW, you can do it a bit more simply than his example.  For example, allowing for a total of 15 digits, including the 2 decimal places:

CStr ({table.field}, "0000000000000.00", 2, "", "")

 "0000000000000.00" specifies a format with leading zeroes, with 13 places to the left of the decimal and 2 to the right.
 2 just re-iterates that there are 2 decimal places.
 The first "" says that the thousands separator is an empty string.
 The second "" (and this is the tricky bit) says that the decimal separator is an empty string.  That overrides the "." shown in the field format.

 So, for example, for 123.45, you get 000000000012345

 Usually you do this kind of thing by creating a formula and putting the formula on the report instead of the numeric field.  One problem with that is that the formula produces a string, so you can't do totals on it and that kind of thing.  You can still do your totals on the original field, so it's not necessarily a big problem, but it can be inconvenient.

 Another option is to put the numeric field on the report and put the conversion formula in the field format.  Near the bottom of the Common tab of the field format, click on the formula button to the right of "Display String".  There you can enter a formula like the ones we've posted.  The difference is that you're putting the numeric field on the report and formatting it manually (using "Display String"), rather using a separate formula to convert the number to a string and then putting that string on the report.  So you can still do totals, etc. on that field on the report.  The down side is that you're doing custom formatting of the field, rather than using the regular formatting options.  If you forget, or someone else looks at the report and doesn't know about the "Display String", you may have some trouble figuring out where that field format is coming from.  :-)

 One other thing.  You said that the field is 19,2.  If the numbers ever actually get that big, you may have a problem.  I don't know about later versions, but CR 10 starts having precision problems when numbers get to around 15 or 16 digits.


Author Closing Comment

ID: 31568711
Thanks to mlmcc and james0628.  Very thorough and easy to understand.
LVL 34

Expert Comment

ID: 24120313
You're welcome.  Glad I could help.

 One other thing I didn't think to mention.

 If you're going to use the "display string" in the field format, in the conversion formula you can replace the field name with CurrentFieldValue.  The beauty of that is that it's a generic term for the value in whatever field you're formatting, so if you have several fields that you want to format the same way, or maybe some time later you want to format a field in a different report in the same way, if you use CurrentFieldValue in the conversion formula, you can just copy&paste that formula into the "display string" for the other fields.  It can save a lot of time.

 There are a number of places in a field format where you can use a formula and you can use CurrentFieldValue in many of them.


Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In a recent question ( here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question