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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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…

821 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