TECHNOLOGY: SQL Developer
Now ReportingBy Sue Harper
Build chart, drill-down, and master/detail reports with Oracle SQL Developer.
In March 2006, Oracle announced the availability of Oracle SQL Developer, a GUI for Oracle Database. Oracle SQL Developer provides an easy-to-install, lightweight, multiplatform GUI that enables database developers to do a variety of database activities, including browsing, creating, and updating data.
In the area of reporting, Oracle SQL Developer provides a collection of data dictionary reports for querying your system details and also offers you the ability to create your own reports and share them. This column shows you different report styles available in Oracle SQL Developer and how you can store and share reports.
Reporting in Oracle SQL Developer 1.1 adds new data dictionary reports, such as Locks by User, to the list of predefined reports. The Sessions report ( Database Administration -> Sessions -> Sessions ) is now a master/detail report. And for all data grids in Oracle SQL Developer, you can export the report output to various formats. Figure 1 shows a Sessions report and the format options available from the Export Data context menu, including the new HTML and XLS formats.
Whereas all data grids have additional context menu items, such as Single Record View and Count Rows , the Sessions report also includes Trace Session and Kill Session context menu options, as shown in Figure 1.
Get to Know the Basics
Although you can create all your reports under the User Defined Reports node, you can also create folders to group related reports. To create a reports folder in Oracle SQL Developer, click the Reports tab, right-click the User Defined Reports node, select Add Folder , and complete the details as required.
Five report styles are available in Oracle SQL Developer 1.1: Chart, Code, plsql-dbms_output, Script, and Table. To create a basic tabular report, right-click the report folder that will contain the new report (or right-click the User Defined Reports node) and select Add Report . Fill in the fields, such as Name, Description, and Tooltip; set Style to Table (the default); and add the following query to the SQL text box:
select e.last_name, d.department_name, e.salary from departments d, employees e where d.department_id = e.department_id order by d.department_name
Note that you can test and adjust the report while you're still in the dialog box—the Test button is on the Details tab. Complete any remaining details, click Apply , and run the report.
To restrict the records returned, add bind variables. Edit the code for your report, and add the following:
and d.department_name like :DEPARTMENT_NAME
The last lines of the new code block are now
where d.department_id = e.department_id and d.department_name like :DEPARTMENT_NAME order by d.department_name;
You can select Apply to update the SQL and run the report without making further adjustments, but it is a good practice to set the prompt and default value for the bind variable when you are editing the report. Click the Binds tab, click Add Bind , and enter the name of your new bind variable, DEPARTMENT_NAME (the bind variable is case-sensitive); a prompt, enter a Department Name; and a default value, Accounting. Optionally, for users new to the Enter Bind Values dialog box, add a helpful tooltip such as Use % to return all records . After you have entered all of the bind variable information, click Apply . If you do not set these, the values are populated automatically the first time you run the report.
If you want to navigate from one report to another, you can do so in Oracle SQL Developer by creating a drill-down report that uses the Report ID. Let's create a Departments report that drills down to an Employees report. It's easier to start with the second report, the report you will drill down to, so create the Employees report first.
Right-click the report folder that will contain the new report (or right-click the User Defined Reports node), and select Add Report . Fill in the fields, including Name (Employees), Description , and Tooltip; set Style to Table (the default); and add the following query:
select employee_id, first_name, last_name, email from employees
The important part of the drill-down report creation process is that you need to create a Report ID for the second report, the drill-down report. So, for your Employees report, in the Create Report dialog box, click the Advanced tab and add a value such as EmpDrillDown to the Report ID field. Click Apply when you have finished.
You can now create the Departments report. For this report, provide the report details, including the following query:
select department_id, department_name from departments
Click the Advanced tab, and this time click the Add Report button and fill in the fields. Name is the value that appears in the context menu, such as Move to Employees , and the Report list provides the link— EmpDrillDown in this example—to the Employees report. Figure 2 shows a Departments report result and a context menu option— Move to Employees —for an Employees report.
Building a Chart
When creating a report with the Chart style, the rule of thumb is to use SELECT group, series, data FROM table . So the basic tabular report you created earlier also has the right ingredients for the Chart style. Another good use of the Chart report style is for looking at your system information graphically. For example, to look at the trends of the datatypes used in columns in each table in a particular schema, create a new report, by setting Style to Chart and using the following query as the SQL:
select table_name, data_type, count(data_type) from all_tab_columns where owner = 'HR' group by table_name, data_type
Click the Chart Details tab in the Create Report dialog box, and set the Chart Type to BAR_HORIZ_STACK before running the chart.
The output is perhaps more useful when you start joining tables or making use of existing foreign key relationships. Consider the EMPLOYEES table again, and note how powerful a graphical representation of all the managers, displaying their direct reports and those people's salaries, can be. Create a Chart report with the following query:
select m.last_name, e.last_name, e.salary from employees e, employees m where m.employee_id = e.manager_id
Once you have set the Style to Chart , click the Chart Details tab and change Chart Type to BAR_VERT_STACK . (For bigger reports, the legend consumes real estate, so I tend to set Show Legend to false .) Note that as you roll your mouse over the graph, as shown in Figure 3, the query detail appears.
Unlike drill-down reports, master/detail reports are coordinated, so as you move from one record to the next in the master, the detail records change. To create a master/detail report example, first create the master report. Create a new report as before, with Style set to Table , and include the following SQL:
select d.department_id, department_name, city from departments d, locations l where d.location_id = l.location_id
To add the detail report, in the Create Report dialog box for the master report, click the Add Child button. You want the detail records to be linked to the master records, so make sure the Type selected is Detail . (If you select Child , there is no link to the master records.) Add the detail query to the new panel in the dialog box:
select * from employees where department_id = :DEPARTMENT_ID
The bind variable is case-sensitive, matching the case stored in the database. You can use the bind anywhere in the detail query, because the bind is from the preceding query (the master).
You can spice up the report by switching the detail report to display a chart ( Style = Chart ). You need to change the detail query a little, because you need information for the axes and the data. Use the following as the new detail query for the Chart report:
select m.last_name, e.last_name, e.salary from employees e, employees m where m.employee_id = e.manager_id and e.department_id = :DEPARTMENT_ID
To finish the chart version of this detail report, set the detail report's Style to Chart . Click the Chart Details tab, and change the Chart Type to BAR_VERT_STACK and Show Legend to false. Figure 4 shows a sample chart result of this detail report.
Other Report Styles
I think of the Code, Script, and plsql-Dbms_output report styles as utilities more than as report styles. To see how the Code style affects your reports, first create a regular tabular report ( Style = Table ) with the following SQL:
select text from user_source where name ='SECURE_DML'
Run the report as HR. Edit the report, switch Style to Code , and run the report again. The report output is now formatted, with reserved words in uppercase and a different-color font. The formatting also indents the code, making it easier to read.
If you regularly run scripts, you can set Style to Script , and Oracle SQL Developer will execute the saved script. Do this with care; the script executes for the user you are connected as, with no additional prompting.
Create reports that output HTML formatted text, by setting Style to plsql-Dbms_output . For example, create a new report that includes the following:
begin dbms_output.put_line ('<H1> This is a Header </H1>'); end;Sharing Reports
All the reports created in this column are stored in a local folder. By default, this is the Documents and Settings\<user>\.sqldeveloper folder and the report is called UserReports.xml. To share reports in your group, export the reports you have created to a shared server. You can export your reports at the folder or individual report level.
In Oracle SQL Developer, select Tools -> Preferences and then the Database -> User Defined Extensions node. Now click Add Row , select REPORT from the Type list, select the Location field, click Browse , select the saved reports XML file, click Save , and click OK . After an Oracle SQL Developer restart, your reports will appear in the Shared Reports node.
You can find useful reports created by other users or add your own favorite reports at SQL Developer Exchange.
This column has demonstrated how easy it is to create reports by using the various new report styles available in Oracle SQL Developer and share your reports. Using charting and master/detail reports is a powerful way to display information about your database and your data.
Sue Harper (email@example.com) is an Oracle senior principal product manager based in London.