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