$X{} clause - define columnName as an expression

[prehlb]

I have a challenge for those using the $X{} clause in SQL environments.

The definitive guide (Third Edition) has the following definition for the $X{} clause:
 

JasperReports has built-in support for two clause functions: IN and NOTIN. Both
functions expect two parameters:

• The SQL column or expression to be used as the left side in the IN/NOT IN clause.
• The name of the report parameter that will supply the values list. The value of this parameter can either be a java.util.Collection instance or an object or primitive Java array.

 

Please note the bolded text: "or expression".  I bring this up because I have an interest in having the actual SQL column name be wrapped in a function for checking against the collection for matches.

For example, say we have courses with course ID's.  One column name is COURSE_ID.  But, some of the course ID's begin with certain characters, say "T", "K", "U" and "A" to define say a type of course.  The report on courses might need to break them into two subreports - one for types T and K and the other subreport for U and A.

Now, the SQL query would normally look like:

1. SELECT COURSE_ID, COURSE_NAME FROM COURSE WHERE $X{IN,COURSE_ID, CoursePrefixes}

The parameter $P{CoursePrefixes} is defined as a java.util.Collection and contains ['T','K'], which are the first characters to match against in the COURSE_ID field.

The problem above is that it is not looking at just the first character of the COURSE_ID field, it's looking at all the characters which is not what I need it to do.  I need to have the SQL IN Clause resolve to:

WHERE LEFT(COURSE_ID,1) IN ('T','K')

which means the query should look like:

2. SELECT COURSE_ID, COURSE_NAME FROM COURSE WHERE $X{IN,LEFT(COURSE_ID,1), CoursePrefixes}

But query 2 fails with syntax issues around the "1)".

So, my question is how would you specify an "expression" for a columnName in the $X{} clause such as above?

I have come up with work-arounds such as using MS SQL CTE's and such, but I wanted to know if there's any documentation for how to formulate an expression for the columnName.

Thank you,

Bill

[lucianc]

You'll have to use a different separator for $X{..}.  The separators supported by default are , ; |

$X{IN;LEFT(COURSE_ID,1); CoursePrefixes} should work.

Regards,

Lucian

[Voltrone]

iReport designer automatically replaces ; separators with , when you save the query so this workaround doesn't seem to work.

[cjasper]

yes you are right the ; is not working but the bar | is working.I tested this with postgres

[jerome.parot]

Nop, this workaround doesn't work with postgres. I tested the | or ; syntax and jasper still replaces it with ,

Hope someone can help me doing this.

 

Regards,

[palee]

Has anyone solved this? I have similar problem.

[ktalarico]

I am having same problem. Would love to know if there is a solution.

[sunilkumarsheoran]

used simple text editor to edit the query within the .jrxml file as suggested (not using IReport Designer's query editor which was the one who replaced even after editing ';' or '|' with ',' ) and it worked.

for example:

select * from table where $X{ IN ;(setup_id,lead_id);_PARAMETER_LEAD}

 

may be i am using old IReport.

 

[AK777]

this comments helped in my case... 11 years after you have posted it! cheers