I have a requirement to process two or more datasets, with the results being a combination of the records and fields. Consider the following bill-of-material, which is extracted from the data warehouse using Oracles 'CONNECT BY' select statement.
| Rownum | Level | Parent | Child | Quantity |
|---|---|---|---|---|
| 1 | 1 | Part 1 | Part 2 | 1 |
| 2 | 2 | Part 2 | Part 3 | 1 |
| 3 | 3 | Part 3 | Part 4 | 2 |
| 4 | 4 | Part 4 | Part 7 | 2 |
| 5 | 1 | Part 1 | Part 5 | 1 |
| 6 | 2 | Part 5 | Part 6 | 1 |
The processing I need to do is:
- If you find a level 2 part of a particular name, report the next part at level 4 ( you do not know know how many level 3 parts will be below the level 2 part)
- If you find Part 2 in the dataset, report the item of a particular type two levels below (Part 2 can be anywhere in the heriarchy)
The Rownum field can be used to iterate over SQL returned results.
How, within a java or scriptlet class, can you get all the returned records, iterate according to lots of rules that involve forward- and backward- referencing relative to a row,
and output so that the iReport designer just works with one or more rows of data?
As a further challenge, I could have mulitple output datasets from multiple sub reports, and the processing occurs across all the data sets.
ie: If you find Part 1 in dataset 1, report all level 2 parts in data set 2; else report only level 1 data in the third data set.
Note:
I cannot query directly on the data ware house using java or similar, as only the iReport 'Report Datasource' is provided by the central IT resources; I just use it within iReport. The above problem is simple if I could directly access, via JDBC within Java, the data warehouse.
I must use iReport and JasperServer as this is the corporate standard. If I was using BIRT, then I would use the beforeOpen, beforeClose, onFetch, open and fetch scripts to process the data
JasperServer provides the ideal way to utilize other formats (PDF, XLS...) rather than hand code.
Thanks in advance
