How to customize Number Format using ToTEXT

[#1]

Dear SAP Team ,

I am Using Formula  trim(totext(sum({command.limit6},{command.currency_code}),'########0.000')) ,

where ToTEXT value is change dynamically depends up on COUNTRY , for EX dubai location we pass value like '########0.000'  ,  for india '########0.00' like this , and also we have multiple location , where to text value will change dynamically from DB Depends up on currency code  else we provided option in our package where users can change  number format for thier own .

My queries is when it is negative value i am getting space between number and minus symbol ( -     5994.270 ) ,  but i need like this -5994.270 .

To understand clearly , please check my Image file .

[Former Member · Accepted solution]

Hi Sriram

The # symbol is reserving space for the number of digits. Hence the - symbol is shown with spaces when there are no digits to replace the space.

Use the below code:

If sum({command.limit6},{command.currency_code}) <0 then

     "-" & trim(totext(abs(sum({command.limit6},{command.currency_code})),'########0.000'))

Else

     trim(totext(abs(sum({command.limit6},{command.currency_code})),'########0.000'))

Let me know if this helps.

Regards

Nikhil Sabnis

[Former Member · Accepted solution]

Hi Nikhil ,

Its working fine , but could you  please explain me how  ( "-" ) its working .

Now i am getting Exactly  -5994.20 , but  is it possible to show negative value like this (5994.20).

if it is a number format then we can change in format object option , but now we are converting to TOTEXT object

"-" & trim(totext(abs(sum({command.limit6},{command.currency_code})),'########0.000'))

[Former Member · Accepted solution]

Hi Sriram

You have given the number format for the totext function as '########0.000'. There are 8 hashesbefore 0.000. Crystal will show 8 spaces (1 for each hash) if there isn't any corresponding digit. That means if you have number like -123456780.000 then the negative sign will be shown exactly how you want. However if you have a number like -12345670.000 then it will be shown as "- 12345670.000" since it does not have the corresponding 8th digit before '0.000'.

Regarding "(" and ")":

Since the totext function is used the number is converted to a string format. Hence you will have to explicitly include the paranthesis. Check the formula below:

If sum({command.limit6},{command.currency_code}) <0 then

     "(" & trim(totext(abs(sum({command.limit6},{command.currency_code})),'########0.000')) & ")"

Else

     trim(totext(abs(sum({command.limit6},{command.currency_code})),'########0.000'))

Regards

Nikhil Sabnis

[Former Member · Accepted solution]

Thanks Nikhil ,

I understand about the Reserve space , but how the paranthesis(&) symbol is reducing space ?.

Anyway i don't like to  disturb you continuously , thanks for your solutions . 

[former_member11696]

Moved to Report Design forum

[Former Member]

Hi Don ,

I am using  in built sap crystal of visual studio 2010 . That's why  placed my query here ,

How to move Report Design Forum .........

[former_member11696]

I moved it, it still is a report design question and the built in designer is almost the same, lots of options and feature not available but basic like this are.

Don

[Former Member]

Hi sriram,

             Replace empty space between - and text.Try

if sum({command.limit6}<0 then

Replace(totext(sum({command.limit6},{command.currency_code}),'########0.000')," ","")

else totext(sum({command.limit6},{command.currency_code}),'########0.000')

[Former Member]

Hi Jaheer ,

I tried exactly what you said , but still i am getting space between minus symbol and value . any other alternate solution is there .

[former_member11696]

This may be a font issue, it could be the font itself is inserting the space. Right click on the field/formula and change the font to Courier New and see if that fixes the issue? It's fixed width font and will indicate if this is a font issue or not.

I never tried but you can do a quicker test...

Also, try replacing the characters in the formula with something else so you can verify the characters are being replaced properly.

Don

[Former Member]

HI DON ,

I tried with Different font size (courier new) , but still am getting space between minus  symbol and value , But i got solution with the help of Nikhil Sabnis  and now its working fine Williams ,

Thanks for your reply .