Web-based report writing with Datavision

At long last, Jim Mason has found a simple report writer that works in Web environments and doesn't break your budget.

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.

Installing Datavision

To install Datavision, I did the following:

  1. Used Winzip to unzip the file datavision-0.8.1_RptWtr_gui.tar.gz into a Windows directory
  2. 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:

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:

  1. Double-click the datavision.bat file in your install folder. (Datavision opens in a new window.)
  2. 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):

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.

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 (jemason@ebt-now.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:

 

  1. Add the five jar files from the Datavision lib directory to the WEB-INF/lib directory of your application
  2. Create a new report object
  3. Read the XML report definition file into the report object
  4. Use the XML report connection information or set a new connection to the database
  5. Set the runtime selection parameters from Java code or an XML parameter file
  6. Set the layout engine (for a Web application use HTML or PDF)
  7. 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.


 

This was first published in June 2004

Dig deeper on Web Tools

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close