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 work 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.
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
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:
set CLASSPATH="%CLASSPATH%;\save\java\jt400.jar;\save\java\mysql.jar;\save\java\db2java.zip;lib\DataVision.jar;lib\MinML2.jar;lib\jcalendar.jar;lib\jruby.jar;lib\gnu-regexp-1.1.4.jar;lib\iText.jar" java -classpath %CLASSPATH% jimm.datavision.DataVision %1 %2 %3 %4 %5 %6 %7 %8 %9
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):
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
|6, 7, 8||Db2java.zip||COM.ibm.db2.java.app.DB2Driver||Jdbc:db2|
|iSeries Java toolbox
|JDK 1.1x to JDK 1.4||Jt400.jar||com.ibm.as400.access.AS400JDBCDriver||Jdbc:as400|
|iSeries Java native
|JDK 1.1x to JDK 1.4||Rt.jar||com.ibm.db2.jdbc.app.DB2Driver||Jdbc:db2|
|JDK 1.1x to JDK 1.4||Classes.zip or rt.jar||sun.jdbc.odbc.JdbcOdbcDriver||Jdbc:odbc|
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
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
|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:
|Figure 3: Report definition completed|
|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.
|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:
Department Phone List Fri Jun 25 17:37:26 EDT 2004 "starting at last name = " 'A' Department Last_name First_name Phone Emp_nbr ADMINISTRATION SYSTEMS DANIEL 000250 0961 SMITH "ADMINISTRATION SYSTEMS" EVA 000070 7831 PULASKI "ADMINISTRATION SYSTEMS" JAMES 000230 2094 JEFFERSON "ADMINISTRATION SYSTEMS" MARIA 000270 9001 PEREZ "ADMINISTRATION SYSTEMS" SALVATORE 000240 3780 MARINO "ADMINISTRATION SYSTEMS" SYBIL 000260 8953 JOHNSON "ADMINISTRATION SYSTEMS" 6 employees in department INFORMATION CENTER DOLORES 000130 4578 QUINTANA INFORMATION CENTER HEATHER 000140 1793 NICHOLLS INFORMATION CENTER SALLY 000030 4738 KWAN INFORMATION CENTER 3 employees in department MANUFACTURING SYSTEMS BRUCE 000150 4510 ADAMSON "MANUFACTURING SYSTEMS" DAVID 000200 4501 BROWN "MANUFACTURING SYSTEMS" ELIZABETH 000160 3782 PIANKA "MANUFACTURING SYSTEMS" IRVING 000060 6423 STERN "MANUFACTURING SYSTEMS" JAMES 000190 2986 WALKER "MANUFACTURING SYSTEMS" JENNIFER 000220 0672 LUTZ "MANUFACTURING SYSTEMS" MARILYN 000180 1682 SCOUTTEN "MANUFACTURING SYSTEMS" MASATOSHI 000170 2890 YOSHIMURA "MANUFACTURING SYSTEMS" "Jim Mason (jemason@ebt -now.com)" 1 Department Last_name First_name Phone Emp_nbr WILLIAM 000210 0942 JONES "MANUFACTURING SYSTEMS" 9 employees in department OPERATIONS EILEEN 000090 5498 HENDERSON OPERATIONS ETHEL 000280 8997 SCHNEIDER OPERATIONS JOHN 000290 4502 PARKER OPERATIONS MAUDE 000310 3332 SETRIGHT OPERATIONS PHILIP 000300 2095 SMITH OPERATIONS 5 employees in department PLANNING MICHAEL 000020 3476 THOMPSON PLANNING 1 employees in department SOFTWARE SUPPORT JASON 000340 5708 GOUNOT SOFTWARE SUPPORT RAMLAL 000320 9990 MEHTA SOFTWARE SUPPORT THEODORE 000100 0972 SPENSER SOFTWARE SUPPORT WING 000330 2103 LEE SOFTWARE SUPPORT 4 employees in department SPIFFY COMPUTER SERVICE DIV. CHRISTINE 000010 3978 HAAS "SPIFFY COMPUTER SERVICE DIV." SEAN 000120 2167 O'CONNELL "SPIFFY COMPUTER SERVICE DIV." VINCENZO 000110 3490 LUCCHESSI "SPIFFY COMPUTER SERVICE DIV." 3 employees in department SUPPORT SERVICES JOHN 000050 6789 GEYER SUPPORT SERVICES 1 employees in department "Jim Mason (email@example.com)" 2
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.
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.
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.