database reports/ireport
I'm trying to create a report using iReport but I can't get the right results printing out.
As a generic example say I have three tables:
table_1{
id
field_
}
table_2{
id
fieldA
table_1_id
}
table_3{
id
field1
table_2_id
}
where table_2.table_1_id references table1.id and table_3.table_2_id references table2.id.
For every occurance of an entry in table2 there may be 0 or more entries in table3 where table3.table_2_id = table2.id.
Let's say I had the following in the tables
table_1
idField_
160
230
377
table_2
idfieldAtable_1_id
1qq1
2rr1
3tt2
table_3
idfield1table_2_id
1a11
2a21
3a33
Now, in my report what I want to do is print out some details for a specific table_1.id.
Specifically, I want to print out contents of table2 where table2.table_1_id = table_1.id,
and for each entry being printed from table_2 I want to print below it the entries in
table_3 where table_3.table_2_id = table_2.id.
For example, say I wanted to print the above where table_1.id = 1 I would like the result
printed in my report as follows:
table_2.fieldA: qq
table_3.field1: a1
table_3.field1: a2
table_2.fieldA: rr
I'm wondering how I should design the report? What I mean is should I use groups or a
sub-report? I've tried both, I don't even get close to what I want with my subreport,
with groups I get close but I get every entry from table_3 repeated under each of the
entries from table_2, so in the above I would get:
table_2.fieldA: qq
table_3.field1: a1
table_3.field1: a2
table_2.fieldA: rr
table_3.field1: a1
table_3.field1: a2
I'm at a loss and not that familiar with databasing and reporting. Also, what query would
I need?
Thanks in advance,
Lionel.
[2183 byte] By [
Lionelva] at [2007-10-2 14:44:40]
I had created a couple of reports long time back(crystal reports).
The point really is I am not sure if I really would be able help you with this.
> table_1 {
>id
>field_
> }
>
> table_2 {
> id
> fieldA
> table_1_id
> }
> table_3 {
> id
> field1
> table_2_id
> }
>
> Now, in my report what I want to do is print out some details for a specific table_1.id.
> Specifically, I want to print out contents of table2 where table2.table_1_id = table_1.id,
> and for each entry being printed from table_2 I want to print below it the entries in
> table_3 where table_3.table_2_id = table_2.id.
===================================================================================
Report Header **
|-|
|{select table_1.id from table_1,table_2 where table_2.table_1_id = table_1.id}1|
|-|
|{select * from table_3, table_2 where table_3.table_2_id = table_2.id}2|
|-|
Report Footer **
====================================================================================
** represents Label - it can be your company name etc. when you design the form
you would have a component somewhere in the toolbar
{} - anything between {} is the query. You might store in the database or you would
have to write it in the report generating application. some database allow you to
store this as view/query.
query1 would be the outer query
query2 would be the inner query
Let me know if this works
Cheers
$
_cka at 2007-7-13 13:16:48 >
Sorry for the late reply! Thanks for the responses. I wasn't sure what you meant with your example with two queries, I couldn't see how I could do that in iReport.
I had in fact already watched the tutorial on sub-reports but hadn't got it to work for me. I finally found a solution. My problem was primarily in my query, but I solved in a bit more of a round about way.
I created a view in the mysql database, but to get the right fields I had to use a left join on what I called table 3 here. This meant that I got all results and where there was no entries in table 3 I would get a null value. I can then choose to not print when the value is null. I used groups after this to present the data in the report.
Thanks for you help.
Lionel.
