Solved

Formatting currency to two decimal places using crystal reports 8.0

Posted on 2004-08-16
18
759 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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 …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

685 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