<html xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:jr="http://jasperreports.sourceforge.net/jasperreports">
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JasperReports 4.0.2 - Query Sample</title>
<style type="text/css">
.title {
font-family: Arial, Verdana, Helvetica, sans-serif;
font-size: 28px;
font-weight: normal;
}
.toc {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.name {
font-family: Courier New, Courier, serif;
font-size: 16px;
font-weight: bold;
}
.label {
font-family: Arial, Verdana, Helvetica, sans-serif;
font-size: 12px;
font-weight: bold;
font-style: italic;
}
.description {
font-family: Arial, Verdana, Helvetica, sans-serif;
font-size: 12px;
font-weight: normal;
}
.value {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.element {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.attribute {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: bold;
}
.code {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.copy {
font-decoration: none;
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 8pt;
font-style: normal;
color: #000000;
}
.subtitle {
font-family: inherit;
font-size: inherit;
font-style: inherit;
font-weight: bold;
text-decoration: none;
color: inherit;
}
</style>
</head>
<body bgcolor="#FFFFFF">
<a name="top"></a>
<table cellspacing="0" cellpadding="0" border="0" width="100%">
<tr>
<td colspan="2" align="right"><span class="element"><a href="../../sample.reference.html">Sample Reference</a> - <a href="../../schema.reference.html">Schema Reference</a> - <a href="../../config.reference.html">Configuration Reference</a> - <a href="http://jasperreports.sourceforge.net/api/index.html">API (Javadoc)</a></span>
<br>
</td>
</tr>
<tr>
<td colspan="2">
<hr size="1">
</td>
</tr>
<tr valign="middle">
<td nowrap="true"><span class="title">JasperReports - Query Sample (version 4.0.2)</span></td><td align="right"><img src="../../resources/jasperreports.png" border="0"></td>
</tr>
<tr>
<td colspan="2">
<hr size="1">
</td>
</tr>
</table>
<br>
<span class="description"><span class="description">Shows how the report query can be build dynamically using report parameters.</span></span>
<br>
<br>
<span class="element"><a href="http://sourceforge.net/projects/jasperreports/files/jasperreports/JasperReports%204.0.2/jasperreports-4.0.2-project.zip/download" target="_blank">Download All Sample Source Files</a></span>
<br>
<span class="element"><a href="http://jasperforge.org/scm/viewvc.php/tags/jr-4-0-2/jasperreports/demo/samples/query/?root=jasperreports" target="_blank">Browse Sample Source Files on SVN</a></span>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td style="width: 20px;">
<br>
</td><td>
<br>
</td>
</tr>
<tr>
<td colspan="2"><span class="label">Main Features in This Sample</span></td>
</tr>
<tr>
<td>
<br>
</td><td><span class="element"><a href="#query">Parameterized Queries (Dynamic Queries)</a></span></td>
</tr>
</table>
<table width="100%" cellspacing="0" cellpadding="0" border="0">
<tr>
<td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td width="80%">
<br>
</td><td width="20%">
<br>
</td>
</tr>
<tr>
<td colspan="6" align="right"><a name="query"></a><a href="#top" class="toc">top</a></td>
</tr>
<tr>
<td colspan="6">
<hr size="1">
</td>
</tr>
<tr valign="top">
<td><img src="../../resources/jr-16x16.png" border="0"></td><td colspan="4"><span class="name">Parameterized Queries (Dynamic Queries)</span></td><td align="right"><span class="copy">Documented by
<a href="mailto:lshannon@users.sourceforge.net" class="copy">Luke Shannon</a><span class="description"> , </span><a href="mailto:shertage@users.sourceforge.net" class="copy">Sanda Zaharia</a></span></td>
</tr>
<tr>
<td colspan="6">
<br>
</td>
</tr>
<tr valign="top">
<td>
<br>
</td><td nowrap="true"><span class="label">Description / Goal</span></td><td>
<br>
</td><td colspan="3"><span class="description">
How to pass parameter references to report queries and how to change the report query at runtime.
</span></td>
</tr>
<tr valign="top">
<td>
<br>
</td><td colspan="1"><span class="label">Since</span></td><td>
<br>
</td><td colspan="3"><span class="description">0.1.0</span></td>
</tr>
<tr>
<td colspan="6">
<br>
</td>
</tr>
<tr>
<td>
<br>
</td><td colspan="5"><span class="description">
One of the most powerful features in the JasperReports API, when running JDBC based reports, is the ability to perform complex manipulations<br>
of the report query during the Filling stage of the report life cycle (this is what the JasperReports API executes the query against the <br>
datasource getting back the data for the report). The API offers three powerful tools for query manipulation that are reviewed in this sample.<br>
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 <br>
style background image.
<br>
<br>
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 <br>
of a report data source (JDBC in this case), parameters (object references that are passed into the report-filling operations by the parent <br>
application), fields (maps data from the data source into the report template), variables (objects built on top of a report expression that <br>
perform various calculations) and groups (covered in the groups sample). <br>
All report templates (JRXML) implicitly declare and use a main dataset. The main dataset is responsible for <br>
iterating through the data source records, calculating variables, filtering out records, and estimating group breaks during the <br>
report-filling process. <br>
In the case of a JDBC based report, the Fields map to the columns coming back from the query. Modifications to the <br>
query allows for fundamentally changes to the data the report works with. <br>
Using Parameters to do this allows us to use information gathered from the parent application, which in turn could come from a<br>
report user (example: A user may provide a start and end date for which they want the data the report show to occur within).
<br>
<br>
<b>Running the Sample</b>
<br>
<i>Prerequisites</i>
<br>
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):<br>
<pre>
C:\>ant -version Apache Ant version 1.8.0 compiled on February 1 2010
</pre>
You can obtain ant from http://ant.apache.org/, instructions for installation can be found there as well.
<br>
<br>
<i>Starting the Data Source</i>
<br>
In a command prompt/terminal window browse to the demo/hsqldb folder of the JasperReports source and run the command 'ant runServer'.
<br>
Leave window/terminal running (see below for sample output).
<br>
<pre>
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
</pre>
<i>Generating the Report</i>
<br>
Open up a separate command prompt/terminal window and browse to the root directory of the sample.
<br>
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.
<br>
Each export type will generate a output type in the build/report folder.
<br>
By running the command 'ant' the following actions will be performed:
<br>
<ul>
<li>All java code will be compiled to produce class files.</li>
<li>JRXML fills will be compiled by JasperReports to produce a .jasperfile (this is a serialized version of a JasperReports object).</li>
<li>The report will be filled with data and the resulting object, JasperPrint, will be serialized to the file system as a .jrprint.</li>
<li>All the exporters the sample is configured to test will run.</li>
</ul>
<br>
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).
<br>
Each of the these task can be ran separately as well:
<br>
<ul>
<li>ant clean - removes all generated files.</li>
<li>ant javac - compiles all java code, this should be done before running further tasks.</li>
<li>ant compile - compiles the JRXML generating a .jasper file.</li>
<li>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.</li>
<li>ant view - opens the report in the JasperViewer</li>
<li>ant pdf - generates a PDF (other exporters are available run 'ant -p' for a full list)</li>
</ul>
<b>Note:</b> All generated files can be found in build/reports.
<br>
You now have a working version of the report you can review.
<br>
<br>
<b>Tools for Manipulating the Query</b>
<br>
<i>$P{}</i>
<br>
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 <br>
query before it is executed. The example does not illustrate this concept, however the $X{} explained shortly works with a similar concept.<br>
When using $P{} you do something like the following:
<pre>
SQL query string: SELECT * FROM Address WHERE city = $P{customerId}
</pre>
If we changed the query in this way and turned on Debugging for the <span class="element"><a href="http://jasperreports.sourceforge.net/api/net/sf/jasperreports/engine/query/JRJdbcQueryExecuter.html" target="_blank">JRJdbcQueryExecuter</a></span> in an application running this report, <br>
we would get an output like this (the hosting application also collected the value for the parameter and supplied it to JasperReports <br>
when it was time to Fill the report):
<pre>
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
</pre>
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.<br>
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.<br>
This illustrates one way of injecting values into the query.
<br>
<br>
<i>$P!{}</i>
<br>
Using $P!{} allows you to modify the query syntax itself. The query in the sample uses this:
<pre>
SELECT * FROM Address WHERE $X{NOTIN, City, ExcludedCities} ORDER BY $P!{OrderClause}
</pre>
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:
<pre>
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
</pre>
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 <br>
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 <br>
to modify the query entirely. For example we could have set the whole 'ORDER BY' clause using $P!{}, or chosen to omit it entirely.
<br>
<br>
<i>$X{}</i>
<br>
<br>
There are also cases when just using <span class="code"><code>$P{}</code></span> in the report query is not enough, because
parts of the query need to be dynamically built, depending on one or more report parameter values,
in order to construct a valid query. The most common case is the <span class="code"><code><column_name> = $P{<param_name>}</code></span>
equality clause. When the value of the parameter is null, the expression <span class="code"><code><column_name> = NULL</code></span>
becomes invalid and it has to be replaced with <span class="code"><code><column_name> IS NULL</code></span>. Another case
comes with <span class="code"><code>IN</code></span> and <span class="code"><code>NOT IN</code></span> query clauses that need to use a collection report parameter as a list of values,
unavailable for the simple <span class="code"><code>$P{}</code></span> syntax.
<br>
Such complex query clauses are introduced into the query using the $X{} syntax. The general form of a
<span class="code"><code>$X{}</code></span> clause is <span class="code"><code>$X{functionName, param1, param2,...}</code></span>.
<br>
Similar to the <span class="code"><code>$P{}</code></span> explanation above, <span class="code"><code>$X{}</code></span> results in <span class="code"><code>?</code></span> 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.
<br>
<br>
<a name="clause_functions" href="../../sample.reference/query/index.html#clause_functions" class="subtitle">Built-in SQL Clause Functions</a>
<br>
<br>
As shown above, complex queries generation might depend on parameter values. JasperReports provides built-in support for
several SQL clause functions which require some additional processing:
<ol>
<li>
<b>The <span class="code"><code>${IN, <column_name>, <parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects three mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>IN</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the report parameter that contains the value list. The value of this
parameter has to be an array, a <span class="code"><code>java.util.Collection</code></span> or <span class="code"><code>null</code></span>.
</li>
</ul>
If the parameter's value is a collection of not null values,
the function constructs a <span class="code"><code><column_name> IN (?, ?, .., ?)</code></span> clause
<br>
If the parameter's value is a collection containing both null and not null values,
the function constructs a <span class="code"><code>(<column_name> IS NULL OR <column_name> IN (?, ?, .., ?))</code></span> clause
<br>
If the parameter's value is a collection containing only null values,
the function constructs a <span class="code"><code><column_name> IS NULL</code></span> clause
<br>
If the parameter's value is null, the function generates a SQL clause that will always evaluate to <span class="code"><code>true</code></span>
(e.g. <span class="code"><code>0 = 0</code></span>).
<br>
</li>
<li>
<b>The <span class="code"><code>${NOTIN, <column_name>, <parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects three mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>NOTIN</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the report parameter that contains the value list. The value of this
parameter has to be an array, a <span class="code"><code>java.util.Collection</code></span> or <span class="code"><code>null</code></span>.
</li>
</ul>
If the parameter's value is a collection of not null values,
the function constructs a <span class="code"><code><column_name> NOT IN (?, ?, .., ?)</code></span> clause
<br>
If the parameter's value is a collection containing both null and not null values,
the function constructs a <span class="code"><code>(<column_name> IS NOT NULL AND <column_name> NOT IN (?, ?, .., ?))</code></span> clause
<br>
If the parameter's value is a collection containing only null values,
the function constructs a <span class="code"><code><column_name> IS NOT NULL</code></span> clause
<br>
If the parameter's value is null, the function generates a SQL clause that will always evaluate to <span class="code"><code>true</code></span>
(e.g. <span class="code"><code>0 = 0</code></span>).
</li>
</ol>
Since JasperReports v4.0.1, the following built-in clause functions are also available:
<ol start="3">
<li>
<b>The <span class="code"><code>${EQUAL, <column_name>, <parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects three mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>EQUAL</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the report parameter that contains the value to compare to.</li>
</ul>
If the parameter's value is not null,
the function constructs a <span class="code"><code><column_name> = ?</code></span> clause.
<br>
If the parameter's value is null, the function generates a <span class="code"><code><column_name> IS NULL</code></span> clause.
<br>
</li>
<li>
<b>The <span class="code"><code>${NOTEQUAL, <column_name>, <parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects three mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>NOTEQUAL</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the report parameter that contains the value to compare to.</li>
</ul>
If the parameter's value is not null,
the function constructs a <span class="code"><code><column_name> <> ?</code></span> clause.
<br>
If the parameter's value is null, the function generates a <span class="code"><code><column_name> IS NOT NULL</code></span> clause.
<br>
</li>
<li>
<b>The <span class="code"><code>${LESS, <column_name>, <parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects three mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>LESS</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the report parameter that contains the value to compare to.</li>
</ul>
If the parameter's value is not null,
the function constructs a <span class="code"><code><column_name> < ?</code></span> clause.
<br>
If the parameter's value is null, the comparison with null will be
neglected and the function generates a SQL clause that will always evaluate to <span class="code"><code>true</code></span>
(e.g. <span class="code"><code>0 = 0</code></span>).
<br>
<br>
<b>Note</b>: If the comparison with null (which always return <span class="code"><code>false</code></span>) should not be ignored, then one
can use the <span class="code"><code><column_name> < $P{<parameter_name>}</code></span> instead.
<br>
</li>
<li>
<b>The <span class="code"><code>${LESS], <column_name>, <parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects three mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>LESS]</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the report parameter that contains the value to compare to.</li>
</ul>
If the parameter's value is not null,
the function constructs a <span class="code"><code><column_name> <= ?</code></span> clause.
<br>
If the parameter's value is null, the comparison with null will be
neglected and the function generates a SQL clause that will always evaluate to <span class="code"><code>true</code></span>
(e.g. <span class="code"><code>0 = 0</code></span>).
<br>
<br>
<b>Note</b>: If the comparison with null (which always return <span class="code"><code>false</code></span>) should not be ignored, then one
can use the <span class="code"><code><column_name> <= $P{<parameter_name>}</code></span> instead.
<br>
</li>
<li>
<b>The <span class="code"><code>${GREATER, <column_name>, <parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects three mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>GREATER</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the report parameter that contains the value to compare to.</li>
</ul>
If the parameter's value is not null,
the function constructs a <span class="code"><code><column_name> > ?</code></span> clause.
<br>
If the parameter's value is null, the comparison with null will be
neglected and the function generates a SQL clause that will always evaluate to <span class="code"><code>true</code></span>
(e.g. <span class="code"><code>0 = 0</code></span>).
<br>
<br>
<b>Note</b>: If the comparison with null (which always return <span class="code"><code>false</code></span>) should not be ignored, then one
can use the <span class="code"><code><column_name> > $P{<parameter_name>}</code></span> instead.
<br>
</li>
<li>
<b>The <span class="code"><code>${[GREATER, <column_name>, <parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects three mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>[GREATER</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the report parameter that contains the value to compare to.</li>
</ul>
If the parameter's value is not null,
the function constructs a <span class="code"><code><column_name> >= ?</code></span> clause.
<br>
If the parameter's value is null, the comparison with null will be
neglected and the function generates a SQL clause that will always evaluate to <span class="code"><code>true</code></span>
(e.g. <span class="code"><code>0 = 0</code></span>).
<br>
<br>
<b>Note</b>: If the comparison with null (which always return <span class="code"><code>false</code></span>) should not be ignored, then one
can use the <span class="code"><code><column_name> >= $P{<parameter_name>}</code></span> instead.
<br>
</li>
<li>
<b>The <span class="code"><code>${BETWEEN, <column_name>, <left_parameter_name>, <right_parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects four mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>BETWEEN</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the parameter that contains the left member value.</li>
<li>The fourth token is the name of the parameter that contains the right member value.</li>
</ul>
If both parameter values are not null,
the function constructs a double comparison, similar to the BETWEEN SQL clause where both interval endpoints
are excluded: <span class="code"><code>(<column_name> > ? AND <column_name> < ?)</code></span>
<br>
If the left parameter's value is null,
the function constructs a <span class="code"><code><column_name> < ?</code></span> clause,
using the right parameter's value at fill time.
<br>
If the right parameter's value is null,
the function constructs a <span class="code"><code><column_name> > ?</code></span> clause,
using the left parameter's value at fill time.
<br>
If both parameter values are null, the function generates a SQL clause that will always evaluate to <span class="code"><code>true</code></span>
(e.g. <span class="code"><code>0 = 0</code></span>).
<br>
</li>
<li>
<b>The <span class="code"><code>${[BETWEEN, <column_name>, <left_parameter_name>, <right_parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects four mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>[BETWEEN</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the parameter that contains the left member value.</li>
<li>The fourth token is the name of the parameter that contains the right member value.</li>
</ul>
If both parameter values are not null,
the function constructs a double comparison, similar to the BETWEEN SQL clause where the left interval endpoint
is included and the right endpoint is excluded: <span class="code"><code>(<column_name> >= ? AND <column_name> < ?)</code></span>
<br>
If the left parameter's value is null,
the function constructs a <span class="code"><code><column_name> < ?</code></span> clause,
using the right parameter's value at fill time.
<br>
If the right parameter's value is null,
the function constructs a <span class="code"><code><column_name> >= ?</code></span> clause,
using the left parameter's value at fill time.
<br>
If both parameter values are null, the function generates a SQL clause that will always evaluate to <span class="code"><code>true</code></span>
(e.g. <span class="code"><code>0 = 0</code></span>).
<br>
</li>
<li>
<b>The <span class="code"><code>${BETWEEN], <column_name>, <left_parameter_name>, <right_parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects four mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>BETWEEN]</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the parameter that contains the left member value.</li>
<li>The fourth token is the name of the parameter that contains the right member value.</li>
</ul>
If both parameter values are not null,
the function constructs a double comparison, similar to the BETWEEN SQL clause where the left interval endpoint
is excluded and the right endpoint is included: <span class="code"><code>(<column_name> > ? AND <column_name> <= ?)</code></span>
<br>
If the left parameter's value is null,
the function constructs a <span class="code"><code><column_name> <= ?</code></span> clause,
using the right parameter's value at fill time.
<br>
If the right parameter's value is null,
the function constructs a <span class="code"><code><column_name> > ?</code></span> clause,
using the left parameter's value at fill time.
<br>
If both parameter values are null, the function generates a SQL clause that will always evaluate to <span class="code"><code>true</code></span>
(e.g. <span class="code"><code>0 = 0</code></span>).
<br>
</li>
<li>
<b>The <span class="code"><code>${[BETWEEN], <column_name>, <left_parameter_name>, <right_parameter_name>}</code></span> clause function</b>
<br>
<br>
The function expects four mandatory clause tokens:
<ul>
<li>The first token represents the function ID and always takes the fixed value <span class="code"><code>[BETWEEN]</code></span>.</li>
<li>The second token is the SQL column (or column combination) to be used in the clause.</li>
<li>The third token is the name of the parameter that contains the left member value.</li>
<li>The fourth token is the name of the parameter that contains the right member value.</li>
</ul>
If both parameter values are not null,
the function constructs a double comparison, similar to the BETWEEN SQL clause where both interval endpoints
are included: <span class="code"><code>(<column_name> >= ? AND <column_name> <= ?)</code></span>
<br>
If the left parameter's value is null,
the function constructs a <span class="code"><code><column_name> <= ?</code></span> clause,
using the right parameter's value at fill time.
<br>
If the right parameter's value is null,
the function constructs a <span class="code"><code><column_name> >= ?</code></span> clause,
using the left parameter's value at fill time.
<br>
If both parameter values are null, the function generates a SQL clause that will always evaluate to <span class="code"><code>true</code></span>
(e.g. <span class="code"><code>0 = 0</code></span>).
<br>
</li>
</ol>
In this sample the query string contains a <span class="code"><code>$X{NOTIN, City, ExcludedCities}</code></span> piece of code:
<pre>
<parameter name="ExcludedCities" class="java.util.Collection"/>
<parameter name="OrderClause" class="java.lang.String"/>
<queryString><![CDATA[SELECT * FROM Address WHERE $X{NOTIN, City, ExcludedCities} ORDER BY $P!{OrderClause}]] ></queryString></pre>
If we run the report again and pass two values into $P{ExcludedCities}:
<pre>
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
</pre>
<b>Creating Watermarks with the Background Band</b>
<br>
When working with JasperReport templates it is best to avoid overlap of elements otherwise you get unexpected results in many of our exporters. <br>
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 <br>
Background band (this will be the case of each page in the generated report).<br>
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 <br>
displayed on each page of the generated report.<br>
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.<br>
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:
<pre>
<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>
</pre>
<b>Further Resources:</b>
<br>
JasperReports Ultimate Guide (available from the JasperSoft eShop)<br>
iReport Ultimate Guide (available from the JasperSoft eShop)<br>
</span></td>
</tr>
<tr>
<td colspan="6">
<br>
</td>
</tr>
</table>
<br>
<table cellspacing="0" cellpadding="0" border="0" width="100%">
<tr>
<td>
<hr size="1">
</td>
</tr>
<tr>
<td align="center"><span class="copy">© 2001-2010 Jaspersoft Corporation <a href="http://www.jaspersoft.com" target="_blank" class="copy">www.jaspersoft.com</a></span></td>
</tr>
</table>
</body>
</html>
