Number Formatting Query

[PMRiley]

CRXI/SQL Server

I have a report that is very tight for space (Needs to fit on A4 Landscape) which has 4 columns in it that need to show a number of days.

Some of the day calculations result in 1/2 days which need to be shown in the results.

To make the report as readable as possible, I need these columns to display as

2 (for 2 whole days)
1.5 (for 1 and 1/2 days)
.5 (for 1/2 day)

instead of the standard 2.0, 1.5, 0.5

Any suggestions?

 

[Jww193]

One way to do this is to create a formula and in the formula do something like the following:

stringvar SomeText;
numbervar yourday:=2.3;

SomeText:=toText(yourday,"0.0"); //or some other format...

if mid(SomeText,3,1)="." then
left(SomeText,1)
else if left(SomeText,1)="0" then
mid(SomeText,2,2)
else
SomeText;

 

[GJParker]

One way to accomplish this is to display the values as a string rather than a number.

Right mouse click on your field -> Format Field -> Common tab -> and add a formula for the display string (x-2) button

Local StringVar Integer := split(totext(CurrentFieldValue),'.')[1];
Local StringVar Decimal := split(totext(CurrentFieldValue),'.')[2];

If Decimal = '00' Then
    Integer
Else If Integer = '0' Then
    '.' & Decimal
Else
    Integer & '.' & Decimal

This should do the trick

Gary Parker
MIS Data Analyst
Manchester, England

 

[PMRiley]

Thank you very much to both of you!

I went with Gary's method in the end (More because this report is for other people to run and if there are formula's they will only mess around with them!!!)

Just one minor comment if anyone else is looking for the same output - the 1st 2 lines of the formula needed amending as below to force the ToText command to allow for 1 decimal place...

Local StringVar Integer := split(totext({@NoOfDays},1),'.')[1];
Local StringVar Decimal := split(totext({@NoOfDays},1),'.')[2];

Paul (Also in Manchester UK!)