Lesson 8

Lesson 8: Creating a Report Using User Data Source

JReport supports users to produce reports using user's own data source. Users provide their data via the Data Source Driver.

This lesson provides an introduction to JReport Data Source Driver. It also shows you step by step how to create and run reports using Data Source Driver:

Create a dummy table in the catalog.

Create a report using the dummy table.

Write the data source driver class.

Run the report with the data source driver.

The example in this lesson is to create an address list report, one of the most commonly used reports. The addresses are not usually stored in the database. Instead, they are frequently stored in a text file. Using the data source driver, we can easily create reports from the address file.

We assume that in an address file each line represents a complete address and the lines are separated by line breakers. In the address line, items are separated by Tab characters. The items include: First Name, Last Name, Address, City, State/Prov., Postal Code, Country, Email address, Home Number, Office Number and Fax Number.

The contents in this example address file are shown below:

Laurena	Croft	34826 Atwood St.	New York City	NY	10004	USA	l.croft@sales.gourmetjava.com	(301) 823-7424	(216) 598-4348	(212) 946-5692
Jonathan	Hopkins	5062 Brandon Green Ave.	Minneapolis	MN	55402	USA	j.hopkins@sales.gourmetjava.com	(301) 946-2349	(972) 795-9452	(612) 538-7188
Jeremy	Miner	9283 Cherry Leaf Lane	Palo Alto	CA	94303	USA	j.miner@marketing.goumetjava.com	(410) 349-2834	(201) 573-0001	(650) 239-9586
Jennifer	Lee	2340 Pondside Ave.	Vancouver	BC	V6B2E2	Canada	j.lee@gourmetjava.com	(703) 223-4422	(416) 622-5095	(604) 629-4932
Geoffery	Schultz	82649 San Antonio Rd	Atlanta	GA	30305	USA	g.schultz@marketing.gourmetjava.com	(301) 342-2343	(253) 923-9481	(404) 348-0984
Katherine	Chen	29437 Bainbridge Drive	Chicago	IL	60632	USA	k.chen@gourmetjava.com	(202) 980-2345	(212) 834-9082	(773) 236-5829
Jimmy	Rayner	89273 Garden of Roses Ave.	Colorado Springs	CO	80919	USA	j.rayner@sales.gourmetjava.com	(703) 529-5923	(703) 394-8741	(719) 713-2138
William	Penne	10954 Piso St.	Villanhermosa	Tabasco	86160	Mexico	w.penne@gourmetjava.com	(410) 234-9872	(416) 622-5095	01 (529) 351-4432
Laura	Fung	7839 Jacobian Drive	Irvine	CA	92606	USA	l.fung@gourmetjava.com	(301) 528-2392	(404) 348-5792	(714) 243-9458
Mary	Everett	30237 Devonshire Boulevard	Indianapolis	IN	46268	USA	m.everett@marketing.gourmetjava.com	(202) 417-7033	(215) 428-3890	(317) 942-1288
Edward	Packard	5679 Tiny Foot Drive	Boston	MA	02109	USA	"e.packard@sales,gourmetjava.com"	(703) 985-5293	(407) 945-9111	(617) 438-5900
Jackie	Westray	82374 Grand View Drive	San Francisco	CA	94111	USA	j.westray@sales.gourmetjava.com	(202) 286-8245	(732) 611-8347	(415) 288-6739
Duke	Edmond	9384 Kingdom Falls Road	Fairfax	VA	22030	USA	d.edmund@marketing.gourmetjava.com	(410) 987-4346	(512) 348-8018	(703) 563-4857
Joseph	Compton	63822 Gateway Drive	Silver Spring	MD	20906	USA	j.compton@gourmetjava.com	(301) 396-9864	(972) 987-6549	(301) 394-9283
Maxine	Olympus	6934 Parkland St.	Tacoma	WA	98401	USA	m.olympus@sales.gourmetjava.com	(703) 234-6173	(919) 982-8312	(253) 983-9833

1. Create a dummy table in catalog

The first step to provide meta data to JReport Designer is to creat a dummy table in the catalog before starting to design the report, so that JReport can layout the report with required data field information (such as the data type and precision). The dummy table is created in the JDBC connection and is used during the design phase just like any other table. When the report is run, JReport will not use the data in the dummy table. Instead, you specify in the report to have JReport access the data from the your Data Source Driver.

In short, what you need to do is to make the data set definition, create the table in a JDBC database and add the table to the catalog.

1.1 Define the data set

The first task of creating a dummy table is to make the data set definition. For example, the data set is defined as:

Column Name	Data Type	Precision	Scale
First Name	Text	50
Last Name	Text	50
Address	Text	255
City	Text	50
State/Prov.	Text	20
Postal Code	Text	20
Country	Text	50
Email address	Text	50
Home Number	Text	30
Office Number	Text	30
Fax Number	Text	30

1.2 Create the table in database

In the JDBC database, you will create a SQL table and leave the table empty.

Because the tutorial catalog is based on Microsoft Access database, you should start the Microsoft Access and open the database demo.mdb. Select the New button in the Tables tab and double click the Design View item in the New Table dialog. After the design view is shown, you can define each field in the table.

1.3 Add the table to catalog

When you have created the table in database, you should add the table to the catalog.

In this example:

The catalog browser appears.

The Add Table dialog appears.

You will find the Addresses Table under the Tables item in the catalog browser.

2. Create a report using the dummy table

After the dummy table is created in the catalog, it is ready to be used to create a report just like any other tables.

In this example:

You create a formula called "Capital" that calculates the initial letter of last name, and then group the details according to the formula "Capital".

l8f4.gif (11719 bytes)

You create a formula called "Fullname" that calculates the full name of the person.

l8f5.gif (7447 bytes)

Start JReport. When the Choose Report dialog appears, double click on the BlankReport icon in the New tab. The Report Wizard will appear.

1. Highlight the Address table
2. Click the button to add the table to query.
3. Type the "AddressBook" on the Query Name field.
4. Click Finish button to create the report.

Then the designer window appears.

You can place some other fields into the reports and change the style of the report objects. Please refer to lesson2 and lesson3 for instruction on how to design the report. After some work, your report could look like:

Click the save button on the toolbar and save the report with the name of lesson8.

3. Write the data source driver class

The main task left by now is to design and code the Data Source Driver. The driver is a java class inheriting from jet.datasource.JRResultSet and implements some abstract methods to provide data to the reports. Please see the documentation of Data Source Driver for detail.

In this example:

You should write a class called AddressListDataSource that inherits from jet.datasource.JRResultSet

public class AddressListDataSource extends JRResultSet

In the class, you should have some properties to store the status and data.

ColumnNameToIndex is a hash table that maps each column name to its column index. In the hash table, the key is the name of the column in the catalog and the value is the index of the column in the address list file. You will use it to look up the column index for a given column name.

Hash table ColumnNameToIndex = new Hashtable(10);

aiColMap is an array that maps required columns to columns in the address file. In the array, the index is the column index of a required column and the content is the corresponding column index in the address file. You will use this mapping when trying to retrieve the actual column.

int aiColMap[];

values is an array that stores the values in the current row. In the array, the index is the column index in the address list file and the content is a the String object that keeps the value.

String values[];

bWasNull is a flag that indicates if the last accessed column was null.

boolean bWasNull = false;

reader is used to read the address list file line by line.

BufferedReader reader;

When AddressListDataSource is constructed, the mapping from column names to column indexes is constructed.

In the hash table, the column name is the field name in the catalog. The column name is used to compare with the column name in column description. The column name in the column description is in upper case and the field name in catalog is case-insensitive.

public AddressListDataSource()

{

    ColumnNameToIndex.put("FIRSTNAME", new Integer(0));    

    ColumnNameToIndex.put("LASTNAME", new Integer(1));    

    ColumnNameToIndex.put("ADDRESSES_ADDRESS", new Integer(2));    

    ColumnNameToIndex.put("ADDRESSES_CITY", new Integer(3));    

    ColumnNameToIndex.put("STATEORPROVINCE", new Integer(4));    

    ColumnNameToIndex.put("POSTALCODE", new Integer(5));    

    ColumnNameToIndex.put("ADDRESSES_COUNTRY", new Integer(6));

    ColumnNameToIndex.put("EMAILADDRESS", new Integer(7));    

    ColumnNameToIndex.put("HOMEPHONE", new Integer(8));    

    ColumnNameToIndex.put("WORKPHONE", new Integer(9));    

    ColumnNameToIndex.put("FAXNUMBER", new Integer(10));    

}

The initialize method handles the column descriptions and arguments.

1. The program creates the value array.

2. The program creates aiColMap according to colDescs and ColumnNameToIndex. For each column in colDescs, the program will look up in ColumnNameToIndex and set the index in aiColMap.

3. Open the address list file specified by the first argument.

public void initialize(JRColDesc colDescs[], String args[]) throws JRResultSetException

{

    values = new String[11]; // 11 columns in the file.



    aiColMap = new int[colDescs.length];

    for (int i = 1; i < colDescs.length; i++)

    {

        aiColMap[i] = ((Integer)ColumnNameToIndex.get(colDescs[i].colName)).intValue();

    }



    if (args.length >= 1)

    {

        try

        {

            File flAddressList = new File(args[0]);

            reader = new BufferedReader(new FileReader(flAddressList));  

        }

        catch(FileNotFoundException e)

        {

            throw new JRResultSetException("File not found. " + args[0]);

        }

    }

    else

    {

        throw new JRResultSetException("AddressListDataSource need parameter.");

    }

}

The next method is to read the next line of the file and use StringTokenizer to get each value.

public boolean next() throws JRResultSetException

{

    try

    {

        String record = reader.readLine();

        

        if ( record != null )

        {

            StringTokenizer tokenizer = new StringTokenizer(record, "\t");

            int iIndex = 0;



            while(tokenizer.hasMoreTokens())

            {

                values[iIndex] = tokenizer.nextToken();

                iIndex++;

            }

        }



        return (record != null);

    }

    catch(IOException e)

    {

        throw new JRResultSetException("Cannot read file.");

    }

}

The close method closes the address list file.

public void close() throws JRResultSetException

{

    try

    {

        reader.close();

    }

    catch(IOException ioe)

    {

        throw new JRResultSetException("Cannot close the file.");

    }

    return;

}

The WasNull method returns if the last accessed column is null.

public boolean wasNull() throws JRResultSetException

{

    return bWasNull;

}

Get the value of the specified column in string. In this example, all columns are in text type and only getString method is implemented, other methods are ignored.

public String getString(int columnIndex) throws JRResultSetException

{

    bWasNull = (values[aiColMap[columnIndex]] == null || values[aiColMap[columnIndex]].length() == 0);

    if (!bWasNull)

    {

        return values[aiColMap[columnIndex]];

    }



    return null;

}

You can find the complete program in AddressListDataSource.java

4. Run the report with the data source driver

Before running the report, you should:

1. Compile the AddressLIstDataSource.java and generate AddressListDataSource.class

2. Place the AddressListDataSource.class in a directory, for example c:\,

3. Append the string c:\ to the classpath variable.

4. Place the address file AddressList.txt in the c:\ directory.

4.1 Add the data source driver to the data source driver manager.

In this example:

The Data Source Driver Manager dialog appears.

The Data Source Editor dialog appears.

After finishing it, you can find the driver in the Data Source Driver Manager dialog.

4.2 Specify the data source in JReport designer.

Open the report and specify the name of the data source driver for the report.

In this example,

1. In JReport designer click the Open button on the toolbar and the Choose Report dialog appears. In the Choose Report dialog, highlight on lesson8 and Click on the Open button to open the lesson8 report.

2. Choose the Address from Data Driver dropdown list in the toolbar.

4.3 Run the report using the data source driver

In this example, let us see how the report works with the data source driver.

1. Click the View Tab to run the report and view the result.

Your screen should look like this:

Congratulations, you have just written a data source driver and created a report using the driver. However, there are much more details in the data source driver. Please see the documentation on data source driver.