Keep currency format in Excel document

Hello,

I created a report with one table containing amounts associated with several currencies like this :

amount

125,40 ?
456,00 $
457,60 ?
600,56 CHF

I figured out how to generate an Excel report from the designer but I did not manage to keep the Excel currency format in the amount column.
I tried to use the Format Number property "Format as Currency" but it seems that we can only use one currency at a time.

Any help is appreciated.
Thx

Find more posts tagged with

Comments

mwilliams

How many different types of currency do you have? Have you tried setting the format in script?

Ludmy

Each row can have a specific currency. 
I tried to set the format in script using the onRender method of my table cell 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var format = new Packages.java.text.DecimalFormat("####.00 $");
this.setDisplayValue(format.format(this.getValue()));
</pre>
 
I also tried to use a Text Item : 
<pre class='_prettyXprint _lang-auto _linenums:0'>
<VALUE-OF>
df = new Packages.java.text.DecimalFormat("#,###.00" +</VALUE-OF>
]]);
df.format(row["valeur"]);
</VALUE-OF>
</pre>
 
The probleme is that the amount is considered as text in the excel file.

mwilliams

Ah, so it's more of a problem with the emitter, rather than the formatting. Have you tried one of the other available emitters for XLS? They might treat this differently.

http://www.birt-exchange.org/org/devshare/designing-birt-reports/1421-native-excel-emitter-plugin-for-birt/

There's also another in the BIRT-Exchange Marketplace (it's free).

Yaytay

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="84655" data-time="1319816533" data-date="28 October 2011 - 08:42 AM">
Ah, so it's more of a problem with the emitter, rather than the formatting. Have you tried one of the other available emitters for XLS? They might treat this differently. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1421-native-excel-emitter-plugin-for-birt/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/1421-native-excel-emitter-plugin-for-birt/</a> 
 
There's also another in the BIRT-Exchange Marketplace (it's free). </blockquote>
 
As the author of <a class='bbc_url' href='http://www.birt-exchange.com/be/marketplace/app-showcase/?app=102'>the other</a> I think mine would convert it to text (or lose the currency symbol) at the moment, but if you attach a self-contained report that demonstrates the problem I'll see if I can make it work for you. 
 
Jim

Yaytay

After doing some investigations I'd say it's not really a problem with the emitter, it's a problem with how you customise the the number format. 
Your approaches are effectively converting the number into a string when the report is run, whereas what you really want is to keep them in a numeric Data element until the emitter handles them. 
 
I've attached a report that demonstrates a way to do this, and wrote a <a class='bbc_url' href='http://www.spudsoft.co.uk/2011/10/lsned47-birt-dynamic-number-formatting/'>blog post</a> on it. 
With that report I'm able to output the values as correctly formatted currency values that Excel can still manipulate numerically. 
 
I only did a little bit of testing with the default emitter but I think it wored with that. It didn't work with my own emitter, but I'll be uploading the fix for that in the next few minutes. 
 
Hope this helps. 
 
Jim

mwilliams

Thanks for the follow up, Jim!

Ludmy

It works fine, thanks