Lesson 9: How to set where portion

In order to let user change the condition of a query used in the report at run time, we support the where portion substitution mechanism to let user specify which WHERE portion is going to be used at run time.

JReport Engine provides the methods to save, get, substitute and delete WHERE portions in the catalog.  By specifying the name of the WHERE portion,  users can pick it out from report catalog easily and bind it up with the current query.  (For detail information on the definitions of those methods, please refer to EngineBeanUsage.htm file.)  JReport Client Viewer provides the same methods as JReport Engine dose.  (For detail information on the definitions of those methods, please refer to ViewerBeanUsage.htm file.  Build 352 contains only setWherePortion and setNamedWherePortion methods and Build 355 will contains all the methods about where portion that the engine has.)

Note:
To install JReport Engine bean and JReport Client Viewer bean on you machine, simply down load them from www.jinfonet.com.  Run EngBeanSetup.exe and follow the instructions provided by the install program will install the engine to your machine.  Unzip jvbean.zip file will install the client viewer bean to your machine.

 

How to set where portion in JReport engine bean

Normally you should first design a report.  In the report, you designed a query to fetch the data from the database to generate the report result.

After the report design work is finished, you can run this report.  The JReport engine then will fetch data from the database based on the query used in this report.  The condition in this query is defined when you design the query.

If you want to use other conditions to qualify the data, then what you need to do is to substitute the WHERE portion of the query.  The new WHERE portion could be a predefined WHERE portion or just a new SQL condition string.  The predefined WHERE portion is a WHERE portion saved in the catalog before you run the report.

The illustrative example TestNamedWherePortion.java is a program purely show how those methods operated on WHERE portion.

In order to run this program, you need to compile it first:

javac -classpath c:\jdk1.2\lib\classes.zip;;c:\swing\swingall.jar;c:\jrebean\lib\jrengine.zip;c:\jrebean\lib\jrviewer.zip; TestNamedWherePortion.java

here, we assume that the JDK 1.2 is installed under c:\jdk1.2 directory and the swing is installed under c:\swing directory and JReport Engine bean is installed under c:\jrebean directory.

After the compilation, you can run this sample program by typing the following command:

java -classpath c:\jdk1.2\lib\classes.zip;c:\swing\swingall.jar;c:\jrebean\lib\jrengine.zip;c:\jrebean\lib\jrviewer.zip; TestNamedWherePortion

This example program use the report customerlist.cls as the demo report and this report is in the tutorial catalog which will be installed to you machine after you installed JReport system.

There are five menu items in this example program:

1)"Save WherePortion";
2)"Set WherePortion condition1";
3)"Set WherePortion condition2";
4)"Delete WherePortion 2";
5)"List Available Where Portions";

You can select menuitem "Save WherePortion" from menu for saving the condition1 and condition2 to the current catalog.  The condition1 is 'Customers_Customer ID less than 20' and the condition2 is 'Customers_Customer ID great than 10 and less than 20.'

After you have saved the where portions, you can select menuitem "Set WherePortion condition1" for running the report, then get the result according to where clause 'Customers_Customer ID less than 20'.  You can also select menuitem "Set WherePortion condition2" for running report with the result according to where clause 'Customers_Customer ID great than 10 and less than 20'.  You can see the differences when you compare the two report results.

You can also select menuitem "delete WherePortion 2" to delete the where portion named "condition2" from the current catalog.

You can select menuitem "List Available Where Portion" to get all the available where portion names in current catalog.

 

How to set where portion in JReport client viewer bean

The following show you how to substitute where portion in client / server environment.

First, you need to download JReport Server and and JReport Client viewer bean from www.jinfonet.com.

To install JReport server, run jrssetup.exe (if you are using MS Windows) or jrssetup.sh (if you are using SUN Solaris) and follow the instructions.   For detail information, please refer to our tutorial lesson5.htm and SolarisInstall.htm.

The illustrative example Sample4.java shows you how to use change WHERE portion methods in the client / server environment.   This program will let you first, run a report (customerlist.cls) with the query predefined in this report and then, change the condition of the query in this report by the conditions either condition1 { (@\"Customers_Customer ID\" > 10) and (@\"Customers_Customer ID\" < 20) } or condition2 {@\"Customers_Customer ID\" < 20 }.

To compile this program, the command line is:

javac -classpath c:\jdk1.2\lib\classes.zip;c:\jrviewer\jrviewer.zip; Sample4.java

To run this program, please type:

java -classpath c:\jdk1.2\lib\classes.zip;c:\jrviewer\jrviewer.zip; Sample4

There are 7 menu items you can select in this sample program:
1)"start";                                   // run this report without change the query condition
2)"setWherePortion1"                // substitute the query condition with the condition one (in a string format) and run this report
3)"setWherePortion2"                // substitute the query condition with the condition two (in a string format) and run this report
4)"Previous Page"                     // move to previous page
5)"Next Page"                          // move to next page 
6)"First Page";                         // move to first page
7)"Last Page"                          // move to last page