Monday, January 25, 2010

Scripted Data sets in BIRT

Why/when do you need a Scripted data set in a BIRT report?
Existing tables in your database will not provide data in the way you need them in a report. You might have to query more than one table to get all the data, or you may want to do some computation which requires going through one table data more than once.

What scripting language can be used?
Javascript is the primary language to be used in BIRT. BIRT also uses the Mozilla Rhino JavaScript engine which provides excellent integration with Java, hence you can use Java libraries as well but with a Javascript like syntax.

How to do it?
- Create a global variable: declare the variable in the initialize event of the report
For example:
importPackage(Packages.java.util.*);
globalVarMap = new java.util.HashMap();
globalVarList = new java.util.ArrayList();

- Populate this variable with required data: Typically you would read a simple dataset and dump all the data in this global map with the primary key (or maybe some other value) of the table being the key of the hashmap. The necessary code goes in 'onFetch' event (method) of the dataset, and a table column is referred to as 'row["CUSTOMER_ID"]'. The onFetch event is called once per each table record, hence you get access to table data one record at a time.
For example:
var list = globalVarMap.get(row["CUSTOMER_ID"]);;
if(list == null) {
list = new java.util.ArrayList();
}

var data={};
data.customer_name = row["CUSTOMER_NAME"];
data.product_bought = row["PRODUCT_BOUGHT"];
data.price = row["PRICE"];

list.add(data);
globalVarMap.put(row["CUSTOMER_ID"], list);

Here the CUSTOMER_ID is not the primary key, and we are just sorting of various items bought by each customer.

- Create a scripted data set: The final step is to create a scripted data set with the required columns and use global variables like the one above to populate this data set. Since you have the necessary data in a bunch of global variables, you can do pretty much what you want. The necessary code goes in 'fetch' event (method) of the dataset.
if(customerCount < globalVarList.size()){
 var data = globalVarList.get(customerCount);

 row["CUSTOMER_ID"] = data.customer_id;
 row["CUSTOMER_NAME"] = data.customer_name;
 row["PRODUCT_ID"] =data.product_id;
 row["TOTAL_PURCHASE"] = data.total_purchase;
 row["QUARTER"] = data.quarter;
 row["COUNTRY"] = data.country;

 customerCount++;
 return true;
}

return false;

Here, we have the necessary data in a global list variable and we just have to populate the scripted data set. In the fetch method we return true to indicate that there is more data coming, and false to indicate that we are done.

Additional Info
BIRT/FAQ/Scripting
BIRT Java Reporting

10 comments:

  1. Hi Deepak,
    Try to understand your example. So, the 1st part of your code 'Populate this variable with required data' should to with the JDBC data set. And the 2nd part 'Create a scripted data set' should go with the script data set. Correct?

    With that done, drag a output column from the script data set to table to bind it. So far so good. My question is does BIRT know to perform the JDBC query 1st when I run the report? I am having trouble getting it to work.


    Thanks in advance,
    William

    ReplyDelete
  2. @William You should also have the JDBC data set bound to a table in a report page, but make this table invisible. And below this table you should create your visible table. BIRT evaluates whatever it sees in the report page. HTH.

    ReplyDelete
  3. i am new to BIRT report. i am using using RTC 3.0. Let me know is there any guide for developing script based report from the basic.

    ReplyDelete
  4. Hi, I am using 2.3.2 and I see this error ReferenceError: "globalVarMap" is not defined. (#1).. See next exception for more information.
    There are errors evaluating script "__bm_onFetch()":
    ReferenceError: "globalVarMap" is not defined. (#1).

    ReplyDelete
  5. There's a little error in your code.

    You declare globalvarMap, but then you call globalVarMap (notice the V is capitalized). That's why you get the undefined error.

    ReplyDelete
    Replies
    1. Thanks for pointing out the typo! I have fixed it now.

      Delete
    2. Hi Deepak,
      Since the JDBCDataSoruce.onFetch() will retrieve ALL records, and put them in the global variables, the ScriptedDataSource.fetch() should iterate on the globalVarMap first before accessing the globalVarList. However, the order of the data (globalVarMap.key) is not guaranteed.
      Is there a way to pause the onFetch() for every new CUSTOMER_ID and perform the fetch()?
      Thanks.

      Delete
  6. what is the data in globalVarList? u are trying to get this globalVarList but looks like its empty isn't it?
    i am also trying to achieve the same.But so far no luck.My values in the list are jumbled up.
    Please explain in detail bat fetching from the list.

    ReplyDelete
    Replies
    1. This was a while back so I don't remember the code exactly. I think my example code is a bit messed up. If you want to get data out of globalVarList you need to store data in globalVarList instead of globalVarMap.

      Delete
  7. Hi Deepak,
    It seems list is still empty even after changing to globalVarList. I am new to BIRT. Could you please provide a workable solution.

    ReplyDelete