ThatJeffSmith

Sweet Child Report O’ Mine

Social:

Updated: April 13, 2015
Reports are great ways to access data on demand without having to write and run the code over and over.

Sometimes you want to take the results of a query and feed it to another query as an input or parameter.

Wouldn’t it be great if you could fire off a report based on the selected value of another report?

This is the basic premise of a Master/Child report.

For this example, I’m going to use my ITUNES table – you can follow along if you want. Just read that post and build your own demo table(s) from YOUR iTunes library.

I want to be able to select one of my albums and then see all of the songs/tracks for THAT specific album.

The Base Report

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

SELECT DISTINCT demo.music_artists.artist, album
FROM demo.music_artists, demo.itunes_music
WHERE demo.music_artists.artist_id = demo.itunes_music.artist
ORDER BY artist ASC, album ASC

Note that when you bring in your ITUNES library, everything comes over as a single table. In a previous post I showed how can you automatically normalize your data using SQL Developer. So NOW, I have two tables, MUSIC_ARTISTS and ITUNES_MUSIC. I didn’t normalize it very far, I could have continued with ALBUMS, but you get the idea, I hope.

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

Scrolling isn't fun...can't I filter?

Scrolling isn’t fun…can’t I filter?

Why yes, yes you CAN filter.

Mousing over the column header will give you a funnel icon – clicking on that will give you a dialog to find just the records you want to view.

Note that the distinct value list is determined client-side. We do NOT re-query the database as that can be expensive. So you’ll only see the distinct values that have been fetched so far to the report.

There we go!

There we go!

Now, how do I get the song titles?

Now let’s add the child report

Click the add button as many times as you'd like to add child reports - you can have any number of reports here that are tied to the 'Master' report.

Click the add button as many times as you’d like to add child reports – you can have any number of reports here that are tied to the ‘Master’ 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.’

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 album tracks by the selected artist. 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.

Note that if you're going to use the BIND to tie the two reports together the :COLUMN in the child report must be UPPERCASE.

Note that if you’re going to use the BIND to tie the two reports together the :COLUMN in the child report must be UPPERCASE.

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

SELECT name, composer, bit_rate, plays , last_played FROM demo.itunes_music
WHERE album = :ALBUM

As I click on a album 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

It's So Easy

It’s So Easy


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

Grids are boring, does this work with charts, too?

Yes! Although we previously had a bug in SQL Developer where it would break the child reports when your Master report was of type CHART – so make sure you’re on at least version 4.0.3. The concept is exactly the same. Query to supply data up top, reference value from first dataset in a report down below, ties in on :UPPER_COLUMN_NAME.

You can't click on the chart labels, you have to click on the charts/graphs themselves to fire the child query.

You can’t click on the chart labels, you have to click on the charts/graphs themselves to fire the child query.

Version 4.0 Makes This So Much Simpler and More Powerful Too

Simpler, and more powerful? Yes. Simpler – you can live-preview the report as you’re designing it. More powerful – you get way more control over exactly how your charts look. Plus, you have 50+ new charting options to choose from.

I’m not a tease, so for your listening enjoyment…

Related Posts Plugin for WordPress, Blogger...
Social:

Similar Posts by Content Area: , , ,