Oracle Pivot Query returns null in JasperReports Server

[henryr]

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 here. That's a bit of a simplified version, but everything essential is there.

The expected (and returned) output from JasperSoft studio looks like this (I unchecked "Blank When Null" for the purpose of debugging, here) : 

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. 

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. 

Now, I don't want to use a Jaspersoft Studio crosstab for the simple reason that this subreport should really not be displayed in the summary, for organisational purposes (and based on my research and experience, it can only work in the summary band. I'm wrong¹). Just as well, I am very reluctant to let this be something other than a crosstab because if it is displayed as a normalized query, this section alone would take up about 10 pages for the older students (besides being difficult to read as reports go). 

 

Like I said, 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 think of an appropriate workaround. If anyone has any insight on this matter, it would be greatly appreciated.

¹ So Jasper crosstabs are a bit less odd than I originally thought, but the original question still remains. Why would an Oracle Pivot return nulls in JasperReports Server but display the correct results in Jaspersoft Studio?

 

Thanks

Roxanne 

PS, this is cross-posted to Stack Overflow. I'll keep this updated in the event of updates.

PPS, just a note that none of the other subreports make use of the Oracle Pivot function.

PPPS, here's the server 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)