Storing reports in folders serves a number of purposes in SQL Developer. Folders help you categorize reports that belong to a particular project or have a similar theme. This in turn helps you quickly find a category of reports, whether instance data or data dictionary reports. In addition, having folders of reports means that you can export, and therefore, later import the folders to reuse and share. You can create nested folders to further organize your reports.

To create a folder, select the User Defined Reports node in the Reports navigator and select New Folder from the context menu, as follows:

This invokes a simple dialog as shown in the following screenshot, which you can then populate with the details about the folder. This folder dialog only requires the Name field to be filled. However, if you provide more detail, it is useful to share reports later.

As soon as you create an initial folder or report, a new UserReports.xml file will be created in the C:Documents and Settings<user>Application DataSQL Developer folder.

The other change to be aware of is that reports created prior to SQL Developer 1.5 were created in UserReports.xml and stored in the C:Documents and Settings<user>.sqldeveloper folder. In SQL Developer 1.5, this location was changed to the Application Data folder as described.

The initial code created using the earlier mentioned folder example looks as follows:

<?xml version="1.0" encoding="UTF-8" ?><displays><folder>
  <name><![CDATA[HR Sample Schema]]></name>
  <tooltip><![CDATA[HR Sample Schema reports]]></tooltip>
  <description><![CDATA[All reports relating to the HR Sample
                Schema]]></description>
   </folder>
</displays>

This is the file that your new reports are added to, which you share with other users. We'll discuss sharing reports later.

This is the most common form of reporting used and, with the exception of a few charts provided, all the shipped reports follow this style. Tabular reports are also used in drill-down and master-detail reports. Regardless of the report you're about to create, you need to start with the initial SQL query. Consider the following query:

SELECT B.DEPARTMENT_ID,
   B.DEPARTMENT_NAME,
   A.FIRST_NAME,
   A.LAST_NAME,
   A.PHONE_NUMBER,
   A.HIRE_DATE,
   A.SALARY,
   C.JOB_TITLE
FROM HR.DEPARTMENTS B,
   HR.EMPLOYEES A,
   HR.JOBS C
WHERE A.EMPLOYEE_ID = B.MANAGER_ID
AND B.DEPARTMENT_ID = A.DEPARTMENT_ID
AND C.JOB_ID        = A.JOB_ID;

When this query is used in a tabular report, the output will appear, as shown in the following screenshot:

If you work with large sets of data, restricting the records returned using a WHERE clause makes the result set more manageable. Writing the query to support input values or a bind variable means that you can rerun the query using different values. This kind of a query makes a good report, because you can pass in values to restrict the result set.

It is important to know that while writing a query with bind variables, unless otherwise handled, the data is stored in the database in the uppercase. If you have data in mixed case in the database, you'll need to code for this.

For example, you can add AND C.JOB_TITLE LIKE :JOB_NAME to the previous query. This is fine, except you need to know what you are searching for and that, in this case, is Job Titles stored in mixed case. So, entering "sales" or "manager" would not return any records. In this example, it's safer to enter the code AND UPPER (C.JOB_TITLE) LIKE UPPER (:JOB_NAME). Then, the user does not need to know the composition of the data.

Once you have set up the binds in your report, you can run the report. You will be prompted for the value that you can add, as shown in the following screenshot:

The Create Report dialog also provides the opportunity to add more meaningful detail to the binds in your reports.

Below the SQL script region is a set of tabs, one of which is the Binds tab, as shown in the following screenshot. This allows you to add more detail to the bind variables declared, which is useful and especially true if you pass a number of parameters.

In the example shown in the following screenshot, we have provided a Default search value, Prompt, and ToolTip. All of these provide added information to other users running the report.

You can create a report that steps from one report to the next. You can also navigate to the definition of an object. Therefore, a report that returns a list of tables has an additional context menu that you can use to navigate to the table in question. The shipped reports also support drill-down reports. Consider the All Constraints report in the Tables | Constraints node. In addition to returning the Constraint details, the report displays a list of tables. Here, the context menu displays an extra menu option to navigate to the table in question:

To create a drill-down report, create the initial master report, as you would do for any other tabular report. By way of an example, we'll create a master or top-level report using the same query that we used before:

SELECT DEPARTMENT_ID,
       DEPARTMENT_NAME,
       LOCATION_ID
FROM DEPARTMENTS

Create a second report using a query that will provide the results of the detail records (following the drill-down action from the first):

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT_ID
FROM EMPLOYEES
WHERE department_id =:DEPARTMENT_ID

Give the report a descriptive name (for example, Employee Details); you'll need to access this later. Notice, for this report, we included a bind variable. Therefore, the report either requires a value to be entered using the Bind Variable dialog, or it has a value passed from the top-level or master report.

Now, return to the top-level report and select the Advanced tab.

Select Add Report and enter the Name of the detail report. This is the value that is displayed in the context menu. In the Report field, there is a drop-down list of all the reports available, whether shipped or user-defined. Find and select the detail report you created, as shown in the following screenshot. Clicking on Apply saves the changes and runs the report.

Running the top-level report is no different from any other tabular report. The context menu now displays an addition menu called Reports. All of the reports that you can navigate to from this report are displayed in the submenu. In the following example, the value that you have selected is passed to the called detail report as a bind parameter:

While drilling down through the reports, a new button becomes available to allow you to traverse back up through the reports, as shown in the following screenshot:

Initially, you create a regular tabular report as the master report. In the following example, we will use the same query as before:

SELECT DEPARTMENT_ID,
       DEPARTMENT_NAME,
       LOCATION_ID
FROM DEPARTMENTS

You can create, test, and even run the report as you would in any tabular report. This will ensure that the master records and reports are correct. In the example shown in the following screenshot, we have run Report Test:

To add the detail, click on the Add Child button on the main report. This invokes a new panel in the dialog and allows you to add the detail records.

The example in the following screenshot displays the full set of employees for any record in the master report. In order to have a meaningful set of records for each department, you need to link the master to the detail. For example:

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT_ID
FROM   EMPLOYEES
WHERE  department_id = :DEPARTMENT_ID

The two key elements here are first that the department_id bind variable is in uppercase (or the case of the column name in the database) as the report is looking for this "variable" in the database, and the second is that you have the join value in the master report.

Once you have created the report, you can tab through the master records to display each set of related detail records.

Notice that it is the name of the report that is displayed in the master and detail tabs.

You can create reports and display them as charts instead of as a tabular output. The basic premise of a chart is that you need values for the x- and y-axis and the central value. Therefore, a report query is structured as follows:

SELECT <group>,<series>,<value>
FROM <table(s)>

The query SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMPLOYEES is an easy example to start with.

Once you have entered the query, switch the display style to Chart and select the Chart Details tab.

The Property node under Master Report in the left navigation pane provides you with more control on the style of chart you are going to display. The choices of chart styles are as follows:

The number of records returned should drive the choice of the chart style you make. For example, the PIE_MULTI choice for Chart Type produces a pie chart for each record and is therefore better suited for fewer records or a query different from the one displayed earlier.

There are a number of other settings, which you can adjust, that control the appearance of the final chart. In the example we are using, Legend has been switched off as this displays each LAST_NAME and makes the report very busy.

The final output for the chart is shown in the following screenshot:

Notice that as you roll the mouse pointer on the various regions in the chart, the values are displayed. You can now incorporate these charts as details in a master-detail report.

SQL Developer offers a limited choice of graphical reports. The product is not a graphical reporting tool, so while these reports may seem limited, they do provide a visual report of the data being queried. Other graphical chart options can be pie charts and gauge charts.