Creating multi tab excel report with summary, groups, and details tabs

[RICE]

Brand new to Jasper and SQL.   I'm trying to create two seperate reports (a management report and individual group report).  Each will be a single excel file with mulitple tabs.   I'm not sure where to even start.  I have the SQL created to pull the various details by group but I'm not sure how to seperate out onto multiple tabs and use parameters.

management report

The first tab will be summary tab with charts and counts, percentages, etc accross all groups.

tabs 2 - 5 will be specific to unique groups

tab 6 will be data dump of all the details.

MGT REPORT
tab 1  - all fruit (summary)
FRUIT	COUNT	%
apples	5	36%
banannas	3	21%
oranges	2	14%
kiwis	4	29%
TOTALS	14	100%
tab 2 - apples
apples	5
tab 3 - banannas
banannas	3
tab 4 - oranges
oranges	2
tab 5 -
kiwis	4
tab 6 - details tab
fruit	color	size	price	etc	etc
apple	red	2	1.99
apple	green	3	1.99
apple	green	4	1.99
apple	yellow	4	2.99
apple	red	4	1.99
banannas	yellow	7	0.98
banannas	yellow	7	0.98
banannas	yellow	7	0.98
oranges	orange	3	1.5
oranges	orange	3	1.5
kiwis	green	2	3.5
kiwis	green	2	3.5
kiwis	green	2	3.5
kiwis	blue	2	17

 

Group report

First tab is just display group detail

second tab will display data dump for group detail

 

2ND REPORT
tab 1 - oranges
oranges	2
tab 2 - details tab
fruit	color	size	price	etc	etc
oranges	orange	3	1.5
oranges	orange	3	1.5

 

 

 

 

[szaharia]

Hi,

First of all, to get multiple tabs in an Excel file, you need to export your report as 'one page per sheet' document. To do so, you have to check the 'One Page per Sheet' export option in JSS, or, if you prefer so, you could set the 'net.sf.jasperreports.export.xls.one.page.per.sheet' property as true in your JRXML main report.

Now, there are several ways to obtain an output layout like the one you described here. For instance:

1. For each tab in your report create a related subreport to represent data (therefore a total of 6 subreports for the first report and a total of 2 subreports for the second report). Place them in the same band of the main report (for instance in the summary band), and separe them with <break/> elements. Don't forget about the 'one page per sheet' option that should be set to true. Export the report.

An example of using subreports can be consulted here.

2. Another possibility is to generate the report as JasperReports book, based on report parts (similar to subreports, but replacing the usual <band/> structure in a report). Some guidance about using report parts is available here and here. Distinct report parts are by default exported in distinct tabs in Excel.

I hope this helps,

Sanda

[ivos.apolenar]

Another solution for create two (or more) separated (independed) sheets withing one XLS(X) file is use two separated jrxml templates. This is IMHO the best solution for simplitance.

More templates cannot affected each other so in case the XLS(x) this approach provide you full control over each sheet desing. The desings can be completely different without any interferences each other.

With page break aprroach in single template I have problem with design - width of columns affected with other sheets (ugly non regular grid, cased cell merging).

Here I attach example code snipped.

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

ClassLoader classloader = Thread.currentThread().getContextClassLoader();var report1 = JasperCompileManager.compileReport(new FileInputStream("/test1.jrxml"));var report2 = JasperCompileManager.compileReport(new FileInputStream("//test2.jrxml"));JasperPrint jasperPrint1 =        JasperFillManager.fillReport(report1, new HashMap<>(), new JRBeanCollectionDataSource(List.of(new Person("John1", "Doe1"), new Person("Foo1", "Bar1"))));JasperPrint jasperPrint2 =        JasperFillManager.fillReport(report2, new HashMap<>(), new JRBeanCollectionDataSource(List.of(new Person("John2", "Doe2"), new Person("Foo2", "Bar2"))));var exporter = new JRXlsExporter();exporter.setExporterInput(SimpleExporterInput.getInstance(List.of(jasperPrint1, jasperPrint2)));exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(new FileOutputStream("/test-output.xls")));var configuration = new SimpleXlsExporterConfiguration();exporter.setConfiguration(configuration);exporter.exportReport();

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

I hope this can help someone to prevent any stuck as I had.
Ivoš

Doufám, že to někomu pomůže, aby zabránil zaseknutí jako já.