SQL Developer 1.1. How Do I Build a Master Detail Report (with Chart)?

Perfect, a query on the Forum triggered a thought for this entry. "How do I create a report that has a table and chart in it?"

Let's start with a Master-Detail report.

1. Using SQL Developer 1.1 Evaluation Release 2 (1.1.0.21.97) go to the reports tab and select "Add a Report" under the User Defined Reports. I'll leave you to fill in the name, description and tooltip details. The report we are creating uses the demo schema HR, with the EMPLOYEES, DEPARTMENTS and LOCATIONS tables.

2. Although you only see one panel at this stage, you are creating the Master query. Set the Style as Table and enter the following query:

select d.department_id, department_name, city
from departments d, locations l
where d.location_id = l.location_id

Note: No trailing ';'

You can elect to test the reports at any stage, using the Test button.

3. Now to add the detail.
Select the Add Child button to open a new panel in the dialog.

The basic detail query, without chart or any complexities is as follows:

Select * from employees
where department_id = :DEPARTMENT_ID

Note: The bind is CASE sensitive. Also useful to note, the child query knows that this bind is from the table above.

Another example using that bind variable is:

select :DEPARTMENT_NAME, last_name, salary
from employees where department_id = :DEPARTMENT_ID

It is not that interesting, to have the department name in the detail records, because you'll just see that name duplicated.

4. Run the report to see the master detail in operation.

That's it, a Master -Detail report in action.

Making the Detail a Chart.

Let's take it further and pull the CHART into play...

5. Select Edit to open the report again.

From the previous blog entry on charts, you saw that we need to select X, Y and data, so we'll do the same here. Replace the child query with:

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

So now I am looking at the departments table and for each department, I want to know who works for each manager in that department and see their salaries.

To create the chart, set the child style to CHART and then look at the chart details and set them to BAR_VERT_STACK and select the Test button.

When you are happy select Apply. Here's mine:

SQL Developer 1.1. How Do I Build a Chart?

I did a few "Sneak Peeks" into 1.1, in an attempt to show you what you might expect down the line. Now that 1.1 evaluation release is available, you can go back and look a those and find the features I mentioned.

I'd now like to take this further and do a series of "SQL Developer 1.1: How Do I ....", first using the evaluation drops as they become available and continue that into the production release.

In showing how to go about discovering new things and working with 1.1 in general, I could take 2 quite different approaches. The one approach would be to be quite organized and plan a whole schedule of entries and the other is to write random ad hoc entries that just jump in and out of the tool all over the place. I'm opting for the latter approach, because right now I am inspired to do a quite write up, just covering a basic chart in SQL Developer 1.1

Building a Chart using SQL Developer 1.1

At this stage I should point out, that the only 1.1 software you have access to is SQL Developer 1.1 Evaluation Release 1.1.0.21.41. That's what I am going to use. If you do not yet have that release, then go to OTN and follow the links to download it.

Assumptions:
The query I will use in my example is for the user SCOTT/TIGER and requires that you have access to SCOTT's EMP and DEPT tables and a Connection to SCOTT.

Starting with the Reports Tab, select User Defined Reports and, using the context menu, select Add Report. If you're diligent, you could add a folder first, so that all your reports are saved into carefully planned categories.
Once you have invoked the new report dialog, there are three areas you should take special note of:

• The Style of report, in this case a CHART
• The SQL text
• Chart Details

It is obviously important to complete other details, such as name and tooltip, these I'll leave you to do. I want to create a chart of Employees' Salaries per Department. Here's my query. Note, no trailing ';'

select emp.deptno, emp.ename, sal
from emp, dept
where emp.deptno = dept.deptno
order by dept.deptno, sal

When creating a chart, the rule of thumb is select group, series, data from table

Before you test the chart, select the Chart Details and change the chart type to BAR_VERT_STACK. For the rest of the options, you can make your own choices. For bigger reports, the Legend can take up quite a bit of real estate, so I tend to set that to False. You can see what suits you. Once you have set the Chart Details, you can return to the Details tab and Test the chart or just select Apply.

Once back in the Navigator, select your report and select the connection you have for SCOTT.


The stripy pajama report reflects the 4 departments in the table , with each employee and salary. The mouseover for each node indicates Employee name and Salary. The power of the chart is that a glance you can see that, based on my data, Department 20 has the greatest salary bill, while Department 10 appears to have the highest paid employee.

This is a trivial example, but you'll note that the results returned are quite rich. Why not try this against some of your System data?

SQL Developer Presentations from Oracle World Available

If you missed Oracle World in San Francisco, or even if you were there, the presentations for the talks are now available for you to browse.

This link takes you to the Content Catalog. Note this app does require a username and password, but they are there for you to use.

If you want to see the talks that Kris and I did, just enter "sue harper" into the Speaker name field and you'll get the Overview and Advanced presentations. (No demos, just power points!) Kris and I will be doing blog entries of the demos in the next few weeks.

If you want to see more SQL Developer related talks, enter "SQL Developer" and you'll also get Donal's talk on the work the Migration Workbench team are doing and how they're using SQL Developer.

Of course you know how to use a simple app like this, but I want to be sure you see the good stuff... ;-)

Oracle SQL Developer 1.1 Evaluation Release is Now Available!

Need I say more? The long awaited evaluation copy of Oracle SQL Developer 1.1 is now available for you to play with. Please send us your comments! I'll give you the links in a mo, please note, this is NOT a Supported release, it is for evaluation purposes only. The team is still working on the code and closing bugs as I write. We'll be doing regular drops for you to evaluate over the next few weeks and will be using a FeedBack Application to track issues and comments.

You will not be supported through Oracle Support or the Forum, only the feedback site. You will not get the evaluation software from the production download site, instead follow the links through the short survey and on to the evaluation release download. (For subsequent downloads you'll be redirected past the survey) When you reach the download page, there are links to a few very useful documents:

• New Features - This is a list of new features, quite comprehensive, possibly missing a few little features here and there
• Known Issues - This is a list of bugs already logged, some already fixed in a later build and which you'll pick up in next week evaluation drop.
• Getting Started - This is really important. If you are using 1.0, there are some files you need to back up if you want to preserve them and go back to SQL Developer 1.0.

But first...download here

No, I can't tell you the production date yet.

Have fun!