2005 IR Help Posted August 26, 2006 Share Posted August 26, 2006 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now