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:
1 Answer:
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".
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".