<?xml version="1.0" encoding="UTF-8"?>

<sample>

  <name>query</name>
  <title>Query Sample</title>
  <description>Shows how the report query can be build dynamically using report parameters.</description>

  <mainFeature ref="query"/>
  <secondaryFeature name="watermark" sample="hyperlink" title="Creating Watermarks and Page Backgrounds"/>
  <secondaryFeature name="conditionalStylesInTemplates" sample="query" title="Using Conditional Styles Defined in Style Templates"/>
  
  <!-- query -->
  
  <feature name="query" title="Parameterized Queries (Dynamic Queries)">
    <description>
How to pass parameter references to report queries and how to change the report query at runtime.
    </description>
    <since>0.1.0</since>
    <documentedBy>
      <author>
		<name>Luke Shannon</name>
    	<email>lshannon@users.sourceforge.net</email>
      </author>
      <author>
    	<name>Sanda Zaharia</name>
    	<email>shertage@users.sourceforge.net</email>
      </author>
    </documentedBy>
    <content>
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/>
    <br/>
    <i>Prerequisites</i>
    <br/>
    <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><![CDATA[
    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/>
    <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).
     <pre><![CDATA[
    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/>
    <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 .jasper file (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 (a Swing report viewer 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>
    	<li>ant test - performs the same tasks as running the 'ant' command with no parameters</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 Parameterizing the Query</b>
    <br/>
    <br/>
    <i><b>$P{}</b></i>
    <br/>
    <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.
	<pre><![CDATA[
    <queryString>
      <![CDATA[SELECT Id, FirstName, LastName, Street, City 
      FROM Address WHERE City = $P{CityParam}]] >
    </queryString>
	]]></pre>
	If we changed the query this way and turned on Debugging for the <api href="net/sf/jasperreports/engine/query/JRJdbcQueryExecuter.html">JRJdbcQueryExecuter</api> 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><![CDATA[
    2010-03-11 12:47:53,648 DEBUG JRJdbcQueryExecuter,http-8080-5:155 - SQL query string: Id, FirstName, LastName, Street, City 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><b>$P!{}</b></i>
    <br/>
    <br/>
    Using $P!{} allows you to modify the query syntax itself. The query in the sample uses this:
	<pre><![CDATA[
    <queryString>
      <![CDATA[SELECT Id, FirstName, LastName, Street, City, (Id < 30) as ConditionalField 
      FROM Address ORDER BY $P!{OrderClause}]] >
    </queryString>
	]]></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><![CDATA[
    2010-03-11 13:01:05,818 DEBUG JRJdbcQueryExecuter,http-8080-4:155 - SQL query string: SELECT Id, FirstName, LastName, Street, City, (Id < 30) as ConditionalField FROM Address 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><b>$X{}</b></i>
    <br/>
    <br/>
	There are also cases when just using <code>$P{}</code> 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 <code>&lt;column_name&gt; = $P{&lt;param_name&gt;}</code> 
	equality clause. When the value of the parameter is null, the expression <code>&lt;column_name&gt; = NULL</code> 
	becomes invalid and it has to be replaced with <code>&lt;column_name&gt; IS NULL</code>. Another case
	comes with <code>IN</code> and <code>NOT IN</code> query clauses that need to use a collection report parameter as a list of values, 
	unavailable for the simple <code>$P{}</code> syntax. 
	<br/>
	Such complex query clauses are introduced into the query using the $X{} syntax. The general form of a 
	<code>$X{}</code> clause is <code>$X{functionName, param1, param2,...}</code>. 
	<pre><![CDATA[
    <queryString>
      <![CDATA[SELECT Id, FirstName, LastName, Street, City, (Id < 30) as ConditionalField 
      FROM Address 
      WHERE $X{NOTIN, City, ExcludedCities} 
      ORDER BY $P!{OrderClause}]] >
    </queryString>
	]]></pre>
	Similar to the <code>$P{}</code> explanation above, <code>$X{}</code> results in <code>?</code> 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. 
	<pre><![CDATA[
    2010-03-11 13:01:05,818 DEBUG JRJdbcQueryExecuter,http-8080-4:155 - SQL query string: SELECT Id, FirstName, LastName, Street, City, (Id < 30) as ConditionalField 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>
	<br/>
	<br/>
	<subtitle name="clause_functions">Built-in SQL Clause Functions</subtitle>
	<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 <code>$X{IN, &lt;column_name&gt;, &lt;parameter_name&gt;}</code> 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 <code>IN</code>.</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 <code>java.util.Collection</code> or <code>null</code>.
	</li>
	</ul>
	If the parameter's value is a collection of not null values,  
	the function constructs a <code>&lt;column_name&gt; IN (?, ?, .., ?)</code> clause
	<br/>
	If the parameter's value is a collection containing both null and not null values,  
	the function constructs a <code>(&lt;column_name&gt; IS NULL OR &lt;column_name&gt; IN (?, ?, .., ?))</code> clause
	<br/>
	If the parameter's value is a collection containing only null values,  
	the function constructs a <code>&lt;column_name&gt; IS NULL</code> clause
	<br/>
	If the parameter's value is null, the function generates a SQL clause that will always evaluate to <code>true</code> 
	(e.g. <code>0 = 0</code>). 
	<br/>&#160;
	</li>
	<li><b>The <code>$X{NOTIN, &lt;column_name&gt;, &lt;parameter_name&gt;}</code> 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 <code>NOTIN</code>.</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 <code>java.util.Collection</code> or <code>null</code>.
	</li>
	</ul>
	If the parameter's value is a collection of not null values,  
	the function constructs a <code>&lt;column_name&gt; NOT IN (?, ?, .., ?)</code> clause
	<br/>
	If the parameter's value is a collection containing both null and not null values,  
	the function constructs a <code>(&lt;column_name&gt; IS NOT NULL AND &lt;column_name&gt; NOT IN (?, ?, .., ?))</code> clause
	<br/>
	If the parameter's value is a collection containing only null values,  
	the function constructs a <code>&lt;column_name&gt; IS NOT NULL</code> clause
	<br/>
	If the parameter's value is null, the function generates a SQL clause that will always evaluate to <code>true</code> 
	(e.g. <code>0 = 0</code>). 
	</li>
	</ol>
	Since JasperReports v4.0.1, the following built-in clause functions are also available:
	<ol start="3">
	<li><b>The <code>$X{EQUAL, &lt;column_name&gt;, &lt;parameter_name&gt;}</code> 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 <code>EQUAL</code>.</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 <code>&lt;column_name&gt; = ?</code> clause. 
	<br/>
	If the parameter's value is null, the function generates a <code>&lt;column_name&gt; IS NULL</code> clause. 
	<br/>&#160;
	</li>
	<li><b>The <code>$X{NOTEQUAL, &lt;column_name&gt;, &lt;parameter_name&gt;}</code> 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 <code>NOTEQUAL</code>.</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 <code>&lt;column_name&gt; &lt;&gt; ?</code> clause. 
	<br/>
	If the parameter's value is null, the function generates a <code>&lt;column_name&gt; IS NOT NULL</code> clause. 
	<br/>&#160;
	</li>
	<li><b>The <code>$X{LESS, &lt;column_name&gt;, &lt;parameter_name&gt;}</code> 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 <code>LESS</code>.</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 <code>&lt;column_name&gt; &lt; ?</code> 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 <code>true</code> 
	(e.g. <code>0 = 0</code>). 
	<br/>
	<br/>
	<b>Note</b>: If the comparison with null (which always return <code>false</code>) should not be ignored, then one 
	can use the <code>&lt;column_name&gt; &lt; $P{&lt;parameter_name&gt;}</code> instead.
	<br/>&#160;
	</li>
	<li><b>The <code>$X{LESS], &lt;column_name&gt;, &lt;parameter_name&gt;}</code> 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 <code>LESS]</code>.</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 <code>&lt;column_name&gt; &lt;= ?</code> 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 <code>true</code> 
	(e.g. <code>0 = 0</code>). 
	<br/>
	<br/>
	<b>Note</b>: If the comparison with null (which always return <code>false</code>) should not be ignored, then one 
	can use the <code>&lt;column_name&gt; &lt;= $P{&lt;parameter_name&gt;}</code> instead.
	<br/>&#160;
	</li>
	<li><b>The <code>$X{GREATER, &lt;column_name&gt;, &lt;parameter_name&gt;}</code> 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 <code>GREATER</code>.</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 <code>&lt;column_name&gt; &gt; ?</code> 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 <code>true</code> 
	(e.g. <code>0 = 0</code>). 
	<br/>
	<br/>
	<b>Note</b>: If the comparison with null (which always return <code>false</code>) should not be ignored, then one 
	can use the <code>&lt;column_name&gt; &gt; $P{&lt;parameter_name&gt;}</code> instead.
	<br/>&#160;
	</li>
	<li><b>The <code>$X{[GREATER, &lt;column_name&gt;, &lt;parameter_name&gt;}</code> 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 <code>[GREATER</code>.</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 <code>&lt;column_name&gt; &gt;= ?</code> 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 <code>true</code> 
	(e.g. <code>0 = 0</code>). 
	<br/>
	<br/>
	<b>Note</b>: If the comparison with null (which always return <code>false</code>) should not be ignored, then one 
	can use the <code>&lt;column_name&gt; &gt;= $P{&lt;parameter_name&gt;}</code> instead.
	<br/>&#160;
	</li>
	<li><b>The <code>$X{BETWEEN, &lt;column_name&gt;, &lt;left_parameter_name&gt;, &lt;right_parameter_name&gt;}</code> 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 <code>BETWEEN</code>.</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: <code>(&lt;column_name&gt; &gt; ? AND &lt;column_name&gt; &lt; ?)</code>
	<br/>
	If the left parameter's value is null,  
	the function constructs a <code>&lt;column_name&gt; &lt; ?</code> clause, 
	using the right parameter's value at fill time.
	<br/>
	If the right parameter's value is null,  
	the function constructs a <code>&lt;column_name&gt; &gt; ?</code> 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 <code>true</code> 
	(e.g. <code>0 = 0</code>). 
	<br/>&#160;
	</li>
	<li><b>The <code>$X{[BETWEEN, &lt;column_name&gt;, &lt;left_parameter_name&gt;, &lt;right_parameter_name&gt;}</code> 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 <code>[BETWEEN</code>.</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: <code>(&lt;column_name&gt; &gt;= ? AND &lt;column_name&gt; &lt; ?)</code>
	<br/>
	If the left parameter's value is null,  
	the function constructs a <code>&lt;column_name&gt; &lt; ?</code> clause, 
	using the right parameter's value at fill time.
	<br/>
	If the right parameter's value is null,  
	the function constructs a <code>&lt;column_name&gt; &gt;= ?</code> 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 <code>true</code> 
	(e.g. <code>0 = 0</code>). 
	<br/>&#160;
	</li>
	<li><b>The <code>$X{BETWEEN], &lt;column_name&gt;, &lt;left_parameter_name&gt;, &lt;right_parameter_name&gt;}</code> 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 <code>BETWEEN]</code>.</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: <code>(&lt;column_name&gt; &gt; ? AND &lt;column_name&gt; &lt;= ?)</code>
	<br/>
	If the left parameter's value is null,  
	the function constructs a <code>&lt;column_name&gt; &lt;= ?</code> clause, 
	using the right parameter's value at fill time.
	<br/>
	If the right parameter's value is null,  
	the function constructs a <code>&lt;column_name&gt; &gt; ?</code> 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 <code>true</code> 
	(e.g. <code>0 = 0</code>). 
	<br/>&#160;
	</li>
	<li><b>The <code>$X{[BETWEEN], &lt;column_name&gt;, &lt;left_parameter_name&gt;, &lt;right_parameter_name&gt;}</code> 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 <code>[BETWEEN]</code>.</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: <code>(&lt;column_name&gt; &gt;= ? AND &lt;column_name&gt; &lt;= ?)</code>
	<br/>
	If the left parameter's value is null,  
	the function constructs a <code>&lt;column_name&gt; &lt;= ?</code> clause, 
	using the right parameter's value at fill time.
	<br/>
	If the right parameter's value is null,  
	the function constructs a <code>&lt;column_name&gt; &gt;= ?</code> 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 <code>true</code> 
	(e.g. <code>0 = 0</code>). 
	<br/>&#160;
	</li>
	</ol>
	In this sample the query string contains a <code>$X{NOTIN, City, ExcludedCities}</code> piece of code:
	<pre><![CDATA[
    <parameter name="ExcludedCities" class="java.util.Collection"/>
    <parameter name="OrderClause" class="java.lang.String"/>
    <queryString>
      <![CDATA[SELECT Id, FirstName, LastName, Street, City, (Id < 30) as ConditionalField 
      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><![CDATA[
    2010-03-11 13:25:23,300 DEBUG JRJdbcQueryExecuter,http-8080-4:155 - SQL query string: SELECT Id, FirstName, LastName, Street, City, (Id < 30) as ConditionalField 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>
    </content>
  </feature>

  <!-- conditional_styles -->
  
  <feature name="conditionalStylesInTemplates" title="Conditional Styles in Style Templates">
    <description>
How to declare conditional styles in style templates and refer them in the report.
    </description>
    <since>6.20.0</since>
	<otherSample ref="templates"/>
	<otherSample ref="jasper"/>
    <documentedBy>
      <author>
    	<name>Sanda Zaharia</name>
    	<email>shertage@users.sourceforge.net</email>
      </author>
    </documentedBy>
    <content>
<subtitle name="style_templates">Defining Conditional Styles in Style Templates</subtitle>
<br/>
<br/>
The most convenient way to configure a report element appearance is to use the <code>style</code> 
attribute for that element. Any type of report element can use this attribute to reference a report style definition, 
and to inherit all applicable properties declared in that style.  
<br/>
<br/>
In case there are several reports that need to work with very similar (or identical) styles, a good practice 
would be to define those styles only once in an external style template file (*.jrtx), and then refer to this template 
everywhere it is needed. All styles defined in a style template become visible in the report after it is referenced 
via the &lt;template/&gt; tag. For more information about style templates you could take a look at 
<code><a href="../templates/index.html" target="_blank">Templates</a></code> sample.
<br/>
<br/>
In our report sample we can see this style template declaration:
<pre><![CDATA[
  <template><![CDATA["ReportStyles.jrtx"]] ></template>
]]></pre>
In various situations we need to configure elements' L&amp;F depending on certain conditions that will be evaluated at runtime. 
For instance, generating alternate row colors in a table with a large number of rows, or using different text styles and colors 
that depend on the current value of the element. In such cases we need a tool for setting up different style features for the same 
element or cell. Conditional styles are used to solve this problem. They can be defined either in the report itself, or, for some 
particular values, in style templates.
<br/>
For more information about conditional styles defined in reports you could take a look at 
<code><a href="../jasper/index.html#conditionalStylesInReports" target="_blank">Jasper</a></code> sample.
<br/>
<br/>
Starting with JasperReports v.6.20.0 conditional styles can be also defined in a style template file, with respect to the following requirement:
<br/>
<br/>
The <code>&lt;conditionExpression/&gt;</code> used to particularize a style must be a simple parameter/field/variable reference.
<br/>
<br/>
In other words, the <code>&lt;conditionExpression/&gt;</code> is allowed to have one of the following forms:
<ul>
<li><code>$P{parameter_name}</code></li>
<li><code>$F{field_name}</code></li>
<li><code>$V{variable_name}</code></li>
</ul>
Complex expressions are not allowed. And because they represent a condition, the <code>$P{parameter_name}</code>, 
<code>$F{field_name}</code> or <code>$V{variable_name}</code> expressions should be Boolean (or boolean) type. Null 
values are evaluated as Boolean.FALSE.
<br/>
<br/>
<subtitle name="query_sample">Conditional Styles in the Query Sample</subtitle>
<br/>
<br/>
Let's take a look at the content of the external <code>ReportStyles.jrxt</code> template file. We'll see the following 
style definitions:
<pre><![CDATA[
<style name="Sans_Normal" isDefault="true" fontName="DejaVu Sans" fontSize="12"/>
<style name="Sans_Bold" isDefault="false" fontName="DejaVu Sans" fontSize="12" isBold="true"/>
<style name="Sans_Italic" isDefault="false" fontName="DejaVu Sans" fontSize="12" isItalic="true"/>
<style name="RowStyle" style="Sans_Normal">
  <conditionalStyle>
    <conditionExpression><![CDATA[$V{ConditionalRow}]] ></conditionExpression>
    <style backcolor="#EEEEEE"/>
  </conditionalStyle>
</style>
<style name="FieldStyle" style="RowStyle">
  <conditionalStyle>
    <conditionExpression><![CDATA[$F{ConditionalField}]] ></conditionExpression>
    <style forecolor="#0000FF"/>
  </conditionalStyle>
</style>
<style name="TitleStyle" style="Sans_Normal" fontSize="22">
  <conditionalStyle>
    <conditionExpression><![CDATA[$P{ConditionalParam}]] ></conditionExpression>
    <style fontSize="26" isBold="true"/>
  </conditionalStyle>
</style>
]]></pre>

There are 3 static styles in this template, followed by 3 conditional styles:
<br/>
<br/>
The <code>RowStyle</code> depends on the 
<code>$V{ConditionalRow}</code> boolean variable and configures the element's backcolor when the condition is met. It will be used 
in the report to generate alternate row colors in a tabular structure.
<br/>
The <code>FieldStyle</code> depends on the dynamic <code>$F{ConditionalField}</code> boolean field value and configures the element's 
forecolor when the condition is met.
<br/>
The <code>TitleStyle</code> depends on the <code>$P{ConditionalParam}</code> boolean parameter and configures the element's 
font size and font weight when the condition is met. It will decorate the report title in our sample.
<br/>
<br/>
Now let's see how were these styles referred to in the report. First, we referenced the external style template file:
<pre><![CDATA[
  <template><![CDATA["ReportStyles.jrtx"]] ></template>
]]></pre>

Then, we declared a boolean parameter, for instance named <code>ConditionalParam</code>:

<pre><![CDATA[
<parameter name="ConditionalParam" class="java.lang.Boolean">
  <defaultValueExpression><![CDATA[true]] ></defaultValueExpression>
</parameter>
]]></pre>

Then, we have a report query that retrieves at least a Boolean field (here identified as <code>ConditionalField</code>):

<pre><![CDATA[
<queryString>
  <![CDATA[SELECT Id, FirstName, LastName, Street, City, (Id < 30) as ConditionalField 
  FROM Address 
  WHERE $X{NOTIN, City, ExcludedCities} 
  ORDER BY $P!{OrderClause}]] >
</queryString>
]]></pre>

Finally, we have a boolean report variable (named <code>ConditionalRow</code>) that depends on the built-in <code>CityGroup_COUNT</code> variable:

<pre><![CDATA[
<variable name="ConditionalRow" class="java.lang.Boolean" resetType="Group" resetGroup="CityGroup">
  <variableExpression><![CDATA[$V{CityGroup_COUNT} % 2 == 0]] ></variableExpression>
  <initialValueExpression><![CDATA[null]] ></initialValueExpression>
</variable>
]]></pre>

After ensuring the needed parameter, field or variable are present in the report, we just use the <code>style</code> attribute 
of the element to reference the needed style. For instance, for the text element in the title section:

<pre><![CDATA[
<textField isBlankWhenNull="true">
  <reportElement style="TitleStyle" x="0" y="10" width="515" height="35" uuid="f4319415-6741-45b7-b0f1-db7067341070"/>
  <textElement textAlignment="Center">
    <paragraph lineSpacing="Single"/>
  </textElement>
<textFieldExpression><![CDATA[$P{ReportTitle}]] ></textFieldExpression>
</textField>
]]></pre>
<subtitle name="running_sample">Running the Sample</subtitle>
<br/>
<br/>
Running the sample requires the <a href="http://ant.apache.org/">Apache Ant</a> library. Make sure that <code>ant</code> is already installed on your system (version 1.5 or later).
<br/>
In a command prompt/terminal window set the current folder to <code>demo/hsqldb</code> within the JasperReports source project and run the <code>&gt; ant runServer</code> command. 
It will start the HSQLDB server shipped with the JasperReports distribution package. Let this terminal running the HSQLDB server.  
<br/>
Open a new command prompt/terminal window and set the current folder to <code>demo/samples/query</code> within the JasperReports source project and run the <code>&gt; ant test view</code> command.
<br/>
It will generate all supported document types containing the sample report in the <code>demo/samples/query/build/reports</code> directory. 
    </content>
  </feature>

</sample>
