CRYSTAL REPORTS print full dollar field

mbroad02
mbroad02 used Ask the Experts™
on
I want to print a dollar field on a report with the following characteristics:

No dollar sign
No decimal point
print full size of field, up to 7 digits, including leading zeroes (FYI if field contains 56.71 I want it to print as 0005671)

I tried playing around with \the "format field" options, but could not get it to do these things.
Please advise
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
first change the field to text:

newfield = ToText(yourfield)
then replace decimal:
newfield =  replace (yourfield, ".",'')
Now add a formula:
if len(newstring) = 6 then "0"&newstring
else...

and so on

Author

Commented:
Not that I don't appreciate your response but isn't there a simpler way?
Commented:
Try the following function:

Function Pad (Value As Number, Comparison As Number, Padding As String) As String

dim SPACE_CHARACTER: SPACE_CHARACTER=ChrW(32)

If Value
Pad=Replace(Space(Len(CStr(Comparison,0))-Len(CStr(Value,0))), SPACE_CHARACTER, Padding) & CStr(Value,0)
Else
Pad=CStr(Value,0)
End If

End Function

Parameters
Value
value to be padded.
Comparison
value compared to this number.
Padding
character used to pad the value.
Return Value
A string.

source: http://www.cogniza.com/blog/?p=50
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Commented:
I forgot to mention, before you pass your field to this function, you need to replace the decimal i.e. multiply by 100.
There is no way to get leading zeroes using CR's standard number formatting options, so you'll have to use a formula of some sort.

 One option is to create a formula that converts the number to a string and put that on the report instead of the numeric field.  There are two basic downsides to this approach:
 1) You have to create a separate formula for each field that you want formatted like this.
 2) The field on the report (the formula) will be a string, so you can't do summaries on that field (eg. right-click and select Insert > Summary).  You can still do summaries on the original field.

 The other option is to use some tricks in the field format.  Again, there are two options here.

 A)
 One is to use the DisplayString option on the Common tab of the field format to convert the field to a string.  This is probably the simplest solution.  It avoids the two downsides to the separate formula solution above:
 1) If constructed properly, you can use the same formula to format each numeric field that should have the same format (7 digits, with no decimal and leading zeroes).
 2) The field on the report is still numeric, so it can be summarized.  The format converts the field to a string for output, but the value behind the format is still the original numeric value.

 In the field format, go to the Common tab and click on the formula (X+2) button to the right of DisplayString near the bottom, then enter the following formula:

CStr (CurrentFieldValue * 100, "0000000")


 CurrentFieldValue is a generic term that can be used in formatting formulas to refer to the value in that field.  So, that same formula can be used to format any numeric field that you want formatted that way.  In fact, you can even select multiple numeric fields, select "Format Objects" and enter that formula for all of them at the same time.

 Note that when you use DisplayString, that formula completely replaces the regular numeric formatting options.  In fact, the Number tab will actually disappear from the format window.  If you delete or comment out the DisplayString formula, the Number tab will return.


 B)
 The other way to get the format you want using the field format is to customize the number format options to remove the decimal point and use the currency symbol to display your leading zeroes.  I can provide details if you like, but the DisplayString is really a lot simpler, so I decided not to confuse things by posting these other details, unless someone really wants to see them.

 James

Author

Commented:
While I appreciate the dhobale answers, james0628's answer was perfect.  Worked great and was simple!
Thanks
I assume that you're referring to the DisplayString option.  It can really simplify things.  The only real issue I have with it is that it's something I don't use often, so if I come back and look at a field later that used DisplayString, I may have some trouble figuring out how I formatted that field.  :-)  The fact that a supposedly numeric field doesn't have a Number tab in the format window could be particularly confusing.

 Anyway ...

 You're welcome.  Glad I could help.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial