Sample Reference - Schema Reference - Configuration Reference - API (Javadoc)
JasperReports - Query Sample (version 3.7.6) |

Shows how the report query can be build dynamically using report parameters.
Download All Sample Source Files
Browse Sample Source Files on SVN

Main Features in This Sample
| Parameterized Queries (Dynamic Queries)

top
| Parameterized Queries (Dynamic Queries) | Documented by Luke Shannon
| Description / Goal |  | How to pass parameter references to report queries and how to change the report query at runtime.
| Since |  | 0.1.0
| One of the most powerful features in the JasperReports API, when running JDBC based reports, is the ability to perform complex manipulations of the report query during the Filling stage of the report life cycle (this is what the JasperReports API executes the query against the datasource getting back the data for the report). The API offers three powerful tools for query manipulation that are reviewed in this sample. We will be discussing each of these in this document. Also demonstrated in this sample is how to use the Background band for adding a watermark style background image. Before we go further we should discuss the concept of a DataSet. A DataSet is an element of the report template and it the combination of a report data source (JDBC in this case), parameters (object references that are passed into the report-filling operations by the parent application), fields (maps data from the data source into the report template), variables (objects built on top of a report expression that perform various calculations) and groups (covered in the groups sample). All report templates (JRXML) implicitly declare and use a main dataset. The main dataset is responsible for iterating through the data source records, calculating variables, filtering out records, and estimating group breaks during the report-filling process. In the case of a JDBC based report, the Fields map to the columns coming back from the query. Modifications to the query allows for fundamentally changes to the data the report works with. Using Parameters to do this allows us to use information gathered from the parent application, which in turn could come from a report user (example: A user may provide a start and end date for which they want the data the report show to occur within). Running the Sample Prerequisites Ant is required. By running 'ant --version' you will be able to check if ant is set up on your system (at least version 1.5 is required):
        C:\>ant -version Apache Ant version 1.8.0 compiled on February 1 2010
You can obtain ant from http://ant.apache.org/, instructions for installation can be found there as well. Starting the Data Source In a command prompt/terminal window browse to the demo/hsqldb folder of the JasperReports source and run the command 'ant runServer'. Leave window/terminal running (see below for sample output).
        C:\js-workspace\jasperreports\demo\hsqldb>ant runServer
    	Buildfile: C:\js-workspace\jasperreports\demo\hsqldb\build.xml

    	runServer:
         [java] [Server@83cc67]: [Thread[main,5,main]]: checkRunning(false) entered
         [java] [Server@83cc67]: [Thread[main,5,main]]: checkRunning(false) exited
         [java] [Server@83cc67]: Startup sequence initiated from main() method
         [java] [Server@83cc67]: Loaded properties from [C:\js-workspace\jasperreports\demo\hsqldb\server.properties]
         [java] [Server@83cc67]: Initiating startup sequence...
         [java] [Server@83cc67]: Server socket opened successfully in 19 ms.
         [java] [Server@83cc67]: Database [index=0, id=0, db=file:test, alias=] opened sucessfully in 1104 ms.
         [java] [Server@83cc67]: Startup sequence completed in 1125 ms.
         [java] [Server@83cc67]: 2010-03-10 11:32:30.423 HSQLDB server 1.8.0 is online
         [java] [Server@83cc67]: To close normally, connect and execute SHUTDOWN SQL
         [java] [Server@83cc67]: From command line, use [Ctrl]+[C] to abort abruptly
Generating the Report Open up a separate command prompt/terminal window and browse to the root directory of the sample. By running 'ant -p' you will be presented with a list of options available. Of interest in this list is all the exporters available for testing. Each export type will generate a output type in the build/report folder. By running the command 'ant' the following actions will be performed: All java code will be compiled to produce class files. JRXML fills will be compiled by JasperReports to produce a .jasperfile (this is a serialized version of a JasperReports object). The report will be filled with data and the resulting object, JasperPrint, will be serialized to the file system as a .jrprint. All the exporters the sample is configured to test will run. You can now run 'ant view' to see a version of the report in the JasperViewer (an applet contained in the JasperReports package which can be used to view a .jrprint object). Each of the these task can be ran separately as well: ant clean - removes all generated files. ant javac - compiles all java code, this should be done before running further tasks. ant compile - compiles the JRXML generating a .jasper file. ant fill - fills the report with data, some reports use the empty data source, others use the HSQL DB and other an inline data source. A .jrprint object is generated in this step. ant view - opens the report in the JasperViewer ant pdf - generates a PDF (other exporters are available run 'ant -p' for a full list) Note: All generated files can be found in build/reports. You now have a working version of the report you can review. Tools for Manipulating the Query $P{} Using $P{} in the report query is for situations where the query is fixed at design time and you only wish to inject values into the query before it is executed. The example does not illustrate this concept, however the $X{} explained shortly works with a similar concept. When using $P{} you do something like the following:
       SQL query string: SELECT * FROM Address WHERE city = $P{customerId}
If we changed the query in this way and turned on Debugging for the JRJdbcQueryExecuter in an application running this report, we would get an output like this (the hosting application also collected the value for the parameter and supplied it to JasperReports when it was time to Fill the report):
       	2010-03-11 12:47:53,648 DEBUG JRJdbcQueryExecuter,http-8080-5:155 - SQL query string: SELECT * FROM Address WHERE city = ?
    	2010-03-11 12:47:53,660 DEBUG JRJdbcQueryExecuter,http-8080-5:252 - Parameter #1 (city of type java.lang.String): New York
In this case the query as seen above and the parameter are passed to the database via the JDBC Driver (MySQL in this example) to be executed. As report developers we don't have to worry about adding quotes around the String value for city in the query as that will be done for us. This illustrates one way of injecting values into the query. $P!{} Using $P!{} allows you to modify the query syntax itself. The query in the sample uses this:
        	SELECT * FROM Address WHERE $X{NOTIN, City, ExcludedCities} ORDER BY $P!{OrderClause}
If we run the report in an application and collect values for the parameters (OrderBy was given the value 'LastName') we will see an output like this:
        2010-03-11 13:01:05,818 DEBUG JRJdbcQueryExecuter,http-8080-4:155 - SQL query string: SELECT * FROM Address WHERE City NOT IN (?) ORDER BY LastName
    	2010-03-11 13:01:05,821 DEBUG JRJdbcQueryExecuter,http-8080-4:303 - Parameter #1 (ExcludedCities[0] of type java.lang.String): New York
Here we can see the value of $P!{OrderClause} was added into the query directly by JasperReports. For this reason, when working with $P!{} you must ensure any values collected will not result in a syntax error in the query as they will be inserted directly into the query. However this does give us the power to modify the query entirely. For example we could have set the whole 'ORDER BY' clause using $P!{}, or chosen to omit it entirely. $X{} $X takes three arguments: Function: Can either be IN or NOT IN Column you want to apply the function too in the query The values the function will use If we run the report again and pass two values into $P{ExcludedCities}:
        2010-03-11 13:25:23,300 DEBUG JRJdbcQueryExecuter,http-8080-4:155 - SQL query string: SELECT * FROM Address WHERE City NOT IN (?, ?) ORDER BY LastName
    	2010-03-11 13:25:23,302 DEBUG JRJdbcQueryExecuter,http-8080-4:303 - Parameter #1 (ExcludedCities[0] of type java.lang.String): New York
    	2010-03-11 13:25:23,302 DEBUG JRJdbcQueryExecuter,http-8080-4:303 - Parameter #2 (ExcludedCities[1] of type java.lang.String): Boston
Similar to the $P{} explanation above, $X{} results in ? being added to the query before submitting it to the DB. Also submitted are the values collected leaving it to the JDBC driver to add the values in and ensure the syntax of the query is correct. In this case we would only get back rows that did not contain the city New York or Boston. In we used the IN function of $X, we could have just gotten rows containing New York and Boston. Creating Watermarks with the Background Band When working with JasperReport templates it is best to avoid overlap of elements otherwise you get unexpected results in many of our exporters. An exception to this rule in the Background band. Content of the pages in the filled report will be overlaid on top of elements placed in the Background band (this will be the case of each page in the generated report). This is why the band is a full page size in the design and contains an image the same height. The result is to have this image displayed on each page of the generated report. TIP: As is the example it is better to have more 'faded' images or text as to not distract the viewer from the main content of the report. Here we can see how the band can be configured to have an image occur in a specific position on each of the rendered pages of the report:
        <background>
    		<band height="742">
    			<image scaleImage="Clip" hAlign="Left" vAlign="Bottom">
    				<reportElement x="0" y="0" width="150" height="742"/>
    				<imageExpression class="java.lang.String"><![CDATA["jr.watermark.gif" ] ]></imageExpression>
    			</image>
    		</band>
    	</background>
Further Resources: JasperReports Ultimate Guide (available from the JasperSoft eShop) iReport Ultimate Guide (available from the JasperSoft eShop)

© 2001-2010 Jaspersoft Corporation www.jaspersoft.com