Solved

Crystal Report number reformat

Posted on 2009-04-09
4
1,704 Views
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.
Help.
thank you
0
Comment
Question by:mbroad02
  • 2
4 Comments
 
LVL 100

Assisted Solution

by:mlmcc
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)

mlmcc
0
 
LVL 34

Accepted Solution

by:
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.

 James
0
 

Author Closing Comment

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

Expert Comment

by:James0628
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.

 James
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now