How to change SQL of subreport

[edilmar]

Hi,

 

I have Jasper + iReport 1.3.0.

Then, my report has a subreport with a SQL that I'd like to change dinamically. I have a dialog with many kinds of filters, some of them I use to change the SQL for Statement/ResultSet into main report, and some filters are to change SQL for subreport. But I don't know how to change SQL for this one dinamically.

Thanks for any help...

[wegnerk]

How does your SQL-Statement looks like? Perhaps you can give the subreport a parameter which contains the query or some dynamic information.

 

but it would be easier if one could see your query.

[edilmar]

The problem is that the SQL may change some filter (AND) clausules. I have five filters, that users may use or not, and combine some of them. Then, the SQL may be:

 

Code:

select ... from ...
where filter1
and filter2

or

select ... from ...
where filter1
and filter2
and filter3

or

select ... from ...
where filter2
and filter4
and filter5

or

etc...

 

There is no need to use all filters togheter. When this occurs in main report, it is easy to solve, I only pass the ResultSet with any SQL code generated in Java code. But the problem is: I don't know how to pass a SQL (neither a ResultSet) to the subreport.

[slaisne]

in your subreport:

 

<parameter name="QUERY" class="java.lang.String"> </parameter>

<queryString>$P!{QUERY}</queryString>

 

in your main report:

send the correct sql statement in the QUERY parameter in the subreport object

[edilmar]

Thanks for your help.

If I put something like this in the subreport SQL:

select ... from ...

where $P{filters}

then iReport 1.3.0 says that the SQL is wrong.

Does this work in running time?

[slaisne]

May you post main report and subreport, because when you send query in your sub-report, all parameter must be replaced.

[toledo05]

I'm not 100% sure this is what you are looking for but it may help. Using a query like:

 

Select * from myTable

where field1=something

and field2=somethingelse

 

If what you want is to control the where statement and only test field1 sometimes, field2 sometimes and both fields some other time you can try an IF statement within the WHERE statement.

 

Set up a pair of parameters:

$P{Field1}

$P{Field2}

 

Select * from myTable

where

if (length($P{Field1}) = 0, true, if ($P{Field1} = "something", true, false))

and

if (length($P{Field2}) = 0, true, if ($P{Field2} = "somethingelse", true, false))

 

 

Basically, if the length of the field is zero true is returned so the field is not tested. If the length is greater then zero, the value is tested and the result of the test is used.

 

You can extend this a little further by passing another couple of params if a length of zero is something you want to test. Instead of testing for length = 0 you the another parameter say: $P{TestField1} = "Y" and $P{TestField2} = "Y" the sql statement would be:

 

$P{TestField1}

$P{TestField1}

$P{Field1}

$P{Field2}

 

Select * from myTable

where

if (length($P{TestField1}) = "Y", true, if ($P{Field1} = "something", true, false))

and

if (length($P{TestField2}) = "Y", true, if ($P{Field2} = "somethingelse", true, false))