[IMAGE]
[IMAGE][IMAGE]
Jim Mason
[IMAGE]
[IMAGE]
I've been looking for a while (more than two years, actually) for a low-cost, flexible, simple report
writer that does the following:
- Supports visual design of simple reports accessing SQL data for end users
- Supports optional output of results to a Web page, PDF file, text file, XML
- Allows Java Web applications to run server-based reports as part of applications
- Has a flexible software license agreement
Good news for me. I've found one I really like: Datavision. It more than meets my requirements -- and
many of my customers' as well. It's a product from an open-source project at www.SourceForge.net.
Datavision is available at no charge under an Apache Software Foundation open-source license
(http://www.apache.org/LICENSE.txt). Jim Menard is the developer of this tool. The main Web site to
access Datavision is http://datavision.sourceforge.net/. Intelligently, Jim Menard built Datavision using
other good open-source frameworks: MinML2, Sun graphics, JCalendar, JRuby and iText. A smart
engineer finds parts rather than building them.
Why open-source software works well
I found several proprietary iSeries report writers that work in Web environments. One that I tested had
a "prettier" GUI workbench to design reports with. It had additional features for generating pivot tables,
data drilldowns, etc., but it cost $30,000! That's more than the price of a new i5 server! I decided I could
make a few sacrifices and save the $30,000.
If you are a traditional iSeries shop, you buy software first from IBM and then look to iSeries-based
vendors for tools and applications. I used to w...
To continue reading for free, register below or login
To read more you must become a member of Search400.com
ork that way, too. The low-cost and flexible licensing for
open-source software has always been great compared with billable iSeries software. Now I find that
open-source software can be better than traditional software in many ways I didn't think possible: easier
to install, well-documented and better support.
Yes, open-source software does have its challenges. For example, the companies providing open-
source software are not well known by most iSeries shops. They don't spend tons of your money on
marketing, so it takes more work to find the vendor and support companies. But once you find them, it's
usually worth your while.
Installing Datavision
To install Datavision, I did the following:
- Used Winzip to unzip the file datavision-0.8.1_RptWtr_gui.tar.gz into a Windows directory
- Edited the Datavision.bat file to include the jar files for JDBC drivers I needed to access my databases
To connect to an iSeries database, you'll want to use the iSeries Toolkit from the open-source project SourceForge.net.
Just download the jt400.jar file and add it to your classpath.
Here's the snippet from the Datavision.bat file showing the jt400.jar added to the classpath:
Running Datavision in Windows
To run Datavision, do these two things:
- Double-click the datavision.bat file in your install folder. (Datavision opens in a new window.)
- Fill out the database connection screen to connect to a database.
I built a few reports using DB2/400 data with the JT400 JDBC driver from the iSeries Java toolkit. I
also built reports using DB2 UDB version 8.1 on Windows and MySQL 4.12 databases. Datavision
worked great on all of them.
Connecting to databases
You must connect to a database before you can design or run a report. The initial database
connection screen opens asking for connection information. Here's the connection information I used to
connect to a library (JMA2L1) on my AS/400 (DCS1):
[IMAGE]
Figure 1
Below are some of the database driver options you have with Datavision.
DB Connection Type
Versions
DB Jar File
Driver Class Name
URL Prefix
DB2 UDB database
local connection
6, 7, 8
Db2java.zip
COM.ibm.db2.java.app.DB2Driver
Jdbc:db2
iSeries Java toolbox
remote connection
JDK 1.1x to JDK 1.4
Jt400.jar
com.ibm.as400.access.AS400JDBCDriver
Jdbc:as400
iSeries Java native
local connection
JDK 1.1x to JDK 1.4
Rt.jar
com.ibm.db2.jdbc.app.DB2Driver
Jdbc:db2
Sun JdbcOdbc
connection
JDK 1.1x to JDK 1.4
Classes.zip or rt.jar
sun.jdbc.odbc.JdbcOdbcDriver
Jdbc:odbc
MySQL
connection
3, 4
Mysql-connector-java-2.0.14-bin.jar
com.mysql.jdbc.Driver
Jdbc:mysql
Creating a report in Datavision
After the connection completes, the Report Design workbench opens. The designer is a WSYWYG
tool that works very easily, even for non-programmers. Here you can see I'm building a report by dragging
and dropping fields from the database tables on the left to the report sections on the workbench at
the right.
Selecting and linking tables
I selected two tables from the list on the left (EMPLOYEE and DEPARTMENT) and added a link
definition that said records in the EMPLOYEE table are joined to records in the DEPARTMENT table on
the EMPLOYEE.WORKDEPT field = DEPARMENT.DEPTNO field relationship.
Adding detail fields to the report
As I drop a field on the detail section, the header title for that field is automatically added to the
header column.
[IMAGE]
Figure 2: Drag and drop database fields on the report detail section
From there, I continued to add the following:
- Selection criteria (Select all employees with lastname >= a parameter entered by the user at runtime)
- Sorting by department and employee name
- Grouping totals by department
- Formatting with added text for report headers and footers
- Additional report information
When completed, my report definition in the workbench looked like this:
[IMAGE]
Figure 3: Report definition completed
[IMAGE]
Figure 4: SQL is generated from the report definition
Running the report
Having completed the report definition, I could now run the report. Because I defined an input
parameter, the report tool prompted me for the parameter at runtime. After entering my selection criteria, I
clicked Run. The report was generated and displayed in a new window.
[IMAGE]
Figure 5: Generated report window
Exporting the report to different formats
A great feature of Datavision is the ability to export a report in different formats. There are many, but
some of the more useful ones I found were text files, HTML files, PDF files, XML files and comma-
separated files (for easy import into Excel).
Here's an example of the report exported as a text file:
Using the reports in your own Java Web applications
A key feature of Datavision is the ability to easily embed a Datavision report in a Java Web
application. To add a report to a Java Web application, follow these steps:
- Add the five jar files from the Datavision lib directory to the WEB-INF/lib directory of your application
- Create a new report object
- Read the XML report definition file into the report object
- Use the XML report connection information or set a new connection to the database
- Set the runtime selection parameters from Java code or an XML parameter file
- Set the layout engine (for a Web application use HTML or PDF)
- Run the report (synchronously or asynchronously)
By default, Datavision writes the generated HTML report page to an HTML file. Your JSP can use a
Java scriptlet to load the HTML to your Web page and display it for a user.
That's it!
Given that it's open source, you could even enhance Datavision to do other things, such as write
reports to an AS/400 output queue.
Where Datavision pays off
Typically, companies have several areas they need good reporting tools:
- Users creating their own reports
- Building reports to run as part of an application
- Exporting data from an iSeries database to Excel, Access or another SQL database
Datavision is a flexible, easy-to-use tool that can fit any of those scenarios. While it lacks some of the
features the expensive Report Writer tools offer, it works in just as many scenarios as those other tools
and can save a lot of money on your Web project budget.
---------------------------------------
About the author: Jim Mason works at ebt-now and StructuredSoft, providing iSeries WebSphere,
WebFacing, Java and StructuredJ development and training services.
Jim is creating a self-study course for RPG programmers that teaches "hands-on" rapid visual
development with WDSC for all types of iSeries and e-business applications without the need to become
a Java expert. Rochester Initiative will publish the course this summer.
