report parameters not appearing in dataset query of subreport table

0

I have a table I'd like to include multiple times in my main report but with variations on the data for the dates and region

I thought it best to create the table as a subreport and pass the date and region values as parameters so that I can change the whole report layout by just altering the subreport in one place.

So I've created a subreport sized to fit just the table which contains a completed count for the region, a total count for the region and a region ID.

I've created parameters in the sub report for myRegion and myYear, both of which are integers.  myYear currently defaults to 

Calendar.getInstance().get(Calendar.YEAR)

I've created text fields in the report and in the table that have values of the respective parameters. When previewing the report the correct parameters show. The next step was to put these parameters into the dataset query for the table so that the data corresponds to the year chosen/ passed as a parameter

so in my query for the table dataset the string

WHERE (mytable.STARTED < "2015-04-01" AND (mytable.ENDED > "2015-01-01" OR mytable.ENDED = "0000-00-00")) AND mytable.region = "30")

I edited it to become

WHERE (mytable.STARTED < "$P{myYear}-04-01" AND (mytable.ENDED > "$P{myYear}-01-01" OR mytable.ENDED = "0000-00-00")) AND mytable.region = "30")

but it fails to become

WHERE (mytable.STARTED < "?-04-01" AND (mytable.ENDED > "?-01-01" OR mytable.ENDED = "0000-00-00")) AND mytable.region = "30")

with a java error of

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:
 
...<snip query>...
 
where (mytable.STARTED < "?-04-01" and (mytable.ENDED > "?-01-01" or mytable.ENDED = "0000-00-00")) and mytable.region = "30")
 
...<snip query>...
 
Caused by: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:
I'm jumping in at the deep end here with this being my first report in Jaspersoft Studio linked to a mysql data set so any hints or suggestions would be appreciated.
cjohnsonuk's picture
Joined: May 20 2010 - 1:41pm
Last seen: 4 years 5 months ago

OK I got it working! :-)

It looks like parameters in the dataset query don't need the double quotes (") around the them.

I created a new parameter that concatenated the month and day of month to the report year parameter that the user is prompted for then put this new parameter in the report "without quotes round it".

 

 

cjohnsonuk - 4 years 5 months ago

1 Answer:

0

OK I got it working! :-)

It looks like parameters in the dataset query don't need the double quotes (") around the them.

I created a new parameter that concatenated the month and day of month to the report year parameter that the user is prompted for then put this new parameter in the report "without quotes round it".

cjohnsonuk's picture
Joined: May 20 2010 - 1:41pm
Last seen: 4 years 5 months ago
Feedback