Table summarizing totals

[fabridp]

Hello.

I'm building a table report. I grouped this table by the field X and in the group footer I made totals.

PRICE GROUP

100      1

200      1

TOTAL 300

100     2

TOTAL 100

______________________________________________________________________________________

Now, i want make something different like this:

PRICE GROUP

100      1

200      1

100     2

 

 

TOTALS TABLE

GROUP TOTAL

1             300

2             100

________________________________________________________________________________________

How can I do that with Jasper-iReport considering i want to output a list of element PRICE/TOTALS?

___________________________________________________________________________

DOCUMENT 1

PRICE GROUP

100      1

200      1

100     2

 

TOTALS TABLE

GROUP TOTAL

1             300

2             100

 

DOCUMENT2

 

PRICE GROUP

100      1

100      1

200     2

 

TOTALS TABLE

GROUP TOTAL

1             200

2             200

.....

___________________________________________________________________________________

Thanks in advance.

Fabrizio

Post Edited by Fabrizio Dal Pozzo at 03/05/09 08:46

[kchaudhry]

Have you tried creating a group on the field that defines "DOCUMENTS" and putting the totals in that group footer?

[fabridp]

I simplified the question but the real case in "code" is effectively different. I create three nested groups

DOCUMENT TYPE

|

-DOCUMENT

|

-ALIQUOTA

 

Totals are displayed when the value of group "Aliquota" change. Now i display totals between document rows but i don't want it instead summarizing totals in a separate footer table with the format used in unwanted case reported.

 

[silvios]

I suppose Your query looks something like

SELECT x, PRICEfield, GROUPfield

  FROM sometable

 WHERE somefield = $P{someparameter}

And in group footer of x you want to have

SELECT GROUPfield, SUM(PRICEfield) AS TOTAL

  FROM sometable

 WHERE somefield = $P{someparameter}

   AND x         = $F{x}

 GROUP BY GROUPfield

That's totally different query, you cannot have two datasources in Jasper

... but what you can do is to create subreport that can have it's own query.

Just create another report, named for example "YourSubReportName.jrxml".

Your subreport should receive all parameters as master report plus parameter

for value of current 'x' (because there is additional AND in where clause).

It should have query with GROUP BY clause as I wrote it above.

When subreport is finished and compiled, return to master report, draw

subreport object in field 'x' group footer. Fill subreport object properties

set 'Connection Expression' attribute = $P{REPORT_CONNECTION}

set 'Subreport  Expression' attribute = "YourSubReportName.jasper"

map subreport input parameter with values from master report like

NAME          EXPRESSION

someparameter $P{someparameter}

xparameter    $F{x}

... or whatever

And that's it.

[fabridp]

Many thanks Silvios! You have been exhaustive.

Unfortunately I don't have permissions to make direct query on DB.

Presently I must use the JRBeanCollectionDataSource to which pass an ArrayList of objects.

-------------

JasperDesign jasperDesign = JRXmlLoader.load(JASPER_REPORT_FOLDER + reportFileName + ".jrxml"); JasperCompileManager.compileReportToFile(jasperDesign, JASPER_REPORT_FOLDER + reportFileName + ".jasper"); JRDataSource lJRDataSource = new JRBeanCollectionDataSource(documents); JasperPrint jp = JasperFillManager.fillReport(JASPER_REPORT_FOLDER + reportFileName + ".jasper", null, lJRDataSource); ...

-------------

How can I feed subreport in this bean-context?

Fabrizio

Post Edited by Fabrizio Dal Pozzo at 03/06/09 08:33