Solved

Crystal Report number reformat

Posted on 2009-04-09
4
1,760 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
[X]
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
4 Comments
 
LVL 101

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 35

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 35

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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. …
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 …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

729 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