Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Crystal Report number reformat

Posted on 2009-04-09
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 101

Assisted Solution

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

Something like this

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

LVL 35

Accepted Solution

James0628 earned 1600 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 35

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

597 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