$X and $P!

[Juriy]

Hi guys!

 

I've recently downloaded JasperReports 1.3.2.

Could you please point at some docs or tutorials that explain the usage of $X and $P!. All I've found so far is QueryReport.jrxml. Are there any more docs, samples or specifications?

[sonni]

$X is completely new. So far I know, there are'nt any documentations published.

 

$P! is for accessing parameters in basically. Use $P! if the parameter changes the query at whole (adding a new statement-part, being a table)

 

for example:

select * for atab where $P{asdf};

 

but use ! if

- select * from atab $P!{asdf}; //the query changes basically

- select * from $P!{qwer};

[sonni]

$X is completely new. So far I know, there are'nt any documentations published.

 

$P! is for accessing parameters in basically. Use $P! if the parameter changes the query at whole (adding a new statement-part, being a table)

 

for example:

select * for atab where $P{asdf};

 

but use ! if

- select * from atab $P!{asdf}; //the query changes basically

- select * from $P!{qwer};

[Juriy]

Ok, I guess I get it with $X. Correct me if I am wrong. $X statement may be used only in <queryString> element. The syntax is:

 

$X{NOTIN, ColumnName, ExcludedValues}

or

$X{IN, ColumnName, ExcludedValues}.

 

ExcmludedValues must be a java.util.List object.

 

But what about the "$P!" ?

Is there some kind of _strict_ rules of $P! syntax usage. Or I should just try using $P, and when it isn't working I should try $P! ;-)

 

Thanx in advance.

[sonni]

I' am sorry, but I don't know anything about the usage of X$

 

You use the P!$ in the same way as P$, but in ohter situations:

- only in query Strings

- if it is not posible to use the parameter as prepared-statement parameter of an sql-query

[teodord]

Hi,

 

The $P{} syntax is used for introducing parameter values just like you use the ? in a JDBC prepared statement. They are placeholders for prepared statement parameters.

 

The $P!{} simply expands the value of the parameter within the query text. It is used for dynamically building queries or for providing the whole query text as a parameter.

 

The $X{} syntax is a new addition in JR 1.3.2 and is something in between the previous two. It is a way to dynamically build a part of the query, but also introduce parameter placeholders.

There are two $X{} functions supported by the SQL query executer in JasperReports:

 

$X{IN, colName, paramName}

 

and

 

$X{NOTIN, colName, paramName}

 

paramName should be the name of a report parameter that is either of type Collection or array.

 

This syntax is to be used in order to produce these SQL sequences in the WHERE clause:

 

colName IN {val1, val2, ...}

 

or

 

colName NOT IN {val1, val2, ...}

 

where val1, val2, ... are values in the collection or array parameter mentioned.

 

Check the supplied /demo/sample/query for a working example of this.

 

I hope this helps.

Teodor

[Juriy]

Thanks a lot! That explains everything!

[beekerc69]

sorry, posted in the wrong place, meant this to be in the answers section to continue the thread

[beekerc69]

I understand the $X syntax, but what if the parameter is not a required parameter (one that you can leave blank to mean "include everything"?

for a single entry parameter $P you would use something like.

select <field_list> from <table> where $P{<parm_name>} is NULL or $P{<parm_name>} = ID

lf the parameter input is blank, then all records are returned, if a parameter input is speciried, then only the one matching reccrod is returned.

how would the NULL condition be handled for $X

select <field_list> from <table> where $X{IN, ID, <parm_name>}

if the parameter(s) are specified then every record with that ID will be returned.

how should the clause be coded to handle the condition if the input parameter is empty, implying "return all rows"?

 

would this work or is it something else?

select <field_list> from <table> where $X is NULL or $X{IN, ID, <parm_name>}