We help IT Professionals succeed at work.

Crystal Reports - Currency formatting for large amounts

Gary Demos
Gary Demos asked
on
114 Views
Last Modified: 2018-08-31
I wish to format currency in my report to make large amounts appear as following:
$1,200,000   would appear as $1.2M
$400,000 would appear as $400K
$10,000 would appear as 10K

My numbers do not get into billions  :)
Comment
Watch Question

Software Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Gary DemosPresident

Author

Commented:
Thanks - worked perfectly the first time!
Gary DemosPresident

Author

Commented:
How would I add the $ sign?
Raghavendra HullurSoftware Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Gary DemosPresident

Author

Commented:
Thanks!
CERTIFIED EXPERT

Commented:
I know that you've already accepted a solution, and I'm not looking for any points, but I wanted to ask a few questions (to check for potential problems), and offer an alternative solution.

 Do you only want to see decimal places on the millions?  For example, if you had 10,200, do you want to see 10K or 10.2K ?

 Do you ever want to see more than one decimal place?  For example, if you had 1,230,000, do you want to see 1.2M or 1.23M?

 Is rounding an issue?  For example, if you had something like 1,290,000 and only wanted one decimal place, do you want to see 1.2M or 1.3M?

 If the value is less than 1,000, do you need to see any decimal places?

 Assuming that you:
 Only want a decimal place on the millions
 Only want one decimal place
 Don't need to round the decimals
 Don't want any decimals on the values below 1,000

 you could also handle it with a formula like the following:

if {your_field} >= 1000000 then
  "$" + CStr ({your_field} / 1000000, "#.#") + "M"
else
  if {your_field} >= 1000 then
    "$" + CStr ({your_field} / 1000, "#") + "K"
  else
    "$" + CStr ({your_field}, "#")

Open in new window


 Oh, I assumed that you also wanted to see the thousands below 10,000 as nK (1K, 2K, etc.).  If not, the formula could easily be changed.

 James
Gary DemosPresident

Author

Commented:
Thanks James! - that seems to be a very effective way to handle this issue. I tried it and it works perfectly. I did have to make a couple edits to the previous answer as shown below, but it works also.

if len(toText({yourfield},'#'))=7 then "$" & left(toText({yourfield},'#'),1)&'.'&mid(toText({yourfield},'#'),2,1)&'M'
else if len(toText({yourfield},'#'))=6 then "$" &left(toText({yourfield},'#'),3) & 'K'
else if len(toText({yourfield},'#'))=5 then"$" & left(toText({yourfield},'#'),2) & 'K'
else "$" & toText({yourfield},'#')
CERTIFIED EXPERT

Commented:
Oh, yeah, Raghavendra's solution should work.  I just thought I'd post an alternative.  And, since you didn't post any details about your data, I was also concerned that there might be some issues that needed to be addressed, like the decimal places.

 James

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions