extract information from databases using birt and … from your databases, analyze that information,...

Extract information from databases using BIRT andEclipseGenerate fancy summaries, charts, and analysis for yourreports

Skill Level: Intermediate

Tyler Anderson ([email protected])Freelance writerFreelance

24 Jan 2006

Business reporting and analysis is a complex process that is difficult to get perfectwhen you want to produce a professional-looking report. Even more difficult isregularly repeating the exercise with new or updated data. The Business Intelligenceand Reporting Tools (BIRT) is a suite of plug-ins for Eclipse that allows you to extractinformation from your databases, analyze that information, then generate summaries,charts, and analysis for your reports. In this tutorial, you'll learn how to use BIRT inyour Java™ 2 Enterprise Edition (J2EE) applications by creating and developingreports with BIRT using the Eclipse Rich Client Platform (RCP) technology.

Section 1. Before you start

If you have programs that collect or generate data and you need that data analyzedusing various reports, this tutorial can help you get a good understanding of usingBusiness Intelligence and Reporting Tools (BIRT). This tutorial assumes basicknowledge of Eclipse and Apache Derby. Testing of the database is implemented bydeploying the example application on Apache Geronimo.

About this tutorial

In this tutorial, you'll learn how to use BIRT in your J2EE applications by creatingand developing reports using BIRT and Eclipse's Rich Client Platform (RCP)technology. You will install BIRT and create your first report that will interface with

Extract information from databases using BIRT and Eclipse© Copyright IBM Corporation 1994, 2008. All rights reserved. of 33

mailto:[email protected]

http://www.ibm.com/legal/copytrade.shtml

and source its data from a Derby database, and build subsequent reports thatanalyze data from the same data set at a different angle. An example application willbe created that collects data from bank transactions. Tellers and bank managers willenter a transaction using a Web browser. A transaction includes the bankemployee's title, deposit or withdrawal amount, and date. The end result will be thatthe data can be analyzed online, anytime, at several angles. Thus, throughout thistutorial, you will be the "bank manager," and you will use BIRT to view and analyzethe transactions performed at your bank.

Prerequisites

The following tools are needed to follow along. Note that this was written using aMicrosoft® Windows® machine. However, the differences in details for followingalong on another machine should be minor.

• Eclipse and BIRT -- This tutorial uses the BIRT RCP Report DesignerV1.0.1. This version uses RCP. The BIRT RCP build provides everythingyou need to use BIRT, including BIRT V1.0.1, Java 2 JDK V1.4.2, EclipsePlatform Runtime Binary V3.1, GEF Runtime V3.1, and EMF V2.1.0. Youjust unzip the download and away you go. Download the BIRT RCPReport Designer from the BIRT Release Build page.

• BIRT Report Engine -- You need the BIRT Report Engine to be able toview BIRT reports.

• Geronimo -- This tutorial uses Geronimo M5 to deploy the sampleapplication, along with the embedded BIRT report objects to view thereports.

• Java -- Both BIRT, Geronimo, and the example application require Javatechnology. Plus, the example application requires J2EE for the servletyou'll create. This tutorial uses Java V1.4.2_09 and J2EE V1.4. However,any version higher than these should suffice. Download the all-in-oneJava technology bundle.

• Derby -- Download Derby and be sure that the .jars you receive areadded to your CLASSPATH.

• DB2 Drivers -- This tutorial uses the IBM DB2® universal drivers forconnecting to Derby.

• Ant -- You need Ant to build the example application because it simplifiesthe build process of WAR files for Geronimo.

Section 2. Overview

developerWorks® ibm.com/developerWorks

Extract information from databases using BIRT and Eclipse of 33 © Copyright IBM Corporation 1994, 2008. All rights reserved.

http://download.eclipse.org/birt/downloads/build.php?build=R-R1-1_0_1-200508091640

http://download.eclipse.org/birt/downloads/build.php?build=R-R1-1_0_1-200508091640

http://geronimo.apache.org/downloads.html

http://java.sun.com/j2ee/1.4/download.html

http://db.apache.org/derby/derby_downloads.html

http://www14.software.ibm.com/webapp/download/preconfig.jsp?id=2004-08-03+09%3A06%3A46.338402R&S_TACT=104CBW71&S_CMP=&s=

http://ant.apache.org/bindownload.cgi


This section gives a high-level overview of what you will learn throughout thistutorial.

BIRT reports

Traditional reports allow businesses to graphically display data that is readable toother people. BIRT helps automate the processing of creating reports in deployedJ2EE applications. In this tutorial, you will learn how to create a report that will bedeployed with your application.

Example application

When customers go to banks and withdraw or transfer money, the transaction isusually performed by a bank employee. The example application for this tutorial is auser interface (UI) that takes in transaction details and stores them in a Derbydatabase. The recorded transaction details include transaction date, employee title,employee number, transaction type, amount deposited or transferred, etc.

Analyzing data

With the example application taking information and storing it in the database, thedata is in its most raw form, without a feasible organization for viewing in a mannerthat would make sense. The BIRT reports you develop will be used to analyze thisdata.

Each type of report covered will allow you to view and subsequently analyze thedata contained in the database.

Embedding BIRT objects in the application

Once the BIRT reports and the example application have been completed, the BIRTobjects are ready to be embedded within your application. This will allow you, thebank's manager, to readily see the trends of the transactions that occur within yourbank. You will gain knowledge that will allow you to make the decisions to changetrends for the better, and improve productivity and performance at your bank.

Section 3. Derby: Setting up

In this section, you will create and initialize the Derby database with test data for usein BIRT. You'll use the built-in Derby database within Geronimo through Geronimo'snetwork server. You'll use this same database when you deploy your application onGeronimo, so it makes sense to use the built-in database.

Setting up Geronimo for Derby

ibm.com/developerWorks developerWorks®



Since you will use the built-in Derby database in Geronimo for BIRT, you need theright drivers.

There are a couple .jars you need to add to Geronimo: the IBM DB2 JDBC UniversalDrivers. You should have them now (see Prerequisites). Unzip the .zip file you getfrom IBM. Take the two .jar files in the lib directory -- db2jcc.jar anddb2jar_license_c.jar -- and place them in the<geronimo-install-dir>/repository/org.apache.derby/jars directory of your Geronimoinstallation.

Geronimo's all set! Start Geronimo by opening up a console and typing:

java -jar<geronimo-install-dir>/bin/server.jar

The Derby network server running on Geronimo is now ready and listening. Next,you'll connect to the network server to create and initialize the database.

Creating the Derby database

With the network server running, you're ready to create the database. Open aconsole and type:

java org.apache.derby.tools.ij

This fires up the Derby ij tool, and takes you to the ij prompt. Create the database bytyping the following at the ij prompt:

connect 'jdbc:derby:net://localhost:1527/BANK;\create=true:user=bankuser;password=bankpass;';

This connects to the Derby network server running on Geronimo, as well as a newBANK database. You will now be able to connect to and reference it from withinBIRT.

Now we create and initialize the table.

Creating and initializing the transactions table

The transactions table will house all information on transactions performed at yourbank. Create the transactions table at the ij prompt by typing the following, as shownin Listing 1.

Listing 1. Creating the transactions table




create table transactions(transactionid integer not null generated always as identity

(start with 1, increment by 1),employeeid integer not null,employeetitle varchar(50),transactiondate varchar(10),transactiontype varchar(50),amount integer,transactionSrc varchar(50),transactionDst varchar(50));

You just created the transactions table successfully. You'll need to fill it with test datafor your reports, so execute the next query in the same ij prompt, as shown in Listing2.

Listing 2. Inserting records into the transactions table

insert into transactions (employeeid, employeetitle, transactiondate,transactiontype, amount, transactionSrc,transactionDst) values

(201, 'LoanOff', '12-08-2005', 'D', 7845, 'Customer', '651232135'),(203, 'Teller', '12-08-2005', 'T', 7123, '453780785', '864513215'),(204, 'Teller', '12-08-2005', 'W', 3564, '684612312', 'Customer'),(203, 'Teller', '12-08-2005', 'D', 1546, 'Customer', '054537563'),(202, 'SrTeller', '12-08-2005', 'D', 8769, 'Customer', '054537563'),(203, 'Teller', '12-09-2005', 'W', 9753, '754350324', 'Customer'),(201, 'LoanOff', '12-09-2005', 'D', 6482, 'Customer', '878505456'),(203, 'Teller', '12-09-2005', 'D', 4657, 'Customer', '040740075'),(204, 'Teller', '12-09-2005', 'T', 3542, '192837247', '075663333'),(203, 'Teller', '12-09-2005', 'D', 4075, 'Customer', '054537563'),(202, 'SrTeller', '12-09-2005', 'W', 23, '153057753', 'Customer'),(203, 'Teller', '12-09-2005', 'D', 1, 'Customer', '871532404'),(203, 'Teller', '12-10-2005', 'D', 4687, 'Customer', '975434231'),(204, 'Teller', '12-10-2005', 'T', 4578, '456213546', '075275705'),(201, 'LoanOff', '12-10-2005', 'D', 9946, 'Customer', '054537563'),(203, 'Teller', '12-10-2005', 'W', 1572, '468734234', 'Customer'),(203, 'Teller', '12-10-2005', 'D', 999, 'Customer', '345357477'),(202, 'SrTeller', '12-11-2005', 'D', 2457, 'Customer', '875725075'),(204, 'Teller', '12-11-2005', 'W', 7974, '643024563', 'Customer'),(204, 'Teller', '12-11-2005', 'T', 500, '23049834', '23049234');

You should have 20 records in the transactions table that you will use as your testdata.

Next, you'll get into the BIRT RCP Report Designer, and analyze this data.

Section 4. Installing and introducing the BIRT RCPReport Designer

In this section, you will be introduced to the BIRT RCP Report Designer by creatingyour first BIRT project and learning the various views available from within BIRT.

Your first BIRT project




If you haven't already downloaded and unzipped BIRT, do so now (seePrerequisites).

Starting BIRT is as simple as clicking on the BIRT.exe file you get when you unzipthat file. You should see BIRT launch, as shown in Figure 1.

Figure 1. The BIRT RCP Report Designer

This is how BIRT should look each time you start it.

Now let's create your first report. Click File > New Report. Name it bank1.rptdesign.Next, select the type of report we'll create: Blank Report. Click Finish. With an activereport, the look and feel of BIRT will change, with several of the views showingoptions. See Figure 2.

Figure 2. An empty report




With your first report initialized, you'll now learn about the views within BIRT.

Views within BIRT

There are several views of interest in BIRT, including:

PaletteThe palette allows easy drag and drop of various report objects into yourreport.

Data Explorer

• Data Sources -- These are the databases you will use in your report. Thistutorial will use one data source.

• Data Sets -- Query your data sources, using SQL, and provide the datayou will use for your report.

• Report Parameters -- The SQL used within your data sets to query datasources can be parameterizable using question marks. Reportparameters are used to fill in the parameters in your SQL.

OutlineThe outline shows the hierarchy of objects within your report. You can use theoutline to quickly find an object in your report that you would like to modify.

Property Editor




Every object in your report has properties. For example, a heading could haveseveral properties, including font, weight, alignment, etc. This is where you setand modify those properties.

Problems

• If something's wrong with your report, you will find details of it here.

• Main editor view showing an empty report (defaults to layout view)

• Layout -- The view of your report you're currently seeing, also as shownin Figure 2.

• Preview -- This tab shows what your report currently looks like. Currently,your report is empty, so this will display something like Report Header,underlined, and in bold font. You'll soon fix that.

• Code -- Any code or BIRT scripting (JavaScript) you have in your report.See Resources for information about using this feature, as this is beyondthe scope of this tutorial.

Cheat SheetsShows you how to get things done in your report. You won't be using this view,so you can strike it.

That finishes the views you can take advantage of when using the BIRT RPC ReportDesigner. Next, you'll create a data source that will allow you to fill in your report.

Section 5. Interfacing to data sources

This is where you'll create a connection, called a data source, to the Derby databaseyou created in Derby: Setting up. You'll also create a data set using SQL on the datasource.

Creating a new data source

Before you can query a database, BIRT requires that you set up a data source. Thisis done using the Data Explorer view:

1. Go to this Data Explorer view and click on Data Sources.

2. Right-click Data Sources and select New Data Source.

3. In the list presented, select JDBC Data Source.




4. Click Next.

5. Click Manage Drivers.

6. Make sure the JAR Files tab is selected and click Add.

7. Find the DB2 driver jars you downloaded in Prerequisites, select the firstone, db2jcc.jar, and click Open.

8. db2jcc.jar should appear in the list of jars. Follow steps 6 and 7 again, butthis time, add the db2jcc_license_c.jar file.

9. You have now completed the prerequisites for adding the DB2 drivers toBIRT. Click OK.

10. Make sure that com.ibm.db2.jcc.DB2Driver (v2.6) is selected as theDriver Class.

11. Use this as the Database URL: jdbc:derby:net://localhost:1527/BANK;.

12. The user name should be bankuser.

13. The password should be bankpass (see Figure 3).

14. Click the Test Connection button. This makes sure that you can connectto your BANK database you created through the Derby network serverrunning on Geronimo.

15. Click Finish.

Figure 3. Creating a data source




Great! You now have a data source, so let's create a data set to have data for yourreports to analyze.

Creating a data set from a data source

You will use the results of the query in your data set to source the data in yourreport. Right-click Data Sets, and select New Data Set. The options should appear,as shown in Figure 4.

Figure 4. Options for creating your data set




Click Finish. Another window should now pop up: Edit Data Set--moneyPerEmployee. Replace the SQL in the text box on the right with thefollowing SQL:

select employeeid, sum(AMOUNT) as totalAmtfrom TRANSACTIONSgroup by employeeid

Click OK. Your data set is ready for use in your report. Expand themoneyPerEmployee data set by clicking on the + symbol beside your newly createddata set in the Data Explorer view. Your Data Explorer view should look like Figure5.

Figure 5. The completed data set




Let's create one more data set. Right-click the Data Sets object again, and selectNew Data Set, just as before. This time, keep the options the same, except changethe name to transactionTypePerDate. Also, replace the SQL in the right text box withthe following:

select transactiondate, transactiontype, sum(AMOUNT) as totalAmtfrom TRANSACTIONSgroup by transactiondate, transactiontype

You should now have two data sets, which you'll use in the next section to create acustom report.

The tricky parts are behind us. Next, we focus on creating the reports based on thedata from the BANK database.

Section 6. Designing a custom report

In this section, you'll create a custom report with a couple charts that help graphicallydisplay data from the BANK database, so that you, the bank manager, can betteranalyze the transactions happening at your bank.

Available report types

There are two report types: listing and charts. There are several genres within thosetypes (bar graphs, pie charts, etc.). However, most are quite similar. The most




difficult of the two main categories are the charts, and within charts, the most difficultis creating a chart with a series grouping key. Thus, you'll focus on creating twocharts -- one that uses a series grouping key and one that doesn't. A series groupingkey is a way to group similar objects along the X-axis. For example, see the result ofthe transactionTypePerDate data set, shown in Listing 3.

Listing 3. Example results from the transactionTypePerDate data set

TRANSDATE TYPE TOTALAMT12-08-2005 D 1816012-08-2005 T 712312-08-2005 W 9238412-09-2005 D 1521512-09-2005 T 354212-09-2005 W 977612-10-2005 D 1563212-10-2005 T 457812-10-2005 W 157212-11-2005 D 245712-11-2005 T 50012-11-2005 W 7974

If you want to show in a single chart the amount transacted for all three types oftransactions for a given day, you'll have three series (one for each type oftransaction) and four points along the X-axis (one for each day). The series groupingkey in the above case is the TYPE.

You can also add tables and other objects to a blank report using several BIRTobjects. You'll learn about these next.

Available items

There are several items you can insert into your report using the Palette:

• Label -- Static text

• Text -- Dynamic HTML/plain text with optional expressions

• Data -- For inserting a data set or expression result

• Image -- Insert an image (can be a URL, just like HTML)

• Grid -- Allows you to display items using a column/row layout

• List -- Displaying lists of results from a data set with a flexible format

• Table -- Similar to List, but in a column format

• Chart -- Inserts a graphical chart

Thus, you can spruce up your report by adding any other data item you might want.

Next, you'll get to start defining the report.




Creating a title for the report

Let's begin your report, starting with a title:

1. Go to the Palette View, and drag a Label to the top of the empty report.

2. Double-click the label you just dropped and type BANK OF MARS THEPLANET.

3. Let's add a little bit of jazz to the text by going to the Property Editor View.In the left box, make sure General is selected.

4. Click the Bold (B) button.

5. Change the Size to X-Large.

6. Click the Center button.

7. Change the background color to a light blue.

See Figure 6 for the preview results (select the preview tab in the main window).

Figure 6. Report title

Next, you'll create your first chart in BIRT.

Constructing a chart

Your first chart uses the moneyPerEmployee data set you created. Let's begin:




1. Go to the Palette view and drag a Chart to your main report in LayoutView.

2. Select Bar chart, and select the right-most subtype, then click OK.

3. In the new window, change the bar chart title to Money Transacted perEmployee, then click Next.

4. Select the moneyPerEmployee data set, then click Next.

5. Find the text, X-Axis Title, and modify it to Employee ID, then click Next.

6. Find the text, Y-Axis Title and modify it to Amount.

7. Place "Amount" in the bottom-most text box, then click Next.

8. Enter row["EMPLOYEEID"] in the top-most text box (Data Definitions),then click Next.

9. Enter row["TOTALAMT"] in the Data Definitions text box, then click OK.

Click the Preview tab and compare your chart with that shown in Figure 7.

Figure 7. Example chart output

Voila! You've completed your first chart. Next, you'll create one more that allows you




to have more than one series (color).

Constructing another chart

This second chart uses the transactionTypePerDate data set you created. Let'sbegin:

1. Go to the Palette view and drag a Chart to your report main report inLayout View just below your first chart.

2. Select Line chart, select the right-most subtype, then click OK.

3. In the new window, change the bar chart title to Transacted Money perDay per Transaction Type, then click Next.

4. Select the transactionTypePerDate data set, then click Next.

5. Find the text X-Axis Title and modify it to Date, then click Next.

6. Find the text Y-Axis Title and modify it to Amount.

7. Place row["TRANSACTIONTYPE"] in the bottom-most text box (this is theSeries Group Key you learned about earlier), then click Next.

8. Enter row["TRANSACTIONDATE"] in the top-most text box (DataDefinitions), then click Next.

9. Enter row["TOTALAMT"] in the Data Definitions text box, then click OK.

Again, click the Preview tab and compare your chart with that shown in Figure 8.

Figure 8. Example chart output using a series group key




This completes the report. Next, you'll create an example application you'll use toenter more data into the Derby database. And after that, you'll embed your BIRTobjects into your Java application and be able to view your reports online.

Section 7. The example application

This sections builds the example application. It consists of a servlet that takes dataentered by a bank employee and stores this information in the same Derby databaseyou've been working with throughout this tutorial.

Transaction details

The example application contains a form for bank employees to enter information ona transaction:

• Employee ID -- ID of the employee




• Employee Title -- Title of the employee

• Transaction Date -- Date of the transaction

• Transaction Type -- Type of transaction (Withdrawal, Deposit orTransfer)

• Amount -- Dollar amount involved in the transaction

• Transaction Source -- Source of the transaction (customer for deposit,an account number for both withdrawal and transfer)

• Transaction Destination -- Destination of transaction (customer forwithdrawal, an account number for both deposit and transfer)

Next, you'll build the user interface for inserting transaction details to Derby.

Building the servlet: The user interface

This servlet takes transaction data from bank employees and saves it to the BANKdatabase. Now you will start to build the example application. Create aBankingServlet.java file and store it in the following location (rooted at a separateapplication directory): ./src/servlets/BankingServlet.java. Define it, as shown inListing 4.

Listing 4. Developing the user interface with a servlet

package servlets;import java.io.*;import java.util.*;import java.sql.*;import javax.servlet.*;import javax.servlet.http.*;

public class BankingServlet extends HttpServlet {public static final String form ="<form method='post'>\n" +"Employee ID:<br>\n" +"<input name='empID' size='25'><br>\n" +"Employee Title:<br>\n" +

"<input name='empTitle' size='25'><br>\n" +"Transaction Date (MM-DD-YYYY):<br>\n" +

"<input name='transDate' size='25'><br>\n" +"Transaction Type--Deposit (D), " +

"Withdrawal (W), Transfer (T):<br>\n" +"<input name='transType' size='25'><br>\n" +

"Amount:<br>\n" +"<input name='amount' size='25'><br>\n" +

"Transaction Source (Enter Customer, or the account #):<br>\n"+"<input name='transSrc' size='25'><br>\n" +

"Transaction Destination (Enter Customer, " +"or the account #):<br>\n" +

"<input name='transDst' size='25'><br>\n" +"<input type='submit' value='Submit'>\n";

public void doGet(HttpServletRequest request,HttpServletResponse response)

throws ServletException, IOException {response.setContentType("text/html");response.setBufferSize(8192);PrintWriter out = response.getWriter();

out.println("<html>" +




"<head><title>Enter a Transaction</title></head>" +"<body>" +

"<h1>Enter a Transaction</h1>\n" +form +"</form></body></html>");

out.close();}

public String getServletInfo() {return "Enter a transaction!";

}}

The main items of interest are in bold -- namely, the form used to take in informationfrom bank employees and the second bold block where the HTML is written to thebrowser. The form should look like Figure 9.

Figure 9. The form

Building the servlet: Storing data in the BANK database

Now we extend the servlet to add a doPost() function that takes the datasubmitted from the form via POST, and process and store it in the BANK database.

Continue to define the BankingServlet.java file, as shown in Listing 5.

Listing 5. Retrieving the POST request




..."</form></body></html>");

out.close();}

public void doPost(HttpServletRequest request,HttpServletResponse response)

throws ServletException, IOException {response.setContentType("text/html");response.setBufferSize(8192);PrintWriter out = response.getWriter();out.println("<html>" +

"<head><title>Enter a Transaction</title></head>" +"<body>" +

"<h1>Enter a Transaction</h1>\n");String empID_str = request.getParameter("empID");

String empTitle = request.getParameter("empTitle");String transDate = request.getParameter("transDate");String transType = request.getParameter("transType");String amount_str = request.getParameter("amount");String transSrc = request.getParameter("transSrc");String transDst = request.getParameter("transDst");

...

The doPost() method, as shown in Listing 5, starts out by retrieving all of theparameters from the received POST request. Next, you'll take this data, verify thatan employee ID has been entered, and store the information in the database. SeeListing 6.

Listing 6. Processing the POST request and storing it in the database

...String transSrc = request.getParameter("transSrc");String transDst = request.getParameter("transDst");boolean result = false;

if ((empID_str != null) && (empID_str.length() > 0)) {int empID = Integer.parseInt(empID_str);int amount = Integer.parseInt(amount_str);Driver driver = null;try {

driver = (Driver)(com.ibm.db2.jcc.DB2Driver.class).newInstance();

} catch(Exception e) {throw new IllegalArgumentException("Unable to load, " +

"instantiate, or register driver " + driver +": "+e.getMessage());

}

try {Properties prop = new Properties();

prop.put("user", "bankuser");prop.put("password", "bankpass");Connection conn = driver.

connect("jdbc:derby:net://localhost:1527/BANK;",prop);

String sql = "insert into transactions "+"(employeeid, employeetitle, transactiondate, "+"transactiontype, amount, transactionSrc, "+

"transactionDst) values "+"(?, ?, ?, ?, ?, ?, ?)";

PreparedStatement statement =conn.prepareStatement(sql);

statement.setInt(1, empID);statement.setString(2, empTitle);statement.setString(3, transDate);statement.setString(4, transType);statement.setInt(5, amount);statement.setString(6, transSrc);




statement.setString(7, transDst);statement.execute();result = true;

...

If a valid employee ID was entered, a connection is made to the BANK databaseusing the same Driver you set up in BIRT. Then a parameterizable INSERT SQLstatement is created, and the data from the POST request becomes the parameters.The SQL statement is finally executed, and result, previously false, gets set totrue, indicating that no errors/exceptions occurred in the database transaction. Thislast segment starts off by catching any exceptions. See Listing 7.

Listing 7. Developing the user interface with a servlet

...statement.execute();result = true;

} catch(Throwable e) {result = false;e.printStackTrace();System.out.println(e.getMessage());System.out.println("Error executing query");out.println("Error in SQL!<br>");

}}

if(result)out.println("Transaction entered!<br>" + form);

elseout.println("Invalid, try again!<br>" + form);

out.println("</form></body></html>");out.close();

}

public String getServletInfo() {...

Notice that if an exception is thrown, result remains false. A transaction successor failure notice is then output to the screen, followed by the form so a bankemployee can immediately enter another transaction.

This ends the servlet. The rest of the section focuses on deploying and building theapplication on Geronimo.

Geronimo specifics: geronimo-web.xml

Geronimo requires a few minor things when deploying applications. In this case,there are only two files you need to create to facilitate deployment of the applicationon Geronimo: geronimo-web.xml (Geronimo-specific) and web.xml (J2EE-specific).Store them both in the ./WEB-INF/ directory. Define geronimo-web.xml, as shown inListing 8.

Listing 8. Defining geronimo-web.xml

<?xml version="1.0" encoding="UTF-8"?><web-app

xmlns="http://geronimo.apache.org/xml/ns/j2ee/web-1.0"




xmlns:sec="http://geronimo.apache.org/xml/ns/security-1.0"configId="com/ibm/eclipse/birtDemo">

<dependency><uri>org.apache.derby/jars/db2jcc.jar</uri>

</dependency><dependency>

<uri>org.apache.derby/jars/db2jcc_license_c.jar</uri></dependency>

<context-root>/birtDemo</context-root><context-priority-classloader>false</context-priority-classloader>

</web-app>

The configId is the ID associated with your application in Geronimo. Next, specifythe two .jars you added to the Geronimo repository as dependencies, so Geronimocan dynamically load them with your application. These jars are the DB2 drivers.Last, specify where on Geronimo you can view your application -- birtDemo, in yourcase (http://localhost:8080/birtDemo).

Next, you'll define the web.xml file.

J2EE specifics: web.xml

The web.xml file defines standard J2EE properties. Define the web.xml file, asshown Listing 9.

Listing 9. Defining web.xml

<?xml version="1.0" encoding="ISO-8859-1"?><web-app xmlns="http://java.sun.com/xml/ns/j2ee"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/

ns/j2ee/web-app_2_4.xsd"version="2.4">

<servlet><display-name>BankingServlet</display-name>

<servlet-name>BankingServlet</servlet-name><servlet-class>servlets.BankingServlet</servlet-class>

</servlet><servlet-mapping>

<servlet-name>BankingServlet</servlet-name><url-pattern>/transaction.html</url-pattern>

</servlet-mapping>

</web-app>

The servlet tags associate the binary BankingServlet file with a name thatGeronimo uses in the servlet-mapping tags. The servlet-mapping tagsspecify where to place the servlet, so if a browser pointed to that location, the servletwould become active (http://localhost:8080/birtDemo/transaction.html, in this case).

The next step is to build the application using Ant.

Building the application using Ant




To build the application, define a build.xml file and store it at the root, as shown inListing 10.

Listing 10. Defining build.xml

<?xml version="1.0"?><project name="build" default="war" basedir="."><property name="java.home" value="c:/j2sdk1.4.2_09"/>

<property name="j2ee.home" value="c:/Sun/AppServer"/><property name="src.dir" value="src"/><property name="dest.dir" value="bin"/>

<target name="compile"><mkdir dir="${dest.dir}"/><javac srcdir="${src.dir}" destdir="${dest.dir}">

<classpath path="${java.home}/lib/tools.jar"/><classpath path="${j2ee.home}/lib/j2ee.jar"/>

</javac></target><target name="war" depends="compile">

<war destfile="birtDemo.war" webxml="WEB-INF/web.xml"><zipfileset file="WEB-INF/geronimo-web.xml" prefix="WEB-INF"/>

<classes dir="${dest.dir}"/></war>

</target></project>

The first target tag, compile, compiles the servlet and stores it in the properlocation for the second target tag, war. This second target tag takes the web.xml,geronimo-web.xml and compiled servlet files and archives them into a web-archive(WAR) file, birtDemo.war, which is ready for deployment on Geronimo.

Deploying and testing the application on Geronimo

To know if the application is ready for the BIRT objects, deploy and test it onGeronimo. Type the following to deploy your application on Geronimo:

java -jar <geronimo-install-dir>/bin/deployer.jar--user system --password manager deploy birtDemo.war

The application has now been deployed on Geronimo, so open a Web browser andpoint it to http://localhost:8080/birtDemo/transaction.html. The browser should nowlook like Figure 9.

Enter the information in the form, as shown in Figure 10.

Figure 10. Entering a new transaction




Click Submit. If everything works correctly, you'll get the following browser output,as shown in Figure 11.

Figure 11. Example browser output for a successful transaction




The application works, so let's import the BIRT objects and analyze some dataonline.

Section 8. Viewing BIRT reports in Geronimo

This section goes over how to view reports within Geronimo. First, you'll deploy theBIRT viewer on Geronimo, along with the .rpt design file (BIRT file). Then you'll viewthis BIRT report directly from Geronimo.

Package the BIRT viewer with the example application

Packaging the BIRT viewer and your application into one .war file embeds the BIRTviewer within your application. This also enables you to view your reports online,anytime, as the banking employees in your bank add transactions.

Do the following to package the BIRT viewer with your application:

1. Go to the < BIRT Report Engine-install-dir >.




2. Make a new directory, merge, and copy birt.war to this directory.

3. Enter this directory and unarchive birt.war by typing: jar -xf birt.war(necessary because there's a bug in the "Web Viewer Example").

4. Delete the birt.war file.

5. Copy your birtDemo.war application file to the < BIRT ReportEngine-install-dir > /merge directory.

6. Unarchive birtDemo.war by typing: jar -xf birtDemo.war

7. Delete birtDemo.war

8. Merge these two files: WEB-INF/web.xml andWEB-INF/web-template.xml. Copy the contents of WEB-INF/web.xml(belongs to your application) and replace the @generate@ statement inWEB-INF/web-template.xml (See Listing 11).

9. Delete the old web.xml file, and change web-template.xml to web.xml.

10. Copy your report file, bank1.rptdesign, from < BIRT-install-dir >/workspace/bank1.rptdesign to < BIRT Report Engine-install-dir > /merge.

11. Finally, Web-archive the merged Web applications by typing jar -cfbirtDemo.war *.

Listing 11. Appending web.xml to the end of web-template.xml

...<servlet-mapping>

<servlet-name>ViewerServlet</servlet-name><url-pattern>/toolbar</url-pattern>

</servlet-mapping>

<servlet><display-name>BankingServlet</display-name>

<servlet-name>BankingServlet</servlet-name><servlet-class>servlets.BankingServlet</servlet-class></servlet><servlet-mapping>

<servlet-name>BankingServlet</servlet-name><url-pattern>/transaction.html</url-pattern>

</servlet-mapping></web-app>

Next, you'll redeploy your application, and test it out.

Redeploy the combined .war file on Geronimo

You have the viewer and your application in a single .war file. You need to redeployyour application so it replaces the old one within Geronimo. Type the following toredeploy the newly created, merged .war file:




java -jar <geronimo-install-dir>bin/deployer.jar--user system --p password managerredeploy birtDemo.war com/ibm/eclipse/birtDemo

The combined application is installed on Geronimo and ready for testing.

View the BIRT report embedded in your application

Now's the exciting part where you get to test the embedded BIRT reports onGeronimo. Point a browser to the following URL:

http://localhost:8080/birtDemo/frameset?__report=bank1.rptdesign

This should bring up your report, just as it did in the BIRT RCP Report Designer. Ifyou want the output in PDF rather than HTML, tack this onto the above URL:

&__format=pdf

See Figure 12 for a screenshot of the report.

Figure 12. Viewing reports in Geronimo




Cool, huh? You can look at your reports whenever you want. Just point your browserto them, and it doesn't matter what's going on back at the bank.

Next, you'll try adding more transactions to the system and see the changes online.

Add a couple more transactions and view the differences

Let's see if your online reports really work by adding some new records through theuser interface you created. Point your browser to the transactions page(http://localhost:8080/birtDemo/transaction.html).

Add new records without creating new dates. Also, add a withdrawal on 10 Dec2005 of $50,000, and you'll verify that a change has occurred for that date when you




regenerate the report. Enter the new record, as shown in Figure 13.

Figure 13. Adding a withdrawal of $50,000 on 12-10-2005

Click Submit. Go back to the browser where your report is and shift-click the reloadbutton on your browser. This should refresh the report regardless of your browser'scached or saved data. The new report should now look like Figure 14.

Figure 14. Viewing the changes to the database in the BIRT report




Section 9. Summary

Congratulations! You successfully learned how to use the Business Intelligence andReporting Tools, developed for Eclipse, and integrated it with a Derby database. Youthen embedded your new BIRT objects into your example application, all deployedon the Geronimo application server. See Resources for links that point to possibleextension points for BIRT reports, tools, and charts, and take advantage of using thepower of this plug-in with the Eclipse platform.




Downloads

Description Name Size Download method

Source code os-birtDemo.zip 19KB HTTP

Information about download methods



http://download.boulder.ibm.com/ibmdl/pub/software/dw/opensource/os-birtDemo.zip

http://www.ibm.com/developerworks/library/whichmethod.html


Resources

Learn

• For links to the latest version of Eclipse, information on IBM's involvement withEclipse, and a guide to some of the most interesting Eclipse projects, check outGet started now with Eclipse.

• See a presentation from EclipseCon 2005 titled Introducing the.BusinessIntelligence & Reporting Tools (BIRT) Project.

• For information about a BIRT presentation at EclipseCon 2006, see BIRT InDepth: Extending and Using the BIRT Framework.

• Get the BIRT Developer Guide.

• See an excellent introductory BIRT tutorial.

• Check out the "Recommended Eclipse reading list."

• Browse all the Eclipse content on developerWorks.

• New to Eclipse? Read the developerWorks article "Get started with EclipsePlatform" to learn its origin and architecture, and how to extend Eclipse withplug-ins.

• Expand your Eclipse skills by checking out IBM developerWorks' Eclipse projectresources.

• To listen to interesting interviews and discussions for software developers,check out check out developerWorks podcasts.

• For an introduction to the Eclipse platform, see "Getting started with the EclipsePlatform."

• Stay current with developerWorks' Technical events and webcasts.

• Watch and learn about IBM and open source technologies and productfunctions with the no-cost developerWorks On demand demos.

• Check out upcoming conferences, trade shows, webcasts, and other Eventsaround the world that are of interest to IBM open source developers.

• Visit the developerWorks Open source zone for extensive how-to information,tools, and project updates to help you develop with open source technologiesand use them with IBM's products.

Get products and technologies

• Check out the IBM DB2 Universal Drivers.

• Download Apache Derby from Apache.org.

• Download Eclipse from Eclipse.org.

• Download BIRT from Eclipse.org.



http://www.ibm.com/developerworks/opensource/top-projects/eclipse-starthere.html

http://www.eclipsecon.org/2005/presentations/EclipseCon2005_10.1_Introducting_the_BIRT_Project_2005-02-25.ppt

http://www.eclipsecon.org/2005/presentations/EclipseCon2005_10.1_Introducting_the_BIRT_Project_2005-02-25.ppt

http://www.eclipsecon.org/2006/Sub.do?id=9

http://www.eclipsecon.org/2006/Sub.do?id=9

http://help.eclipse.org/help31/index.jsp?topic=/org.eclipse.birt.doc/birt/birt-02-1.html

http://www.eclipse.org/birt/tutorial/

http://www.ibm.com/developerworks/library/os-ecl-read

http://www.ibm.com/developerworks/views/opensource/libraryview.jsp?search_by=eclipse

http://www.ibm.com/developerworks/opensource/library/os-eclipse-platform/

http://www.ibm.com/developerworks/opensource/library/os-eclipse-platform/

http://www.ibm.com/developerworks/opensource/top-projects/eclipse.html

http://www.ibm.com/developerworks/opensource/top-projects/eclipse.html

http://www.ibm.com/developerworks/podcast/

http://www.ibm.com/developerworks/opensource/library/os-ecov/

http://www.ibm.com/developerworks/opensource/library/os-ecov/

http://www.ibm.com/developerworks/offers/techbriefings/

http://www.ibm.com/developerworks/offers/lp/demos/

http://www.ibm.com/developerworks/views/opensource/events.jsp

http://www.ibm.com/developerworks/opensource

http://www14.software.ibm.com/webapp/download/preconfig.jsp?id=2004-08-03+09%3A06%3A46.338402R&S_TACT=104CBW71&S_CMP=&s=

http://db.apache.org/derby/derby_downloads.html

http://www.eclipse.org/

http://www.eclipse.org/birt/


• Download Apache Ant from Apache.org.

• Download Apache Geronimo from Geronimo.apache.org.

• Download Java technology from Sun Microsystems.

• Check out the latest Eclipse technology downloads at IBM alphaWorks.

• Download Eclipse Platform and other projects from the Eclipse Foundation.

• Download IBM product evaluation versions, and get your hands on applicationdevelopment tools and middleware products from DB2®, Lotus®, Rational®,Tivoli®, and WebSphere®.

• Innovate your next open source development project with IBM trial software,available for download or on DVD.

Discuss

• The Eclipse Platform newsgroups should be your first stop to discuss questionsregarding Eclipse. (Selecting this will launch your default Usenet news readerapplication and open eclipse.platform.)

• The Eclipse newsgroups has many resources for people interested in using andextending Eclipse.

• Participate in developerWorks blogs and get involved in the developerWorkscommunity.

About the author

Tyler AndersonTyler Anderson graduated with a degree in Computer Science from Brigham YoungUniversity in 2004 and is currently in his last semester as a Master of Sciencestudent in Computer Engineering. In the past, he worked as a database programmerfor DPMG.COM, and he is currently an engineer for Stexar Corp., based inBeaverton, Oregon.



http://ant.apache.org/

http://geronimo.apache.org/downloads.html

http://java.sun.com/

http://www.alphaworks.ibm.com/eclipse

http://www.alphaworks.ibm.com/

http://www.eclipse.org/downloads/

http://www.ibm.com/developerworks/downloads/

http://www.ibm.com/developerworks/downloads/

news://news.eclipse.org/eclipse.platform

http://www.eclipse.org/newsgroups/

http://www.ibm.com/developerworks/blogs


extract information from databases using birt and … from your databases, analyze that information,...

Documents