In many reports, the data displayed for one part of the report is determined by the data displayed for another part.
This is termed a "master/detail," or "parent/child," relationship.
To create a master/detail report, we need to define data link between two queries.
When you run a master/detail report, each row of the master query will cause the detail query to be executed, retrieving only matching rows.
Let us experience master/detail report with an example.
Open the report builder tool Build a new report manually OK
We will be directed to Data Model
Click on SQL . We get SQL Query Statement window.
Provide the Query Select * from dept OK
Provide the connection Details, User Name , password and the Database OK
This is how the Data Model looks like.
Again
Click on SQL . We get SQL Query Statement window.
Provide the Query Select * from emp OK
This is how the Data Model looks like.
Lets us establish relationship between two queries.
This is possible by creating data link.
Select Data Link, drag mouse pointer from Q_1 to Q_2
Observe the relationship between the queries is established.
Q_1 is the master query.
Q_2 is the detail query.
Data link information is also available in Object Navigator ( Press F5 )
Under Data Model Data Links
Now, Select Tools under Menu bar, select Report Wizard
Select - Create both Web and paper layout Next Style - Group Above Next
Move G_DEPTNO - Across
Move G_EMPNO - Down
Next Move DEPTNO, DNAME, ENAME, JOB, SAL to Displayed Fields
Next Next Select Predefined Template- Blue Finish
Save the report as MASTER_DETAIL.rdf
The report is a four page report.
This is how the report looks like
In Page 1 -- We get the details to employees working in deptno 10
In Page 2 -- We get the details to employees working in deptno 20
In Page 3 -- We get the details to employees working in deptno 30
In Page 4 -- We get the details to employees working in deptno 40
Output of Page 1
Click on next page
Output of Page 2
Click on next page
Output of Page 3
Click on next page
Output of Page 4 ( we have no employees )
The detail query ( Q_2 ) retrieves all related records for each record retrieved by the master query.