Parent Child Report - SQL Developer

1. To create a parent child report in SQL Developer Click on the View Menu and then Reports.


Once reports is shown right click on User Defined Reports and select New








2. Give the report a name and in the SQL box enter the sql for the parent report.







3. Click Apply



4. Go Back to reports and edit the report.











5. Click on child reports and enter a name for the child report.









6. On the tree on the left hand side, expand child reports and click on SQL Query.

Enter the query for the child report.
In this report the child report ties to the parent report via the deptno number.
The deptno number of the parent report is referenced by :DEPTNO. The bind variable must be in upper case and must be prefixed with a colon.






7. Save the report.



8. Double click the report in the reports tree to run it.  When an entry in the parent report is selected the appropriate children in the child report are shown.

Drill Down Report - SQL Developer

In SQL Developer you can link one oracle report to another creating a drill down from the first report to the second. To do this we create two reports, the master report and the drill down report.

1. First I'm going to create a master report based on the dept table.

From the VIEW menu choose Reports.

Once reports is displayed right click on User Defined Reports and select New Report








2. The Create Report dialogue is displayed.

Enter the query to select the details of the master report in the SQL section, in this case



  
SELECT *
  FROM dept


and click apply






The newly created Dept report now appears under User Defined Reports.


3. Right click on the report and run it.









4. Next I'll create the drill down report.  This report is based on the emp table which details employees.  Each employee in this table belongs to a department shown in the Dept report above.

As before I'll click on User Defined Reports and choose new report.

I'll give it a name of emp and under SQL Query I'll enter



  
SELECT *
  FROM emp
 WHERE deptno = :DEPTNO


The where clause ties the drill down to the master report.  It says select records from emp where the deptno, (department number), equals the :DEPTNO bind value passed in.  When referencing a bind value it is always in upper case and always prefixed with a colon.








5. Next I need to go back to the master report (dept).

Edit the report and choose Drill Down.
Click Add Report.

Give the report a name and then under report click the drop down box and select the emp report.






6. Save the report and then run it again.

Right click on any department.  The employess report is shown in the menu.







7. Click on employess and the employees report will be run for the selected deptartment