| multi-query report [message #175061] |
Wed, 31 May 2006 10:00  |
srao
Messages: 9
Registered: March 2005
|
Junior Member |
|
|
Hi
I am using oracle report builder ver 9.0.4.0.33 on 10g DB and very new to to this tool. So pardon me if this is a trivial question.
Problem:
I need to create a multi query report where the seconf query is driven by the values in the current row of the first query.
Eg. First Query Q1 is :
select emp.ename, dept.dname, dept.deptno dno from emp, dept
where
emp.deptno = dept.deptno
Second Query Q2 is
select dept.loc from dept
where
dept.deptno = :dno
There will be several rows that are feteched by Q1. For each dno that is fetched in Q1, Q2 should execute and fetch the dept.loc value.
The corresponding values of both query results need to be printed on individual pages. So which means the report should have as many pages as the rows fetched by Q1.
Note: EMP and DEPT have foreign key relation set up with DEPTNO column.
My approach:
Used Form-like report style as this displays a one record per page.
1. Created 2 queries in the data model view and defined a data link on DEPTNO column and using WHERE = properties. No luck.
2. Tried to define Q2 with bind reference as shown in the Problem section above. No luck here too.
The first query gets displayed as it should but the correspnding value (i.e., the corresponding location for the dept name displayed int the first query) in second query is where I am having problems. It just reads the first dept name and displays the same value for all the pages in the report. Or sometimes it just displays only the first page and rest of the pages (there should be 14 pages as first query returns 14 rows) simply dont exist!
What am I doing wrong?
Thanks,
Suma
|
|
|
|
| Re: multi-query report [message #175073 is a reply to message #175061] |
Wed, 31 May 2006 11:50  |
srao
Messages: 9
Registered: March 2005
|
Junior Member |
|
|
Hi all,
Found a solution for the above problem.
Problem solved by creating both the layout blocks (for query Q1 and query Q2) in tabular style, and setting the Maximum Records Per page property in property inspector of their corresponding repeating frames to 1. Also the display property for the second report block is set to "Down", while the first one is set to "Acorss". I did use the data link to link two queries based on the foriegn key deptno. I guess it should work the same, if you used bind reference on deptno in Q2 to link with Q1.
Very basic, but will not work if you change the default advanced layout options or mess with the layout of the columns.
Funny though I am facing problems if I change the layout style model of Q2 to other than that chosen for Q1. (Eg if Q1's report style is "Form-like" and Q2's is "Tabular" etc..)
Please share your thoughts if u've faced similar situation.
Thanks,
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
