formatting and centralizing decimal point

Hi
I want to be able to format a number or string where the decimal points are always lined up.
Some will have a percentage sign and others will not.
e.g.
0.45%
123.45%
78.0
-0.45%
-0.4
So, in my report I would like to see all the decimal points aligned vertically.
e.g.
     0.45%
 123.45%
   78.0
    -0.45%
    -0.4
Is this possible?
PHIL SawyerAsked:
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.

James0628Commented:
Possible, yes.  Getting exactly what you need may be another matter.

 What are the rules?
 Are you simply trying to hide the second decimal place if it's 0, or is it something else?
 Could the values have more than 2 decimal places?  If so, do they need to be rounded to 2 decimal places?

 When do you show a "%"?

 Can you use a fixed-space font, instead of proportional?  With a fixed-space font, you can put a space where the second decimal place or "%" would be and things will still line up.  With a proportional font, that won't work as well, since a space doesn't take as much room as those other characters.

 Are you going to be exporting the report to an Excel file or anything like that?  The obvious solutions involve converting the values into one or more strings, which will look fine on the report, but may not give you what you want if the report is exported to Excel, for example.


 FWIW, assuming that your field is a number with only 2 decimal places (so no rounding is required) and you're just hiding the second decimal place if it's 0, and that you're using a fixed-space font, the following formula seems to work.  It produces a string, and when you put that formula on the report, you would use "Align Right" on the field.

(
if Right (CStr ({your field}, "0.00"), 1) = "0" then
  CStr ({your field}, "0.0") + " "
else
  CStr ({your field}, "0.00")
)
 +
(
if <your condition for showing a % symbol> then
  "%"
else
  " "
)


 Obviously you'd need to replace "<your condition for showing a % symbol>" with whatever test you use for that.

 James
0

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
mlmccCommented:
Are you dealing a single field?
Is it a numeric field that you are formatting?

Are you converting it to a string in some way?

If you don't plan to export to Excel and you have converted the value to a string with the % and right number of decimals then 2 formulas can be used to align the decimals

Formula 1
Split({@yourFormattedString},'.')[1] & '.'

Formula 2
Split({@yourFormattedString},'.')[2]

You can then put them on the report next to each other.  Formula 1 is right aligned.
Formula 2 is left aligned.

mlmcc
0
PHIL SawyerAuthor Commented:
It starts life as a numeric field - I guess we can only achieve what I want by converting it to a string?

Apologies - I should have been more concise....assume we have the following data fed into Crystal and then assume that if COL1 = "B" then no percentage sign else percentage sign.
e.g.
col1    col2
A           0.45
B       123.45
C          78.0
B         -0.45
E           -0.4


mlmcc - I need the one field as I want to use it in a crosstab using the mode function.

Regards
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

James0628Commented:
So, the field that you're trying to format is in a cross-tab?  I assume that it's a summary field (not a field that you're using for the rows or columns).

 Is COL1 a field, or are you referring to a column in the cross-tab?
 I was assuming that it was a field, but then there would be the question of how a field in individual records is related to a summary in the cross-tab (since each summary could include records with several different values in COL1).

 James
0
mlmccCommented:
In a cross tab you may have to try to use the Display String capability using the formulas James posted above to get the % and decimals correct

mlmcc
0
James0628Commented:
mlmcc,

 FWIW, I was going to suggest the same thing in my last post, but then started wondering about COL1.  If that's a field, I don't know if it will be possible to use it to control the display of "%" in a cross-tab summary field.

 James
0
PHIL SawyerAuthor Commented:
Hello
Just a note to say apologies for not responding earlier  - anyway,  you both have given me enough to sort my problem.
Many Thanks
0
James0628Commented:
You're welcome.  Glad I could help.

 James
0
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.