multiple sql statements?????

0
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
2005 IR Help's picture
Joined: Aug 9 2006 - 3:40am
Last seen: 7 years 8 months ago

Answers:

No answers yet
Feedback