multiple sql statements?????

[2005 IR Help]

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