parameters not being sent to subreport

[jvway]

Hi all,

(iReport 4.8)

I'm trying to pass prompted parameters to a subreport but they don't seem to be getting there.  These are parameters that I'm prompting for when running the main report.  I have them defind in the parameter map in the subreport properties on the main report.  I then have these same parameters defined inside the subreport and in the subreports query.

But when I run the main report iReport errors out and shows me ? marks in the place of the parameters in the iReport output window.  So it's like the parameters aren't getting passed to the subreport.

Error filling print... net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query :   SELECT     Sum(vw_TimeTracker_Oppt.`OpptTotal`) AS SumOpptTotal,     Sum(vw_TimeTracker_Oppt.`ProjGross`) AS SumProjGross,     Sum(vw_TimeTracker_Oppt.`ProjForecast`) AS ProjForecast,     Sum(vw_TimeTracker_Oppt.`CombinedTime`) AS SumCombinedTime,     Sum(vw_TimeTracker_Oppt.`RateCalc`) AS SumRateCalc FROM     `vw_TimeTracker_Oppt` vw_TimeTracker_Oppt WHERE     Left(vw_TimeTracker_Oppt.`SolutionName`, 4) = ? AND     $X{IN, vw_TimeTracker_Oppt.`OppType`, ?} AND     (vw_TimeTracker_Oppt.`EndDate` >= ? AND     vw_TimeTracker_Oppt.`EndDate` <= ?) AND     (vw_TimeTracker_Oppt.`Posted` >= ? AND     vw_TimeTracker_Oppt.`Posted` <= ?)

Attached are the two files.

Thanks in advance,

Jerry

[jvway]

Oh, looks like it was because I hadn't compiled the subreport on its own yet.  I usually do that.  I just got cocky this time.  whoops.

[jvway]

Nope I was wrong.  I guess I didn't refresh the data.  After closing iReport and retrying the report I get the same problem as above.  Still need help on why this isn't working when trying to pass prompted paramters to a subreport.

Thanks,

Jerry

[lucianc]

The question marks stand for prepared statement parameters, the values are not directly included in the query text.

One problem with the query is $X{IN, vw_TimeTracker_Oppt.`OppType`, $P{OpptTypeSelect}}.  The last token should be OpptTypeSelect, you should not use $P{..} in this context.

If this doesn't fix the query completely, please post a full exception stack trace.

HTH,

Lucian

[jvway]

I see what you're saying about the $P but in that context it is the subreport and I'm trying to retrieve the parameter value that was entered into the main report. Not sure how else to retrieve those values. In past subreports I think I've used the field data from the main report but this parameter data isn't saved in any fields on the report. I suppose could make them hidden fields if I need to use it that way.

 

Thanks for the response.

[jvway]

I notice near the end of the error output there is this but I'm not sure what it's referring to:

Caused by: java.sql.SQLException: Parameter index out of range (6 > number of parameters, which is 5).

 

I mean I guess it's saying I'm only sending it 5 parameters and it's expecting 6. But all parameters are filled with values since the main report runs fine without the subreport. Hm, I may change tack here and try storing the parameters in fields. That's what I've done in the past and seems to work. Just seems like an unnecessary step.

[jvway]

Lucian,

So your comment that OpptTypeSelect being in the IN statement did make the report funciton but it is not respecting the parameter, so my subreport isn't filtering the way I would expect. I've got some more trouble shooting options but thought I'd pass this on.

[jvway]

So, I guess my question comes down to, how do I pass a collection as a parameter to a subreport. I think that's what's missing in terms of the index out of range error. It's getting nothing for the IN statement.

[lucianc]

You can enable debug logging for net.sf.jasperreports.engine.query.JRJdbcQueryExecuter and it will log the query and parameters. That should give you a quick hint on what the problem could be.