Dynamic Numeric Formatting With Different Currency Code

Hi mmah,

What do you want the final value to look like?

mmah

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="65774" data-time="1277137515" data-date="21 June 2010 - 09:25 AM">
Hi mmah, 
 
What do you want the final value to look like? </blockquote>
 
Dear Michael, 
 
The final value would read as SGD 10,000.00 (the number is in standard currency format), with currency code placed first before the value. 
 
Thanks. 
 
Melvin

Melvin,

And you have many different currency codes in the table?

mmah

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="65863" data-time="1277308787" data-date="23 June 2010 - 08:59 AM">
Melvin, 
 
And you have many different currency codes in the table? </blockquote>
 
Michael, 
 
We have various currency codes, depending on how many currency records available. It would be around (in future) 20 plus. We're trying to accommodate all currency codes defined and referenced from another table. 
 
Melvin

Melvin,

I'm not sure exactly what the ? does for you. It looks like you can remove that. As far as the $, you're getting that because the standard currency symbol in Singapore is the $. If you don't want to see that, just remove the ?.

As for the format of the number, are all of your currencies going to have the same numerical setup ###,##0.00? Or are some currencies where there are 0 or 3 decimal places, etc?

Along with the answers to these questions, can you post a few different lines of what your data looks like and what you want it to look like in your table, so I have some data to work with in trying to set the correct format with different currency codes? Thanks.

Hi Michael,

We have a similar requirement to get the number format from the database table based on the user selection. The user can select the number of the decimals and the date format based on a list of formats in the data base table.

Can this be implemented in the report without using Java script? If the Java script is used, the format data from the interactivity on the web viewer is not working. Please let me know your thoughts.

Thanks
UY

Hi, Any suggestions?

Thanks
UY

Can you create a sample report that shows what you're doing? You can just use a static data Set for the codes for the parameters.

Sure, I will try to get a report in place with static data for the format and post it.

Thanks
UY

I'll keep an eye out for it!

Are either of these any help:
http://www.birt-exchange.org/org/devshare/designing-birt-reports/1439-birt-dynamic-number-formatting/
http://www.spudsoft.co.uk/2011/10/lsned47-birt-dynamic-number-formatting/

Written last year to answer a similar question.

Jim

Thanks, Jim!

<blockquote class='ipsBlockquote' data-author="'actuser9'" data-cid="110101" data-time="1349293563" data-date="03 October 2012 - 12:46 PM">
Thanks for the links. I have gone throught them earlier and was under the impression that it might work for my need. I was wondering if the number format could be dynamic in the below code. This ("###0.00{RoundingMode=HALF_UP}") needs to be dynamic format from data base. 
 
var currency = this.getRowData().getColumnValue('Currency');this.getStyle().numberFormat = currency + "###0.00{RoundingMode=HALF_UP}"; </blockquote>
 
Yes, you can make the number formats dynamic. 
In fact, by modifying the Data control in this way you can make pretty much anything dynamic. 
 
The little bit of script you've got there shows how you can pull a value from the data set and how you can modify one aspect of the style of the Data control. 
If your number format comes from another column it's just a case of pulling that out of the RowData and fitting it in to the number format. 
 
The only thing that may cause you pain is that you won't get any help from the tools - so you'll have to know all of the correct number format codes that you want to use and you will have to get them into your RowData. 
 
Jim

Hi Jim,

As you said, modifying the code to the below worked for the column. The data base format is applied on the column dynamically.

var date = this.getRowData().getColumnValue('DateFormat');
this.getStyle().dateFormat = date;

But, can you suggest on getting this implemented at the report level? I am sure this has to be done in the beforeFactory script. Tried several ways and nothing seemed to work. Appreciate all your help with this.

Regards
UY

<blockquote class='ipsBlockquote' data-author="'actuser9'" data-cid="110520" data-time="1350324067" data-date="15 October 2012 - 11:01 AM">
 
But, can you suggest on getting this implemented at the report level? I am sure this has to be done in the beforeFactory script. Tried several ways and nothing seemed to work. Appreciate all your help with this. 
 </blockquote>
 
I was suggesting changing the format in the onRender event for h Data control. 
You can't use any beforeFactory because they are only called once per control, you need something that is called for each row. 
 
Jim

Hi Jim,

My requirement is like this. I have a date format that is stored into the database tables as selected by the user from a front end application. Based on this user preferred date format, when the report is run, that particular date format needs to be applied on all the table columns available in the report. To do this, I was wondering if applying this format could be only possible on column by column basis. May be this can be doable using the beforeFactory code?

Any suggestions are welcome.

Thanks
UY

<blockquote class='ipsBlockquote' data-author="'actuser9'" data-cid="110528" data-time="1350329927" data-date="15 October 2012 - 12:38 PM">
Hi Jim, 
 
My requirement is like this. I have a date format that is stored into the database tables as selected by the user from a front end application. Based on this user preferred date format, when the report is run, that particular date format needs to be applied on all the table columns available in the report. To do this, I was wondering if applying this format could be only possible on column by column basis. May be this can be doable using the beforeFactory code? 
 
Any suggestions are welcome. 
 
Thanks 
UY </blockquote>

Oh, I see, you number format doesn't change per row, it jsut changes once for the run of the report.

In that case yes, you should be able to do it in the beforeFactory.

Does it fail if you try?

Jim

Yes, I am trying to implement this and nothing happens in the report. Not sure what is going wrong.

reportContext.getDesignHandle().findStyle("report").dateFormat = "mm/dd/yyyy";

Not much is going wrong

The problem is simply that BIRT has three separate date/time formats: dateFormat, dateTimeFormat & timeFormat - you need to make sure you set the right one. 
 
So this works: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
java.lang.System.out.println( "Format before: " + reportContext.getDesignHandle().findStyle("data").dateTimeFormat );

reportContext.getDesignHandle().findStyle("data").dateTimeFormatCategory = "Custom";
reportContext.getDesignHandle().findStyle("data").dateTimeFormat = "yyyy-MM-dd";

java.lang.System.out.println( "Format after: " + reportContext.getDesignHandle().findStyle("data").dateTimeFormat );
</pre>
 
I've attached a file containing that beforeFactory. 
 
Incidentally, if you want to see the output from java.lang.System.out.println be sure to run eclipsec, rather than eclipse. 
 
Jim

Jim,

Thanks for the report and sharing this information.

The report works like a charm. Now I am trying to get the format of the "DateFormat" column on the "data" style to make the report dynamic. This "DateFormat" column is hardcoded in the sample report but actually this will be populated from the database. The "DateFormat" column would be unique, like "MM/dd/yyyy" or "MM-dd-yyyy". This needs to be applied across all the date columns in the report.

I would need to capture the "DateFormat" from the data set and apply it to the entire report.

Any ideas?

Thanks
UY

Hi,

I tried creating a global variable for the dataset during oncreate event and use it in the beforefactory, does not seem to work.

Thanks
UY

It' going to be a bit awkward getting the format from another data set because BIRT doesn't want to ull the data until too late to be useful.
I aven't tred to see if there's a simple fx for this, but there are two options that would work if you can use them instead:
1. Get the data format outside the report and pass it in as a parameter.
2. Trick BIRT into getting the data set containing the format earlier, by attaching the data set to a parameter, see the blog for details: http://birtworld.blogspot.co.uk/2008/04/data-driven-reports.html

Jim

Thanks Jim.

Being a novice to java scripting, this looks like a brain teaser to me. But I will give my best shot to accomplish my requirement. Will post my results.

Regards
UY