Solved

Formatting currency to two decimal places using crystal reports 8.0

Posted on 2004-08-16
18
756 Views
Last Modified: 2012-05-05
Hello,
What is the formula used to  format currency so that $2 will display as $2.00?
Thanks
0
Comment
Question by:newaira
  • 8
  • 5
  • 4
  • +1
18 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 11814007
You don't need a formula - in the designer just format the field for two decimal places.

Rt-click on the field.  Select "format field".  On the 'number' tab click the Customize button, then for "decimals" select "1.00".

For most versions of Crystal by default there's a button on the toolbar that looks like ".00" with arrows beneath them - you can select a field then click these buttons as a shortcut to increase / decrease the decimal format.

HTH

frodoman
0
 

Expert Comment

by:w003rpb
ID: 11819707
All you need to do is right-click on the field and when the menu pops up, choose "Format Field."
Once you select the proper format, you can change the number of decimal places you wish to see.  
Hope this helps!
Ryan
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11819940
Ryan - That's exactly what I posted 17 hours earlier.  There's no reason to repeat what's already been posted... - frodoman
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Expert Comment

by:w003rpb
ID: 11819977
I apologize!  I did not realize you had posted that.  
Newaira- Please accept Frodman's comment.  It is a very good posting and I apologize for the duplication.
Ryan
0
 
LVL 1

Expert Comment

by:lalitgupta_99
ID: 11837916
Ho one morw way to do it in the formula field use
totext({value},'#####.##')

it will solve your purpose

thanks
0
 

Accepted Solution

by:
w003rpb earned 50 total points
ID: 11840439
Hello!  There is also a picture function that, which works just like the one above.  Just some examples for you:

Field value = 8939494949
picture(field, "(xxx)xxx-xxxx")      for a phone number (893)949-4949


or

454655
picture(field, "$xxx,xxx.00"              for currency $454,655.00

There are many other things you can do with this function.  It is much more flexible that just using totext.  HOpe this helps!
Ryan

0
 
LVL 42

Expert Comment

by:frodoman
ID: 11840486
Ryan and lalitgupta have both given some alternatives but you need to be careful with them because both will crash if you have a null value in the field for any reason.

Unless there's some reason to make this more complicated, just stick with formatting for 2 decimal places - this will give results needed, doesn't require additional formulas, and won't crash on null values.

{ Yes guys, I know you can add isNull to the formulas to handle these situations but I didn't see a need to complicate this further :-)  }

frodoman
0
 
LVL 3

Author Comment

by:newaira
ID: 11841891
Thank you all for your comments.

The field in question is a string, not an integer. So is it still possible to set it to two decimal places without having to change the types of the fields?

I just don't want to try something and mess up my working report.

0
 
LVL 42

Expert Comment

by:frodoman
ID: 11841916
Well it does need to be more complicated then.  Use one of the formulas proposed above, however, check it first for null or empty values:

if isNull({table.field}) then ""
else <whichever formula you want here>
0
 

Expert Comment

by:w003rpb
ID: 11841941
The picture formula will work just fine.  Try it out..>I am sure it will work just fine for your purpose.  The picture formula works with any field tpye.
Happy Reporting
0
 
LVL 3

Author Comment

by:newaira
ID: 11843625
What exactly is supposed to go in place of the ##'s or the XX's?

Because the field that has to have 2 decimal places has a value that is always changing?

0
 

Expert Comment

by:w003rpb
ID: 11843739
However long the field is should determine the number of "X" to use.  If you want to round to 2 decimals then it should look like this:

picture(field.field, "xxxxx.xx"

Hope this helps!
Ryan
0
 
LVL 3

Author Comment

by:newaira
ID: 11843988
Thanks for the reply.

I tried your method and now my field which used to turn out as $100.0 turns out to become $100..These numbers are representing currency values, so the lowest is $0.01 and the highest value will be $100.

This is the formula that I have written.

If isNull ({log.Denom}) then ""

else
picture({log.Denom}, "$xxx.xx")
0
 

Expert Comment

by:w003rpb
ID: 11844197
Looks good to me!  
Ryan
0
 
LVL 3

Author Comment

by:newaira
ID: 11844337
But then why does each currency have a period at the end. For example 1 cent would be $0.01. (notice the period at the end)
and $100.0 turns to $100.. (with two periods).

Sorry, I should have noted this in my previous post.
0
 

Expert Comment

by:w003rpb
ID: 11844521
Can you give me an example of what value you are given and what you want it to look like....I think I am losing track of what exactly you want?

In your orininal question, to get $2 to display as $2.00 you would do:
picture(field, "X.00")

Can you give me another example?  Are you working with currency?
0
 
LVL 3

Author Comment

by:newaira
ID: 11851103
Nevermind, I got it working. The reason why it was displaying like that was because the width of the field was too short. Thus it was chopping off that extra zero.
0
 

Expert Comment

by:w003rpb
ID: 11851150
I am glad I could be of help!  Have a great weekend!
Ryan
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

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 …
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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