I am creating a report in Jaspersoft Studio and I have an Oracle pivot query as a subreport which works fine both as a standalone and as part of the larger report when executed within Jaspersoft Studio. The query simply sums the number of absences and lates that a student has accumulated per month and per schoolyear based on the number of periods in which attendance is taken. The query can be tested and viewed here: http://sqlfiddle.com/#!4/a18a7b/13 . That's a bit of a simplified version, but everything essential is there.
The expected (and returned) output from JasperSoft studio looks like (I unchecked "Blank When Null" for the purpose of debugging, here) it should. Please see attached file "Expected output.png"
The problem arises when I upload the report to JasperReports Server. I use the export wizard inside Jaspersoft Studio, and the rest of the report (along with the 12 or so other subreports) come out beautifully. But this subreport behaves very strangely. When I execute the same report from JasperReports Server, the pivot function seems to work, but it appears to completely ignore the aggregate functions (the sums) and returns null where the sums should be. However, it does fill in the "school year" column with the appropriate values. Please see attached file "JasperReports Server output.png"
I should note that the subreport displays correctly if I convert it to a normalized query (so not a crosstab) and the data checks off fine.
I have a feeling it has to do with the aggregate function not working properly somewhere on the JasperReport Server end, but I can't be sure.
PS, just a note that none of the other subreports make use of the Oracle Pivot function.
PPS, here's the JasperReports Server version info:
Product Edition: Professional
Product Version: 5.5.0
License Type: Commercial
And I'm using Jaspersoft Studio 6.1 (as well as have been using 6.0.3 and 6.0.4 before)