BIRT Best Practice

BIRT Best Practice

 

Abstract:

This document is meant as a documentation of the work done during my Internship using BIRT. It is focused on a set of useful examples for the work with BIRT. This document is not a substitute of a proper BIRT tutorial, but is meant as a compliment to get to know some useful techniques.

The following was written down rather quickly and was originally meant for myself. So if you find any errors or other shortcoming, please email me:

evgeny dot sitnikov at gmail.com

 

Table of Contents:

1. Introduction: 

2. Installing BIRT              

4. Birt WEB Viewer              

4.1 Installing BIRT Web Viewer              

4.2 Short Guide              

5. Automatic generation of BIRT reports              

6.1Potential errors:              

7. Integration of HTML/JavaScript into the Report Layout: 

7.1 How to do it              

7.2 <VALUE-OF> Tag              

8. HTML/JS and Property Binding   

8.1 Use Case              

9. Scripting              

9.1 Example:              

9.2 Highlighting with Scripts:              

9.3 Visibility with scripts:              

10. BIRT internal logging:              

11. BIRT localization:              

12. Google Maps Integration for BIRT:              

13. BIRT Scripted DataSet              

14. BIRT – Work with Parameters programmatically              

15. Information sources:              

15.1 Useful websites for specific topics:              

1. Introduction:

BIRT stands for Business Intelligence and Reporting Tool. It is an easy to use, open source tool which can be integrated as a plug-in in the Eclipse IDE. The main advantage of BIRT over other open-source or proprietary tools can be seen in the possibility to interact with Java and JavaScript. This opens up an amount of flexibility which is just limited by the technical possibilities of Java/JS and the developers creativity. So flexibility is the key value of BIRT.

 

2. Installing BIRT

In order to install BIRT you have two possibilities.

You can just include the BIRT plug-in in your Eclipse environment.  Use your update manger in Eclipse for that: http://wiki.eclipse.org/BIRT_Update_Site_URL.

 

The other option is to download the all-in-one package from the official BIRT Project website: http://download.eclipse.org/birt/downloads/

The all-in-one version contains an instance of Eclipse and the BIRT plug-in. This is the easiest way to get started with BIRT.

  

3. Getting an idea about BIRT:

To gain a first idea about BIRT, here is a screen shot:
 

Figure 1: Eclipse - BIRT Report Design View

Here we see the 5 most important components of the graphical interface of BIRT:

1. Palette - contains all structure elements for the layout of a report (tables, grids, image place holders, text, list, ...)
2. Outline – elements of a report (data sources, data sets, parameters, layout elements, scripts, ...)
3. Report Layout - here you can modify the structure and the layout of the report, it gives you an idea about the way the report will look like (its not a WYSIWYG, since the data needs to be added).
4. Property Editor - every element in a report has certain properties, beginning with the colour and size to more complex like expressions and event handlers.
5. View Report Button - Executes Report in the integrated Web Viewer or inside of you browser if chosen.

3.1 Getting Data into your Report:

Obviously our main interest is to represent data; therefore we need to retrieve it.
BIRT distinguishes between Data Sources and Data Sets.

A Data Source in BIRT can be regarded as a connection to a certain Data Source. By creating a Data Source we make sure that we can retrieve data from it.
BIRT supports the following Data Source Types:
    - Flat File Data Source
    - JDBC Data Source
    - Scripted Data Source
    - Web Service Data Source
    - XML Data Source
 

After establishing a connection to a Data Source, a Data Set can be created.

A Data Set can be seen as a query on the Data Source. The user defines the columns which should be retrieved: 

Figure 2: Sample Data Set creation Example

 

With these columns a simple report can be build:

Figure 3: Sample Data Set with Table

4. Birt WEB Viewer

4.1 Installing BIRT Web Viewer

In order to make you report run on tomcat, follow the steps described here: http://www.eclipse.org/birt/phoenix/deploy/viewerSetup.php, in the following an extract from the eclipse website:

 

---

Deploy the BIRT Viewer application. Follow these steps:

• Download the zip file with the BIRT report engine runtime. The file is named birt-runtime-version#.zip.
• Unzip the file in a staging area.
•  Look under the birt-runtime- directory and locate the "Web Viewer Example" directory.
•  Copy the Web Viewer Example directory to the webapps directory of your Tomcat installation. For ease of reference, rename the directory to "birt-viewer".
• Stop, then restart Tomcat.
•  Display the Tomcat manager application to check that the viewer is deployed:http://localhost:8080/manager/html.
•  Verify that birt-viewer is listed as an application, then click on the birt-viewer link.
•  A page confirming that the BIRT viewer has been installed should be displayed. Click on the link labeled "View Example" to confirm that your installation is working properly.
• The BIRT Viewer requires that cookies be enabled.

If you choose to put the Viewer into some other location, you'll need to use a context entry within the server.xml file to indicate the deployment location. See Tomcat documentation for details.

Install your JDBC Drivers

Add the jar files for your JDBC drivers to the Viewer. Copy the driver the following directory:

Birt 2.1/2.2 Note:If you are installing BIRT 2.1 or later the driver needs to be copied to birt-viewer\WEB-INF\platform\plugins\org.eclipse.birt.report.data.oda.jdbc\drivers.

---

 

 

4.2 Short Guide

A detailed guide on the usage of the Birt Viewer can be found here:

http://www.eclipse.org/birt/phoenix/deploy/viewerUsage2.2.php

 

In the following some of the most important points;

 

Running a report:

In order to run your report, put it into the Birt Viewer and run the following URL:

http://localhost:8080/birtViewer/frameset?__report=TestReport.rptdesign

 

URL parameters for the Birt Viewer

The most important parameters to include in the url:

__title – set the title of the window

__showtitle – if the Birt Viewer bar should show the title or not

__toolbar – show Birt Viewer Toolbar or not

__navigationbar – show Birt Viewer Navigation Bar or not

 

Example:

http://localhost:8080/birtViewer/frameset?__report=TestReport.rptdesign&__title=My Birt Viewer&__showtitle=false&__toolbar=true&__navigationbar=false

 

5. Automatic generation of BIRT reports

 

The following is a copy from the following blog: http://eclipser-blog.blogspot.com/2008/02/automatic-generation-of-birt-reports.html

 

---

Automatic generation of BIRT reports is a common task for every company that has reports that should be presented to the customers.

Those reports should be delivered regularly. If howto that I am going to present is available somewhere than I am sorry, but I was not able to find it out and I had to merge knowledgle scattered in a several wiki pages.

But in the ends solution seems to be very easy:

1. Download BIRT runtime and unzip it somewhere. You should have now birt-runtime-2_2_1_1 folder.
2. Set up environment variable to point to that folder either in OS or inside birt-runtime-2_2_1_1\ReportEngine\genReport.bat(sh for linux). 
3. Put your jdbc jar file in folder birt-runtime-2_2_1_1\ReportEngine\plugins\org.eclipse.birt.report.data.oda.jdbc_2.2.1.r22x_v20070919\drivers.
4.  Execute genReport.bat script to see usage. For all those good computer scientists (and good computer scientist is a lazy one) I present sample command: genReport.bat -f PDF -o outputfile.pdf -F report_design.rptdesign

---

6. Data Set

The Data Set has the following options:

• Data Source: Set the Data Source
• Query: Write the SQL query (put “?” to replace flexible parameters)
• Output Column: Shows the columns which have been specified in the query, the Alias feature is handy to rename a column (in order to not retype a long name like COUNT(DISTINCT(ORDERS.ORDER_ID)) every time)
• Computed Column: To calculate a new column out of the ones recognized in “Output Column”
• Parameters: Specify parameters here, the order of the parameters here must be according to the “?” placeholders specified in the query
• Filters: Filter the query results
• Property Binding: Adapt the query, see below
• Settings:
• Preview Results: Self-explaining

6.1Potential errors:

In case the preview is not updated: Change slightly the query (add a space, parentheses).

If there are errors like ”No such column specified”. Empty the query, run the preview, then fill in the same query again. Take care to set the Aliases / Computed Columns back to the previous values.

6.2 Property Binding

Data Source and Data Set creation have a set of highly interesting options one of them is the Property Binding.

With the help of this concept it is possible to make a Data Source and Data Set configuration dependent on the parameters entered. BIRT provides an ExpressionBuilder for this purpose, a possibility to script the desired behavior in JavaScript.

Below an example:

Figure 4: Property Binding and Expression Builder 

 

7. Integration of HTML/JS into the Report Layout:

BIRT has the possibility to integrate HTML on top of the Report Layout. This can be a very powerful feature, when it comes to interaction between the user and the report. Interactive elements like drop-down lists, check boxes, radio boxes aso. can be included and used in order to set some parameters in a more intuitive way, without the usage of the less user-friendly “Parameter” box in the report taskbar. This feature is especially interesting in interaction with the Property Binding functionality above.

7.1 How to do it

Insert a “Text” element from the Palette into your Report design. In the drop down menu change “Auto” to “HTML”. Now you can insert a HTML form.

Figure 5: Text Item for HTML usage

 

7.2 <VALUE-OF> Tag

BIRT has a very useful tag to integrate report values into a script. The <VALUE-OF> tag allows reading report parameters inside a script:

<VALUE-OF>params["from"].value</VALUE-OF>

This line returns the value of the parameter “from”, so that it can be read by the script editor.

 

8. HTML/JS and Property Binding

8.1 Use Case

With BIRT it is possible to introduce some interaction into the report. In the following an example with Check Boxes, the result will be like this:

 

Figure 6: Simple Data Set, Check Boxes Example

 

The user can choose between three different categories, here “Premium”, “Regular” and “Basic”. He can check one, two or all of the check boxes then click reload. Dependent on his choice the query is chosen in the Property Binding and a respective result is retrieved.

In the following the HTLM code:

---

<form>

 

<input type="checkbox" name="checkboxes" id="cb1" value="premium" CHECKED >Premium</input></br>

<input type="checkbox" name="checkboxes" id="cb2" value="regular" CHECKED >Regular</input></br>

<input type="checkbox" name="checkboxes" id="cb3" value="basic" CHECKED >Basic</input></br>

<input type="button" onclick="createOrder()" value="Reload Report">

 

<script>

function createOrder() {

checkboxes=document.forms[0].checkboxes;

if(checkboxes[0].checked){ pPremium = true ;

}else{ pPremium = false;}

if( checkboxes[1].checked){pRegular = true;

}else{ pRegular = false; }

if(checkboxes[2].checked){ pBasic = true;

}else{ pBasic = false; }

//set the pChoice value for the parameter queryParam

pChoice ='';

if(pPremium && !pRegular && !pBasic ){

              pChoice='F';

}

...

if(!pPremium && !pRegular && !pBasic ){

              pChoice='NNN';

}

//Build url and call it

var url = 'http://localhost:8080/birtViewer/frameset?__report=MyReport.rptdesign&__format=html&__title=My Report Viewer&__showtitle=false&queryParam='+pChoice+'&from=<VALUE-OF>params["from"].value</VALUE-OF>&to=<VALUE-OF>params["to"].value</VALUE-OF>';

window.location = url;

}

</script>

</form>

---

The code above is an HTML form, which includes a JavaScript part. First we create a code for the three check boxes and a button as an input.

When the button is clicked the createOrder() function is called.

Inside the function we save the state of the three checkboxes and dependent on that we set the pChoice variable.

With pChoice we build the URL to call in order to customize the report according to the users choice.

The report is reloaded with the new parameter. Dependent on the parameter chosen by the user a different Data Set is used. Here the Property Binding functionality comes in place.

The property binding looks like this:

---

if(params["queryParam"].value == "F"){

"select TO_char(order.departure, 'YYYY-MM'), pkl.pkl_type,count(distinct(order.order_id)) from orders.pkl pkl, test2.database order where pkl.pkl_order_id = order.order_id and  (order.MRD_FIRST = 'Y') and order.departure between ? and ? GROUP BY TO_char(order.departure, 'YYYY-MM'),  pkl.pkl_type  ORDER BY TO_char(order.departure, 'YYYY-MM'), pkl.pkl_type "

}else{

...

if(params["queryParam"].value == "NNN"){

" … ";

}

}

---

 

9. Scripting

Nearly every element in BIRT has events with an integrated scripting possibility. In order to use it click on a report object (tables, datasets, …) and then go to the “Script” Tab underneath the Report Layout window:

Figure 7: Scripting Possibility of a BIRT Object

 

The Script drop down list shows you the different stages of the report creation during which the script can be executed.

An overview over all Scripts used in a report is accessible through the Scripts Button in the ”Outline” View:

Figure 8: Overview over all scripts used

 

9.1 Example:

A simple example is the counting of the lines of a table.

Add “Text” Element into the first column of a table. Choose “HTML” in the drop-down list and enter  <VALUE-OF>i</VALUE-OF>. : 

Figure 9: <VALUE-OF> Tag example

 

Now mark the row of the table where the counting should be done and enter the following scripts:

• onPrepare: i=0;         
• onCreate: i++;

The onPrepare script is executed before the row is filled with data. The onCreate script is executed immediately before every single row is filled with data.

9.2 Highlighting with Scripts:

BIRT has a very powerful highlighting possibility which can be accessed through the Highlights tab of the Property Editor. However this is sometimes not enough and some scripting is needed in order to achieve a more powerful highlighting rule.

9.2.1 Example

We want to achieve that every second group of a table is highlighted. Since we group the table by the first column, and we wouldn’t like to repeat the same value of the first column in every row, we create a highlight rule which changes every time the group value changes.

On the group level we set:

• onPrepare: i=0;
• onCreate: i++;

On the row level we set:

• onCreate:    if (i%2 == 1 ){ 
                this.getStyle().backgroundColor = "RGB(242,236,222)";

          }

This will set the background color of every second group to the indicated value.

 

9.3 Visibility with scripts:

The same functionality as the highlighting with scripts can be used for the visibility of some elements.

Some code can be set in order to show or hide one cell, line or table. Got to Visibility in the Property tab and enter a script like:

if (i == 0){ false } else{ true }

You can also just enter a boolean variable, which is set somewhere else in the script.

 

10. BIRT internal logging:

With the help of the Events and a Java class an easy logger for BIRT can be realized.

Create a class which will receive the logging messages:

---

import java.io.BufferedWriter;

import java.io.FileWriter;

import java.io.IOException;

 

//The Class is there to log what happens inside of a Birt report.

public class BirtLogger {

              public static void log(String comment) throws IOException {

FileWriter log = new FileWriter("c:/Temp/text7.txt",      true);

                                          BufferedWriter out = new BufferedWriter((log));

                                          out.write("\n"+comment);

                                          out.close();

                  }

}

---

Now go to the Outline view of your report and click on the name of the report design and go to the Script Tab. In initialize event type: 

---

importPackage(Packages.com.company.birt.io);

BirtLogger.log("Initialize Report");

---

  

This command imports all Classes from the package com.company.birt.io. Then we call the log method of the BirtLogger class and pass a String to it. This command can be included in any other event to log the current events in BIRT.

  

11. BIRT localization:

 

BIRT has the possibility to localize the report. In order to change the language of the Report Viewer, just change the URL parameter __local to for example: __locale=de_DE.

In order to change languages of the report itself you need to work with property files.

 

Create the properties files in the languages needed, the names should be like this:

names_en.properties

names_de.properties

names_fr.properties

 

Example of a property file for English, German and French:

names_en.properties:

first_name=First Name

last_name=Last Name

names_fr.properties:

first_name=Prénom

last_name=Nom de Famille

names_de.properties:

first_name=Vorname

last_name=Nachname 

  

 

Then click on the report design name in the Outline view and go to the Resources section. Type the name of your properties files, in the example above it would be just “names”.

 

Now, in every case, when you want to localize an element, click on that element, go to the Localization section, choose Browse and choose the property which you want to give to this element.

 

 

12. Google Maps Integration for BIRT:

 

Since we have a possibility to integrate HTML code into BIRT, it is also possible to add Google Maps to a report.

 

First of all you need to generate a key for Google Maps, which can be done on the Google Maps website.

 

In BIRT create a Text Element and set it to HTML. Then add the following code:

 

---

<iframe src ="googlemap.html?Longitude=<VALUE-OF>params["longitude"]</VALUE-OF>&Latitude=<VALUE-OF>params["latitude"]</VALUE-OF>" width="465" height="320">

  <p>Your browser does not support iframes.</p>

</iframe>

---

This piece of code calls the googlemaps.html file and passes it some parameters from the report.

 

The file googlemap.html looks like this:

 

---

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

  <head>

    <script src="http://maps.google.com/maps?file=api&v=1

            &key=ABQIAAAAftwXx2hMOePGP2BE2_5RNRT2yXp_ZAY8_ufC3CFXhHIE1NvwkxT-zgYL1zP-iujbpK-MPXdMZlTEzw" type="text/javascript">

    </script>

  </head>

  <body>

    <div id="map" style="width: 400px; height: 300px"></div>

    <script type="text/javascript">

    var map = new GMap(document.getElementById("map"));

    map.centerAndZoom(new GPoint(-83.022206, 39.998264), 3);

    </script>

  </body>

</html>

---

Now, you can extend this concept according to you wishes; have a look at the very powerful Google Maps API (http://code.google.com/apis/maps/documentation/index.html). A very good tutorial for Google Maps can be found here: http://econym.org.uk/gmap/ .

 

13. BIRT Scripted DataSet

BIRT has a number of Data Source options, such as JDBC, XML and Web Services, however this choice might be not enough if one wants to retrieve data with some extra options or wants conduct some calculations outside of BIRT (a reason might be that BIRT can be quite slow on that and you can use another resource for such a task). BIRT has the possibility to use a Scripted Data Source. In the following an example to use a Java Data Source:

---

import java.util.ArrayList;

import org.eclipse.birt.report.engine.api.script.IUpdatableDataSetRow;

import org.eclipse.birt.report.engine.api.script.ScriptException;

import org.eclipse.birt.report.engine.api.script.eventadapter. ScriptedDataSetEventAdapter;

import org.eclipse.birt.report.engine.api.script.instance.IDataSetInstance;

 

public class TestDataSet extends ScriptedDataSetEventAdapter {

              int rowNum = 0;

              boolean firstFetch=true;

              @Override

public boolean fetch(IDataSetInstance dataSet, IUpdatableDataSetRow row)  {

      if(firstFetch){

            rowNum=0;

            firstFetch=false;

      }             

      if (rowNum <10) {

          try {

              row.setColumnValue("orderCode", rowNum*1);

              row.setColumnValue("firstName", rowNum*2);

              row.setColumnValue("lastName",rowNum*3);

              row.setColumnValue("type", rowNum*4);                                                                 

              row.setColumnValue("comments",rowNum*5);                                                        

              row.setColumnValue("departure",rowNum*6);                                          

              row.setColumnValue("arrival", rowNum*7);

              row.setColumnValue("from", rowNum*8);

              row.setColumnValue("to",rowNum*9);

              row.setColumnValue("customerID", rowNum*10);             

           } catch (ScriptException e) {

                 e.printStackTrace();

           }

           rowNum++;

           return true;

       }else{

           return false;

       }

}

@Override

public void open(IDataSetInstance dataSet) {

}             

}

---

In order to create a scripted Data Source follow the these steps:

1.      Create a Data Source which uses Scripted Data Source:

2.      Create a Data Set which uses the Scripted Data Source and type in the Columns, which you would like to use:

3.      Click on the Scripted Data Set and go to the Event Handler section, there browse for the Scripted Data Set class:

14. BIRT – Work with Parameters programmatically

In order to communicate between the BIRT report parameters and a scripted data set, the following concept is to apply.

Create a java class, which will do the parameter management and add it to the EventHandler of your report (click on report name in the Outline view and go to Event Handler).

 The class can look like this:

---

import org.eclipse.birt.report.engine.api.script.IReportContext;

import org.eclipse.birt.report.engine.api.script.ScriptException;

import org.eclipse.birt.report.engine.api.script.eventadapter.ReportEventAdapter;

 

public class ParamSetter extends ReportEventAdapter {

 

     static Logger logger = Logger.getLogger(ParamSetter.class);

     @Override

     public void initialize(IReportContext reportContext) {

         try {

             super.initialize(reportContext);

         } catch (ScriptException e) {

             e.printStackTrace();

         }

         Context aContext = ContextManager.getContextInstance();

         Parameter parameters = new Parameter();

         String aParamList[] = {              "order","from","to"};

         for (int i=0; i<aParamList.length; i++){

             if ( (reportContext.getParameterValue(aParamList[i])!=null)

                                                        && (((reportContext.getParameterValue(aParamList[i]))).toString().trim().length()>0) ){

                                                                               

                   Parameter.addColumn(aParamList[i],        

                   reportContext.getParameterValue(aParamList[i]).toString());

             }

         }

         aContext.setParameters(parameters);

         processParameters(reportContext, parameters);

     }

     public void processParameters(IReportContext reportContext, Parameter parameters){

     }

}

---

 

As for the supportive classes, have a look at this example:

---

public class Context {

              public  Parameter parameters;

              public  void setParameters(Parameter parameters){

                            this.parameters = parameters;

              }

}

---

---

import java.io.IOException;

import java.util.LinkedList;

public class Parameter {

              public static LinkedList<String[]> parameters;

              public Parameter(){

                            parameters = new LinkedList<String[]>();

              }

              public static void addColumn(String aParam, String paramValue){

                            String [] dummy = {aParam, paramValue};

                            parameters.add(dummy);

              }

}

---

---

import com.company.birt.io.Context;

 

public class ContextManager {

             

              private static Context _context = null;

              public static Context getContextInstance(){                           

                            if (_context==null){

                                          _context = new Context();

                            }

                            return _context;

              }

}

---

 

15. Information sources:

 

One of the best sources of information are the two books:

-          BIRT – A Field Guide to Reporting (online version)

-          Integrating and Extending Birt (online version)

 

A good source of information can be found in the Eclipse Birt Report Developer Guide and the APIs, which is in the help contents of Eclipse or here.

Some further docs which might be helpful might be found on the Actuate website.

15.1 Useful websites for specific topics:

 

Birt internet forums:

www.birt-exchange.com (If possible, always post an example with the Sample Data Source/Set)

http://www.eclipse.org/forums/index.php?t=thread&frm_id=2

 

Scripted Data Sets:

http://www.eclipse.org/birt/phoenix/deploy/reportScripting.php#javaevents 

A simple example: http://dev.eclipse.org/newslists/news.eclipse.birt/msg16471.html 

http://digiassn.blogspot.com/2006/10/birt-scripted-data-source-to-call-web.html 

 

Using BIRT Report Viewer:

http://www.eclipse.org/birt/phoenix/deploy/viewerUsage2.2.php  

Data Access FAQ:

http://wiki.eclipse.org/index.php/BIRT/FAQ/Data_Access 

A very good Tutorial for BIRT with POJO's and Charts:

http://www.vogella.de/articles/EclipseBIRT/article.html 

Deployment of reports with BIRT:

http://www.packtpub.com/article/deployment-of-reports-with-birt 

BIRT Ressources:

http://wiki.eclipse.org/Articles%28BIRT%29 

BIRT World Blog:

http://birtworld.blogspot.com/ 

Create an Amazon Book Catalog using BIRT and WS:

http://dev.senthil.co.in/2007/01/create-java-classes-from-wsdl-may-be.html 

Birt and Java interface:

http://davidjberman.com/blogs/birt_reporting/default.aspx 

Deploying BIRT:

http://onjava.com/pub/a/onjava/2006/07/26/deploying-birt.html?page=1