Map Bind Variables to Sub Reports with a Dynamic Where Clause

[geethapriya1987]

Hi,

I have a parent report with a dynamic where clause, which is passed as $P!{WHERE_CLAUSE}, and the where clause in turn contains $P{} parameters

Eg: $P!{WHERE_CLAUSE}=> where docno between $P{P_FROMDOCNO} and $P{P_TODOCNO}

Values for $P{P_FROMDOCNO} and $P{P_TODOCNO} are also passed in the same execution

 

The parent report has a subreport, that summarizes based on certain category, whose query is based on the parent's where clause.

select vat,sum(amt) from tablename $P!{WHERECLAUSE} order by vat

So the where clause is passed to the sub-report using parameter mapping. But the subreport fails to read $P{P_FROMDOCNO} and $P{P_TODOCNO} values from the parent, resulting in empty document. All the above parameters have been mapped to the subreport parameters. The main goal is to fire the query in the database with bind parameters, even for the sub reports.

Kindly let me know if any solution for the above.

 

Thanks in advance,

Geetha

 

[sanbez]

Order of subreport parameters is important in this case. Is $P{WHERECLAUSE} placed after $P{P_TODOCNO} and $P{P_FROMDOCNO}?
Can you show subreport parameters definition (part of subreport.jrxml)?

[geethapriya1987]

Hi Sanbez,

The order is as you said, the where clause is defined before the From and To docno in the subreport as below.

 

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

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

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

 

[geethapriya1987]

Closing this question as the parameters are working fine. What I had was a data issue. Thanks!