Report query datasubsets crosstabs temporary table

[contentengineer]

We're converting an existing report that uses SQL to create a grouped "pivot" table which is then quickly manipulated using a single grouped report in iReport/Jasperreports.

 

(i.e. The SQL generates a temporary table with each row has the following columns:

GroupTitle|GroupLine|Month1Revenue|Month2Revenue ... |TotalRevenue)

 

to produce the follwing report:

 

Month|Month|Month| ...|Total

Group by Customer

Customer1

Customer2

Customer3

 

Group by Category

Category1

Category2

Category3

 

Group by Type

Type1

Type2

 

Group by CostCentre

CostCentre1

CostCentre2

CostCentre3

 

---

We want to migrate to crosstabs using basic underlying transaction data in a temporary table which is created from a join of six tables in a single report query.

 

Month|Customer|Category|Type|Revenue|CostCentre

 

---

Can we use report subdatasets to re-use the main query?

 

How can we cache the results from the temporary table; and re-use for each crosstab?

[contentengineer]

We have attempted to create crosstabs using iReport wizard... and get the following error:

 

ErrorÂfillingÂprint...ÂErrorÂincrementingÂcrosstabÂdataset

net.sf.jasperreports.engine.JRRuntimeException:ÂErrorÂincrementingÂcrosstabÂdataset ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillCrosstab$JRFillCrosstabDataset.customIncrement(JRFillCrosstab.java:654)