Context: You have database and you need to do data analysis: draw charts, build some tables, calculate totals, etc. You want it all to be available over the web and secured with a password.
Developer Environment
Thats all, you're secured :) This is should be fine for most cases, but I would recommend you to read about HTTPS if your data is extremely secure.
Now you should be able to start tomcat and run reports on the server.
Troubleshooting.
Your database is any JDBC-supported database (I use MySQL 5.1.49).
Your server is running any OS where Java can run (I use Ubuntu Linux 10.10 available through SSH).
I will show how to implement this using Eclipse BIRT 3.7.
Developer Environment
- Download "Eclipse IDE for Java and Report Developers" package here.
Unzip to install. - Design new report (I created sales.rptdesign). This is really straightforward.
JDBC Driver. You will need MySQL JDBC driver to create Data Source for report. I got mysql-connector-java-5.1.17-bin.jar here.
Installing the driver in BIRT designer is easy using "Manage drivers..." dialog. But you will probably have problems deploying it to the runtime.
Fonts. You probably won't have any problems with fonts in BIRT designer. And again you will likely have problems with fonts in runtime.
Connection Profile Store. With BIRT 3.7 you can use Connection Profiles to hold database connections. After you've finished designing and testing your report, double click report Data Source to bring properties dialog and create new connection profile store in there. Save it to some file (I saved to planet33_v2.xml).
Here's what I have in there:
Note: I bet you can use JDNI data sources here (and I suppose this is even preferable because of connection pooling, etc.). Please, drop a few lines in comments below with instructions how you do this.
You can now edit XML source of you report and replace /report/data-sources with something like this:
Several things to mention here:
Connection Profile Store. With BIRT 3.7 you can use Connection Profiles to hold database connections. After you've finished designing and testing your report, double click report Data Source to bring properties dialog and create new connection profile store in there. Save it to some file (I saved to planet33_v2.xml).
Here's what I have in there:
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <DataTools.ServerProfiles version="1.0"> <profile autoconnect="No" desc="" id="ecc3bc60-d4fd-11e0-957a-e0e31b9a34ee" name="planet33_v2" providerID="org.eclipse.datatools.enablement.mysql.connectionProfile"> <baseproperties> <property name="org.eclipse.datatools.connectivity.db.connectionProperties" value="" /> <property name="org.eclipse.datatools.connectivity.db.savePWD" value="true" /> <property name="org.eclipse.datatools.connectivity.drivers.defnType" value="org.eclipse.datatools.enablement.mysql.5_1.driverTemplate" /> <property name="jarList" value="/usr/local/share/mysql-connector-java-5.1.17-bin.jar" /> <property name="org.eclipse.datatools.connectivity.db.username" value="your_username" /> <property name="org.eclipse.datatools.connectivity.db.driverClass" value="com.mysql.jdbc.Driver" /> <property name="org.eclipse.datatools.connectivity.db.databaseName" value="planet33_v2" /> <property name="org.eclipse.datatools.connectivity.db.password" value="your_password" /> <property name="org.eclipse.datatools.connectivity.db.version" value="5.1" /> <property name="org.eclipse.datatools.connectivity.db.URL" value="jdbc:mysql://127.0.0.1:3306/planet33_v2" /> <property name="org.eclipse.datatools.connectivity.db.vendor" value="MySql" /> </baseproperties> <org.eclipse.datatools.connectivity.versionInfo> <property name="server.version" value="5.1.49" /> <property name="technology.name.jdbc" value="JDBC" /> <property name="server.name" value="MySQL" /> <property name="technology.version.jdbc" value="4.0.0" /> </org.eclipse.datatools.connectivity.versionInfo> <driverreference> <property name="driverName" value="MySQL JDBC Driver" /> <property name="driverTypeID" value="org.eclipse.datatools.enablement.mysql.5_1.driverTemplate" /> </driverreference> </profile> </DataTools.ServerProfiles>
Note: I bet you can use JDNI data sources here (and I suppose this is even preferable because of connection pooling, etc.). Please, drop a few lines in comments below with instructions how you do this.
You can now edit XML source of you report and replace /report/data-sources with something like this:
<data-sources> <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc.dbprofile" name="Planet33 V2 Data Source" id="359"> <property name="OdaConnProfileName">planet33_v2</property> <property name="OdaConnProfileStorePath">../conf/planet33_v2.xml</property> </oda-data-source> </data-sources>
Several things to mention here:
- planet33_v2.xml (Connection Profile Store)
- Check all properties and change them according to your connection.
- Note the jarList property, there you should specify path(s) to where your JDBC drivers located (I copied driver that I've downloaded to /usr/local/share/mysql-connector-java-5.1.17-bin.jar).
- When you create connection profile store file from designer it places property with name="org.eclipse.datatools.connectivity.driverDefinitionID". You should remove this property because of this issue.
- sales.rptdesign (The Report)
- You should keep value of ida-data-source@id attribute the same that was in your design.
- Value of OdaConnProfileName should match value of DataTools.ServerProfiles/profile@name attribute from planet33_v2.xml.
- Note that OdaConnProfileStorePath is relative path (see below). But you can keep it absolute if you want.
Server Environment
(Note: I recommend to configure Tomcat instance on your developer machine first to make it easier to verify report settings, and then transfer the entire $CATALINA_HOME to production server. Of course, you can do all these steps on production server directly.)
- Download Apache Tomcat (any Java application server should be fine).
Unzip to some folder (I used /usr/local/share/apache-tomcat-5.5.33/) -- this will be $CATALINA_HOME. - Download BIRT "Runtime" package here.
Copy birt.war (BIRT Web Viewer application) to $CATALINA_HOME/webapps. - Edit $CATALINA_HOME/catalina.sh and paste these lines somewhere after JAVA_OPTS variable initialized (this prepares workspace for DTP plugin):
java_io_tmpdir=$CATALINA_HOME/temp org_eclipse_datatools_workspacepath=$java_io_tmpdir/workspace_dtp mkdir -p $org_eclipse_datatools_workspacepath JAVA_OPTS="$JAVA_OPTS -Dorg.eclipse.datatools_workspacepath=$org_eclipse_datatools_workspacepath"
- Start Tomcat by running $CATALINA_HOME/startup.sh. After this BIRT Report Viewer application should be available by http://localhost:8080/birt. Also birt.war should be now extracted to $CATALINA_HOME/webapps/birt -- this will be $BIRT_HOME. You can now delete $CATALINA_HOME/webapps/birt.war.
- Copy planet33_v2.xml to $CATALINA_HOME/conf as (remember OdaConnProfileStorePath property in sales.rptdesign file?).
- Copy your sales.rtpdesign file to $BIRT_HOME.
At this point you should be able to execute the report by simply following the address http://localhost:8080/birt/frameset?__report=sales.rptdesign&__dpi=600.
Note the __dpi URL parameter -- it controls DPI of chart images rendered in HTML/PDF. You will probably want to modify like this it to increase image quality. Also note that if you set chart output format to SVG you will get vector graphics quality in PDF output.
Security
There are obvious reasons why you may want to keep your reports secure.
Besides that keep in mind that in BIRT Web Viewer application all reports sources (*.rptdesign files) available to user by request. Try navigating to http://localhost:8080/birt/sales.rptdesign and you'll see what I mean. I think this is a good reason, why you should use connection profile store (or at least JNDI data sources), because that files not available through HTTP.
Implementing simple (HTTP BASIC AUTH) security with Tomcat is pretty simple.
First, modify $BIRT_HOME/WEB-INF/web.xml, by adding this (you can change role names as you want):
You may also do the same for $CATALINA_HOME/conf/web.xml to secure all applications in this Tomcat instance.
Second, you should edit $CATALINA_HOME/conf/tomcat-users.xml to define user login and password.
<!-- Define a security constraint on this application --> <security-constraint> <web-resource-collection> <web-resource-name>Entire Application</web-resource-name> <url-pattern>/*</url-pattern> </web-resource-collection> <auth-constraint> <!-- This role is not in the default user directory --> <role-name>manager</role-name> </auth-constraint> </security-constraint> <!-- Define the login configuration for this application --> <login-config> <auth-method>BASIC</auth-method> <realm-name>BIRT Report Viewer</realm-name> </login-config> <!-- Security roles referenced by this web application --> <security-role> <description> The role that is required to log in to the BIRT Report Viewer </description> <role-name>manager</role-name> </security-role>
You may also do the same for $CATALINA_HOME/conf/web.xml to secure all applications in this Tomcat instance.
Second, you should edit $CATALINA_HOME/conf/tomcat-users.xml to define user login and password.
Thats all, you're secured :) This is should be fine for most cases, but I would recommend you to read about HTTPS if your data is extremely secure.
Deploy to server
- Copy Tomcat to the server:
Tip: Use scp command in terminal to transfer files from your machine to the server over SSH:
scp /usr/local/share/apache-tomcat-5.5.33 dmitrygusev@planet33.ru:/usr/local/share/ - Copy JDBC Driver to the server:
- Copy this driver to the same path as specified in the jarList property from planet33_v2.xml file.
- DO NOT COPY this driver to $BIRT_HOME/WEB-INF/lib, because it may lead to ClassNotFoundException.
Fix file permissions. When you copy files over scp you may need to chmod them to grant read/execute access. This should fix it:
chmod a+r /usr/local/share/mysql-connector-java-5.1.17-bin.jar chmod -R a+r /usr/local/share/apache-tomcat-5.5.33/ chmod a+x /usr/local/share/apache-tomcat-5.5.33/bin/*.sh
Now you should be able to start tomcat and run reports on the server.
Fonts. BIRT PDF output doesn't work good for Russian fonts out-of-the-box, because of licensing issues with fonts. One simple solution to fix this is:
- Get the *.ttf font files you need (you can copy them from any Windows installation, look in c:\Windows\Fonts). These 8 files should be enough in most cases (these are "Arial" and "Times New Roman" fonts):
arialbd.ttf arialbi.ttf ariali.ttf arial.ttf
timesbd.ttf timesbi.ttf timesi.ttf times.ttf - Copy these files to /usr/share/fonts/truetype (or any other place that is referenced from fontsConfig.xml).
- Don't forget to fix file permissions:
chmod a+r /usr/share/fonts/truetype/*.ttf - Reference the fonts from *.rptdesign (or configure font-aliases):
/report/styles<style name="report" id="4"> <property name="fontFamily">"Arial"</property> <property name="fontSize">9pt</property> </style>
- Restart Tomcat:
- $CATALINA_HOME/bin/shutdown.sh
- $CATALINA_HOME/bin/startup.sh
Russian Localization. I used this method to do it. Although you may want to try BIRT Language Packs.
Troubleshooting.
- Neither the JAVA_HOME nor the JRE_HOME environment variable is defined
At least one of these environment variable is needed to run this program
You should define JAVA_HOME variable. Execute this command before running Tomcat's *.sh files in terminal:
export JAVA_HOME=/usr/bin/java
- If you get OutOfMemoryError you may want to give JVM more memory. Edit $CATALINA_HOME/bin/catalina.sh to include this (see this thread on stackoverflow, and read more about JVM memory settings):
JAVA_OPTS="$JAVA_OPTS -Xms512m -Xmx512m -XX:MaxPermSize=256m"
- If you got OutOfMemoryError you most likely couldn't restart Tomcat using $CATALINA_HOME/bin/shutdown.sh script.
To kill Tomcat instance use htop command in terminal. In htop interface select Tomcat process (this is /usr/lib/java), press 'k', select 9 SIGKILL in "Send signal" area, and press Enter. To exit htop press 'q'. - Executing report never stops. Tomcat process consumes all CPU resources.
I've seen this situation when used charts in report and they were on page break. I fixed this by moving charts to other place (far from page break). Changing page size to avoid page breaks also fixes this issue.
