Question about Sub report

[rashmijk]

Hi,

We have a J2EE application. For the reporting functionality we are using JReport.

We have a catalog report which is generated bi-annually and contains listing of all the products in the inventory.

The product table has one to many relationship with ingredients and directions.

We have to display all the products with their respective ingredients and directions

We are acheiving this functionality by using sub-report for ingredients and directions.

What this results in is
if there are 3000 products
then the report will be generated using
1 query for Fetching all the Products
3000 queries for fetching the ingredients for each Product
3000 queries for fetching the directions for each Product

In total 6001 jdbc (network) calls.

Is their a better way to do this? How do other Reporting tools handle this kind of situation?

If this is not the right forum to ask this question please point me to a vendor neutral Reporting forum.

Thanks
Rashmi

 

[skuhlman]

Since this is in the Crystal Reports forum, I'm assuming that JReport is somehow connected to or using Crystal.
Unless you have formatting issues that require the subreport setup, in Crystal, you can set a one-to-many relation from the PRODUCT table to the INGREDIENTS table and a one-to-one relation to the DIRECTIONS table. Set up a GROUP on PRODUCT ID#. Put Product information in the Group Header Band. Put Ingredient Information in the Detail Band. Put Direction information in the Group Footer Band.