Number Format

HI, 
 
I have one Data field (Decimal) where displaying Amount in Currency format like $1,234.50. 
 
But for my requirement If amount is more than 1 million i have to display in millions and have append the string in millions, if the amount is less than 1 million i have to display the whole number. In both cases number format should be same. The Data Field in Cross Tab. 
 
For Example: If amount is 20000 have to display like $20,000.00. 
If amount is 1000000 then have to dispaly like $1 (in Millions). 
 
As i have created Decimal Data Field it wont accept String value (in millions) to append. If I change the field to String I cant able to format the number its displaying number without format. 
 
Even I have tried with some java script on rendor of the value, but format is not happening. 
 
Could anyone help me to fix this issue. 
 
Thanks in Advance... 
 
Regards, 
Ram

Find more posts tagged with

Comments

birtprofi

What is if you have to display 1.234.567,00 ?

regards
rafael

legend.ram

<blockquote class='ipsBlockquote' data-author="'birtprofi'" data-cid="67254" data-time="1281526950" data-date="11 August 2010 - 04:42 AM">
What is if you have to display 1.234.567,00 ? 
 
regards 
rafael </blockquote>
 
As the amount is more then 1 million i have to display $1.23 (in millions) 
 
I am rounding to 2 decimals.

CalilJorge

<blockquote class='ipsBlockquote' data-author="'legend.ram'" data-cid="67256" data-time="1281527376" data-date="11 August 2010 - 04:49 AM">
As the amount is more then 1 million i have to display $1.23 (in millions) 
 
I am rounding to 2 decimals. </blockquote>
 
I haven't found a funcion to round to 2 decimals, but you can do some logic like this: 
 
if(amount >= 1 million) { 
amount = round(amount/10000) / 100; 
} 
 
return Format_in_$$(amount); 
 
I got it right? 
 
Regards, 
Calil.

birtprofi

yes this works

if
(dataSetRow["AMOUNT"] > '1000000') (dataSetRow["AMOUNT"]/'1000000')
else
dataSetRow["AMOUNT"]

best regards
rafael

legend.ram

Hi, 
 
My Problem is not logic, its format issue. 
 
Please look at the attachment. In first cross tab, I am achieving rounding and formate by a style. And dispalying ( in millions ) string, upon the condition if amount is greater than 1000000. But as I am creating report in .xls format, amount and ( in millions ) are coming in 2 different cells. Which not looking fine. I want complete thing in one cell only. 
 
In the 2nd Crosstab I created String Data Field, There I am adding Currency Symbol manually and for the amount, if amount is greater than 1000000 dividing the amount by 1000000 and at the end appending in millions string, if amount is less then 1000000 dispalying the amount as it is. But in this case format is issue, as it is a String field I cant apply fromat number style to achieved 1000 separator and automatic Currency symbol. One more problem is when i created the report in .xls, in the excel sheet one info is showing in the columns Number Stored as Text, even this info i don't want. 
 
 
Even I attached the created .xls sheet with 2 cross tabs, please refer that attachment for getting clear idea about my requirement. 
 
I am just trying for script on presenting the value (onRender) read the number and apply the style and convert into string and append the string in millions if amount is greater than 1000000. Please let me know is this possible, if yes please provide me one example or modify in the attached report. 
 
My BIRT runtime is 2.5.1 
 
Thanks for your support.... 
 
Regards, 
Ram

birtprofi

there is no attached file

legend.ram

<blockquote class='ipsBlockquote' data-author="'birtprofi'" data-cid="67301" data-time="1281592385" data-date="11 August 2010 - 10:53 PM">
there is no attached file </blockquote>
 
Sorry, Please find now.

birtprofi

try this one.

cross tab 1 should work. Now you have the currency symbol if the value is < 1000000 and you have mil if the value is > 1000000 (I worked with 2 data elements and the visibility and format number function of birt)

If you export in excel, all the values are numbers and not text fields

Could you work with this?

best regards
rafael

legend.ram

Thanks allot... As I am new to BIRT I dont know about custom formatting...