Formatting a Number field, derived field?

[abowling]

I have a derived field to calculate the hourly rates for a group of employees who are paid semi-monthly. This derived field looks like this:

10.00
12.23
16.50
8.82 etc...

I need to format it to look like this:

000000001000
000000001223
000000001650
000000000882

Is there a derived field that could format this?

 

[dlblack]

You might try a combination of dividing by 100 and left pad (lpad) with zeroes to the final length desired. Something like lpad(yourfield/100,12,0)

David L. Black Consulting

 

[gmireles]

I had to format one of my derived fileds the same way, on my field name in the report I had to add this:

DEFERRAL_AMT \L9 \PL0

the deferral_amt is the name of my field
the \L9 is the lenght of the field
the \PL0 means populate with preceding zeros left aligned.

so my data comes out like this in text format
000108100

I ran my report and saved it in excel and it didn't look the same but it did work when I savedit to text. Hope this helps

 

[gmireles]

I forgot to tell you, if you want to change the way the data formats to example from 10.00 to 1000 then in the derived field you have to use a calculation like this:
sum(distinct "REPORTS"."V_YTD_INFO"."YTDGROSS$" * 100)

I did this for my gross amount, so if an employee made 3444.23, on my report it will appear as 344423.
hope this helps.