Use Parameters and Variables in WHERE of Query?

[incirlikhodja]

I have two questions

1.  I am trying to use a parameter of a Jasper report in the following manner

($P{TimeKeeper} = 'ALL'  OR tk.tk_id = $P{TimeKeeper}

The parameter $P{TimeKeeper} is defined as a type String.  However, this statement gets a syntax error on the part

$P{TimeKeeper} = 'ALL' Saying that 'ALL' is a VARCHAR and that $P{TimeKeeper} is bit.  Is this not allowed??

2.  I have a Jasper Report variable that I would like to use to further filter my result set in the query.  The variable has a rather lengthy calculation that involved a call to a scriptlet.  However, I would still like to use the variable to further filter in my query.  Can this be  done?

Post Edited by incirlikhodja at 05/20/2010 20:33

Post Edited by incirlikhodja at 08/10/2010 19:20

[itchytoes]

Hi --

 

What type is your TimeKeeper parameter?

Also, you must remember to use the bang if the parameter is within the query string.

i.e. ($P!{TimeKeeper}='ALL' OR tk.tk_id = $P!{TimeKeeper}

 

You should be able to pass in TimeKeeper as a string and it would be okay.

 

Regarding 2. I'm not sure I understand your question. I don't know that you can use a variable in your query, but I have used a <parameter> in my query where the parameter was derived from another parameter by call some java code.

 

 

[incirlikhodja]

1.  Making the parameter syntax change helped.

2.  An example of what I am trying to do:  Let's suppose that I have a variable called cnvtsumtotal that had some long complicated java code to compute it and I resorted to calling a scriptlet that contained that code.  THEN I wanted to filter the rowset...comparable to a WHERE $V{cnvtsumtotal} > 0.  I realize that I cannot do this in the SQL, correct?  I was wondering if there was someplace else in the Jasper report where I could do this subsequent filter on the row set. 

 

[itchytoes]

Hi --

Okay. I think I get what you are doing.

I think I am unclear as to what your variable computation takes as input. If is based on an input parameter, you can create a second input parameter that is computed using the first one, then pass that along to the query.

 

On the other hand, I've had cases where I've had to "massage" my data from a query, which included some fancy filtering and other types of re-arrangement because the SQL code could not return it the way I wanted it. I'm not sure that this is the best or fastest way to do things, but I generally would have a top level report do the query and add the rows into a HashMap or something like that (using a scriptlet method) then I would call a subreport at a group footer or something where the datasource is a JRMapArrayDataSource, and I use a scriptlet method to obtain this new datasource by massaging the data and filtering and whatever. This might not be practical if your report deals with tons of data rows, but it works for me. Other users might have better suggestions.