# 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?
###### Who is Participating?

Commented:
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
)
+
(
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

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

Author 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

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

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

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

Author 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

Commented:
You're welcome.  Glad I could help.

James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.