ThatJeffSmith

Sweet Child Report O’ Mine

I am just getting my feet wet when it comes to the reports in SQL Developer. The tool itself has quite a lot going on, everything from a database IDE to a migration platform, to a data modeling component. And then throw in the reporting stuff. I’ve already covered how to build a simple bar chart report in an earlier post, but now I want to take this report to the next level!

Basic reports are tied to a single dataset. Adding one or more child reports allow you to take a value from the parent dataset and feed that as an input to a secondary query and report. Maybe my report is showing a breakdown of breweries by country in a bar chart, and I want my report consumer to be able click on a country and then be shows a list of the breweries for that country. SQL Developer can accomplish this using a Child Report.

The Base Report

For the parent report I am going to use my previous beer example. It’s a chart report, and here’s the underlying SQL statement:

SELECT country,country, count(*) FROM beer
group by country, country
having count(*) > 100
order by 3 desc

Running this report gives me a chart that looks like this:

My Breweries by Country Summary Report Chart

Now let’s add the child report

Step 1: Add the Child Report

Right-click on the report in the tree and choose ‘Edit Report.’ We are going to go to the Child Reports tree and ‘Add Child.’ Note that you can add as many child reports as you would like, but I’m going to stick with the walking before the running.

Where do we go?
Where do we go now?
Where do we go?

I am going to choose a style of ‘Table’ for the report. I just want a grid style list of breweries by the selected country. The most important information to provide here will be the query that will be used to populate said grid. To tie the two datasets together, we need to use a bind variable. The ‘trick’ here is to uppercase the bind variable name to match the column name from the master query.

The Bind Variable ties the Master and Child queries together

For this particular report, the bind feeds off the COUNTRY name. So my child report query looks like this:

select brewery, city, state
from beer
where country = :COUNTRY

As I click on a Country in the master chart report, that value is fed to this query and will populate the grid for the child report.

Where do we go?
Where do we go now?
Where do we go?

The Completed Master-Child Report

The report is interactive. The chart labels are active, click and get the details!


If you would like to read more about reporting in Oracle SQL Developer, then check out my predecessor’s article in Oracle Magazine from 2007, ‘Now Reporting.’

A new feature for v3.1 is our PDF reporting. We will have a video tutorial posted to coordinate with the release and will be available on OTN.


Where do we go now?
No, no, no, no, no, no
Sweet child,
Sweet child report of mine.