Multiple SQL Queries for one report?

[2005 IR Help]

By: Andy Wadsworth - fritztdog

Multiple SQL Queries for one report?

2004-08-10 15:37

I'm using Jasper Reports 0.5.2 and iReport 0.2.3.0.

I'm wondering if it's possible to create a report that displays

the results of two similar, but unrelated tables in separate

detail bands. My desire is to have two groups with

independent detail bands that have their content driven by

separate SQL query results.

 

For example, assume I have a restaurant and I want to

generate a report showing the number of beverages and

food items that were purchased today. The beverage query

is something like:

select * from beverage where purchasedtoday>0;

 

The food query might be:

select * from food where purchasedtoday>0;

 

What I want to do is generate a single Jasper report that

displays something like the following...

 

Food item Number Cheese Onion Pickle Tomato

Hot Dogs 121 79 94 18 3

Hamburgers 94 53 82 85 71

Chicken 62 18 44 11 52

Fries 183 27 0 0 0

 

Beverages Number Small Medium Large Ice

Coca Cola 174 38 120 16 151

Sprite 48 27 14 7 37

Jolt 3 0 0 3 3

 

Keep in mind that this is just an example to illustrate what I

want my report to look like. So, whereas I might be able to

modify the database schema driving the food/beverage

example to achieve my goal, I do not have this luxury with

my real application.

 

Thanks for your help.

 

Andy Wadsworth

BondMart Technologies, Inc.

 

 

 

 

 

 

By: Richard Barnett - richard_barnett

RE: Multiple SQL Queries for one report?

2004-08-10 18:05

One way (possibly suboptimal) is:

* use a "custom" datasource over a list of maps

* put all rows from both queries into the datasource in the correct order

* add some property to each record to indicate whether it's a beverage or food item

* use the beverage/food indicator to switch groups & to dynamically pick which field or heading to use.

 

Another is to use a "heading" record in the datasource to generate the right column headers for each group (ie only use JasperReport's grouping to add whitespace) but then your ds records will have to have properties called 'param1', 'param2', etc -- more of a problem if you want different formats in the same column.