Solved

Formatting currency to two decimal places using crystal reports 8.0

Posted on 2004-08-16
18
747 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
 

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
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

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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. …
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

19 Experts available now in Live!

Get 1:1 Help Now