Dynamic QueryString

[2004 IR Help]

By: Michael Galpin - fupeg

Dynamic QueryString

2005-09-22 13:54

I have a report that needs a filter. So it's basic queryString is something like:

 

select A,B,C from MyTable

 

I've implemented the filter as request parameters to the servlet that generates the report. So if there is a parameter A then I want the queryString to be:

 

select A,B,C from MyTable where A=$P{argA}

 

Similarly if parameter B is present then I want the queryString to be:

 

select A,B,C from MyTable where B=$P{argB}

 

and if both are present:

 

select A,B,C from MyTable where A=$P{argA} and B=$P{argB}

 

Bad Approach 1: Four different compiled reports and application logic to determine which one to run

 

Bad Approach 2: One compiled report. Application logic to compute the where clause that is then passed in as a parameter $P!{whereClause}

 

Is there a better approach? Maybe a way to put a scriptlet in my report to compute the where clause or something even easier?

 

 

 

 

By: C-Box - c-box

RE: Dynamic QueryString

2005-09-22 23:09

Why don't you create the WHOLE query string from "select till where-clause" in your application and pass it via parameter!?!?

 

The parameter must be used with the exlamation mark "!" when using it within/as a query. But you did so... what error occurs? Exception? No data? Wrong data?

 

hth

C-Box

 

 

 

 

By: Michael Galpin - fupeg

RE: Dynamic QueryString

2005-09-23 15:29

I do not get an error with either approach. Both work. Both seem like hacks though, so I was hoping that there was some better/easier way that I did not know about.

[manu10184]

hi folks....

         i ve stuck in some  prob...... the thing is,  i have used runtime parameter by whereclause... in ireport it  ll get filter while i publisng in jasperserver it would'nt get filter.... i.e in ireport i use prompt & parameter name customer,   i pass customer='tcs'  in i report it ll wrk fine.... similarly while publishng in jasper i pass parameters as customer='tcs'... it wouldnt get filter instead it ll display all customers.... so plz if any1 can look up this n reply to me asap............

 

 

bye