Crystal Reports - Currency formatting for large amounts

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  :)
Gary DemosPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raghavendra HullurSoftware DeveloperCommented:

There are no such formats for displaying 1000's and millions as we can see under format options.
You have to achieve the same using formatting formuale with something like as below

if len(toText({number value},'#'))=7 then left(toText({number value},'#'),1)&'.'&mid(toText({number value},'#'),2,1)&'M'
else if len(toText({number value},'#'))=6 then left(toText({number value},'#'),3) & 'K'
else if len(toText({number value},'#'))=5 then left(toText({number value},'#'),2) & 'K'
else toText({number value},'#')

Replace the bold highlighted {number value} with your actual number field needing this formatting.

This will format number for 1000 and million formats and since you confirmed your number doesn't go to billions, that format is not included and also no formatting option for values less than 10K and the values will be displayed as it is.

P.S: Since the values are displayed in first 2 or 3 number values, all the remaining digits will not be considered for display.

Hope this helps you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary DemosPresidentAuthor Commented:
Thanks - worked perfectly the first time!
Gary DemosPresidentAuthor Commented:
How would I add the $ sign?
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Raghavendra HullurSoftware DeveloperCommented:

Use this.

if len(toText({number value},'#'))=7 then '$'&left(toText({number value},'#'),1)&'.'&mid(toText({number value},'#'),2,1)&'M'
else if len(toText({number value},'#'))=6 then '$'&left(toText({number value},'#'),3) & 'K'
else if len(toText({number value},'#'))=5 then '$'&left(toText({number value},'#'),2) & 'K'
else '$'&toText({number value},'#')
Gary DemosPresidentAuthor 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"
  if {your_field} >= 1000 then
    "$" + CStr ({your_field} / 1000, "#") + "K"
    "$" + 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.

Gary DemosPresidentAuthor 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},'#')
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.