2005 IR Help Posted August 26, 2006 Share Posted August 26, 2006 By: jasper_new - jasper_new multiple sql statements????? 2004-08-16 09:40 Hi everybody. I am trying to create a statistic report that gets data from several tables. As a result a must have a table where the columns are created according the radio button choice, the same with rows. Ex.: ---------Type1-----Type2-----Type3-----Total Level1--- 5 ------- 6 ------- 1---------12-- Level2---- 6 ------- 12 -------- 4 ---------22-- Leve3---- 5 ------- 0 --------- 3 -------- 8--- Total ----- 16 ------ 18 -------- 8-------- 42 Here Type1, Type2, Type3 - are the radio button choice of the field Type in DB. And Level1, Level2, Level3 are the radio button choice of the field Level as well. Type and Level are in different DB tables and must be calculated according the identical ID. Is it possible to create a report like this using JasperReport and iReport? I cannot figure out how to make groups in columns and totals in all directions. Thanks. By: Richard Barnett - richard_barnett RE: multiple sql statements????? 2004-08-17 03:55 I don't fully understand the query/data you've described, but it sounds relatively complex, and AFAIK JasperReports can only group in one "dimension" (both in terms of page layout & totalling). If you can formulate a SQL query which returns the data from the non-total rows (level1, level2, etc) then it should be fairly easy. Otherwise perform multiple queries & add a simple bean for each row to a JRBeanCollectionDataSource -- I don't know whether you could run such a report from iReport. By: jasper_new - jasper_new RE: multiple sql statements????? 2004-08-17 06:54 hi, Richard. This is sql statement I put as a report query: select table1.type, table2.level, table1.id from table1, table2 where table1.id=table2.id order by table2.level, table1.type. The best result I got using iReport was: Level 1 Type ------ Type 1 ---------------3 Type ------ Type 2 ---------------4 Total ----------7 Level 2 Type ------ Type 1 ---------------2 Type ------ Type 2 ---------------1 Total----------3 Level 3 Type ------ Type 1 ---------------4 Type ------ Type 2 ---------------1 Total----------5 But I need a result view to be as I posted. Do you have any ideas if it's possible to do? By: Richard Barnett - richard_barnett RE: multiple sql statements????? 2004-08-17 19:45 You'll need to poke around the more data-warehousey parts of your DB to find out if there's some way you can get the results you want in a single query. Otherwise, a SQL query like select table2.level, count(table2.id) from table1, table2 where table1.id=table2.id and table1.type='type1' group by table2.level will give you the first column of your desired table. IMO it's very worthwhile playing with datasources which don't just return a single SQL query; this will allow you to generate reports aggregating complex data from multiple sources/queries, formatted in whichever way you choose. By: jasper_new - jasper_new RE: multiple sql statements????? 2004-08-18 11:32 Do you think I can create a "view" from several tables and then use it for the report? Natasha By: Richard Barnett - richard_barnett RE: multiple sql statements????? 2004-08-18 21:22 You might be able to create a view such that you could query it directly from your report to get the data you need. Unfortunately I'm no SQL guru & don't have the time to work out how you'd do it in your case. Hmm, a view something like select t2.level, 1 as type1, 0 as type2, 0 as type3, 1 as total from t1, t2 where t1.id=t2.id and t1.type='type1' union select t2.level, 0 as type1, 1 as type2, 0 as type3, 1 as total from t1, t2 where t1.id=t2.id and t1.type='type2' union select t2.level, 0 as type1, 0 as type2, 1 as type3, 1 as total from t1, t2 where t1.id=t2.id and t1.type='type3' (Ugh! Brittle ugly query, relies on you knowing all the values for t1.type. Maybe SQL gurus can show a much more elegant way of doing that?) Then a report query select level, count(type1) as type1, count(type2) as type2, count(type3) as type3, count(total) as total from viewname group by level should be pretty close. By: jasper_new - jasper_new RE: multiple sql statements????? 2004-08-19 13:34 It works now! Thanks a lot for your help! Natasha 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