Grandparent – Parent – Child Reports in SQL Developer

You’ll never see one of these family stickers on my car, but I promise not to judge…much.

Parent – Child reports are pretty straightforward in Oracle SQL Developer. You have a ‘parent’ report, and then one or more ‘child’ reports which are based off of a value in a selected row or value from the parent. If you need a quick tutorial to get up to speed on the subject, go ahead and take 5 minutes 🙂

Shortly before I left for vacation 2 weeks agao, I got an interesting question from one of my Twitter friends.

Tell Ronald I sent ya if you follow him 🙂

Now that I’m back from vacation, I can tell Ronald and everyone else that the answer is ‘Yes!’

And here’s how 🙂

Time to Get Out Your XML Editor

Don’t have one? That’s OK, SQL Developer can edit XML files. While the Reporting interface doesn’t surface the ability to create multi-generational reports, the underlying code definitely supports it. We just need to hack away at the XML that powers a report.

For this example I’m going to start simple. A query that brings back DEPARTMENTs, then EMPLOYEES, then JOBs.

We can build the first two parts of the report using the report editor.

Save the Report to XML

Once you’ve generated the XML file, open it with your favorite XML editor. For this example I’ll be using the built-in XML editor in SQL Developer.

Right after the PDF element in the XML document, we can start a new ‘child’ report by inserting a DISPLAY element. Now this is technically an undocumented (read: unsupported) feature, but it works. So take this as one of those use at your own discretion tips.

Once the XML is open, you can probably see for yourself how it all works. One of the nice qualities of XML is that it’s somewhat self-documenting – especially if the developers practice good naming conventions. In this case I can vouch for them as I pretty much figured out the structure on my own with just a quick nod from @krisrice.

SQL Developer Reports in their raw XML glory!

I just copied and pasted the existing ‘display’ down so I wouldn’t have to worry about screwing anything up. Note I also needed to change the ‘master’ name so it wouldn’t confuse SQL Developer when I try to import/open a report that has the same name.

Save the file and ‘Open Report…’

You’ll see your new report name in the tree. You just need to double-click it to open it.

Here’s what it looks like running

A 3 generation family 🙂

Now Let’s Build an AWR Text Report

Ronald wanted to have the ability to query AWR snapshots and generate the AWR reports. That requires a few inputs, including a START and STOP snapshot ID. That basically tells AWR what time period to use for generating the report.

And here’s where it gets a litle tricky, so please bear with me.

We’ll need to use aliases for the SNAP_ID column. Since we’re querying SNAP_ID in both the grandparent and parent reports, AND since we need to reference both values in the grandchild report, we need to use different bind variable names. Fortunately for us, SQL Developer’s clever enough to see a column name has been aliased and use the alias for the bind.

We can’t reference a bind variable from outside the parent query. Or in other words, my grandchild report can’t reference a value from the grandparent report. To get around this limitation, i just carry the selected value down to the parent. In my parent query SELECT you see the ‘:START1’ at the end? That’s making that value available to me when I use it in my grandchild query.

To complicate things a bit further, I can’t reference a bind in a report query that alread has a ‘:’ in the name. SQL Developer will just get confused when I try to reference the value of the variable with the ‘:’ – and no, ‘::Name’ doesn’t work either. But that’s OK, just alias it!

Ok, and the last trick – I hard-coded my report to use my database’s DB_ID and INST_ID into the AWR package call. Now a smart person could figure out a way to make that work on any database, but I got lazy and and ran out of time. But this should be far enough for you to take it from here.

Here’s what my report looks like now:

Caution: don’t run this if you haven’t licensed Enterprise Edition with Diagnostic Pack.

Thanks for the explanation, Jeff! Using this hack, I created a report that allows for drilling down from a Statspack graph to the top waits and then further down to top SQL for the given wait and interval.

Hi Jeff,
First of all, thank you very much for your great blog, I return to it quite often.
I did get all of this to work BUT…
a) If I add a drill-down to a child or grandchild, it also comes up on the parent (respectively grandparent). I’d like to change that since the specific drill-down doesn’t apply there but can’t seem to get it out even though I put removeFromParent=”true” in the XML (attribute doesn’t do what I hoped it would but it was worth a try…)
b) What’s worse; the drill-down from a child doesn’t work the first time I call it. Well, it opens up the report but the binds are not passed. The only way I can get a functional drill-down from child so far is: first drill down from parent, after the report opened click “back”, select a record in the child and do the drill-down from child. So if I make my selection in parent and child and drill down from the child first time around, the binds come up as Null; I always have to do the round-trip from parent first.
c) If I do a drill-down from a child, the “back” button doesn’t work.
d) Drill-down from the toolbar never passes binds, regardless of the selection and sequence of events/clicks.

That’s quite a couple of issues to put in one reply but if you could give me a hint on how to resolve b), that would be really, really appreciated 🙂

Now I realize this post was about multiple levels of child reports hence all the drill-down questions may be a bit out of place but the functionality just screams to be used this way; get to a specific item/selection with a couple of clicks in your dashboard report and run a detailed (drill-down) report without having to pass all those binds by hand (copy-paste).

Hi, I used this method to create multiple levels of child reports in the past. When I just tried in the latest version of SQL Developer, it doesn’t seem to work. Is that right or am I possibly doing something wrong?
Thanks.

Hi, I managed to get grandparent-parent-child report and it works. But when I generate HTML report the child portion of the report is not there. I’m on SQLDeveloper 4.0.0.13.
Is this going to be supported in the new release?

Why not make it a simple master detail with the master query being:
SELECT * FROM
(SELECT dbid, instance_number inst_id,
LAG(snap_id) OVER (PARTITION BY startup_time ORDER BY snap_id) prev_snap_id, snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 3

I was able to “hack” the XML as per this post and get Grandparent=>Parent=Child to work.

I’m even able to have 2 tabs at the Parent level (each with it’s own child).

Basically I’m asking at the Grandparent level for the user to tell me the “Batch Job” they want to know about.

Parent level then gives the “executive summary” of what happened

Grandchild level will allow a further drill into the details.

Is there a way I can have the selection of “Job” at the Grandparent level also select the right TAB at the parent level? By default it always passes the data from GP to the leftmost Parent Tab (which is only appropriate for 1 choice at the GP level).

If I have 20 reports at the GP level
I’ll have 20 tabs with the right query at the Parent Level
Then 1 Child for each parent that has the right detail query at the child level.

I was hoping there might be some sort of

onclick=”onSelectChildTab(this, 1)

type syntax like you get when doing and export of a report that has a Parent => multiple children when viewing the HTML output

You can’t delete a parent record if it will leave orphan rows. You say you created two tables with foreign keys – have you already forgotten those two tables?

You can see the referential integrity constraints for each table on the table editor. Open a table, go to the Constraints page. Look for constraint_type of ‘Foreign_Key.’ Then scroll over for the R_TABLE_NAME – that will tell you where your parent record is. If you want to know what the child tables are, you should be able to import your DD to a data model and see that visually.

Greetings Jeff!
I’m a SME in accounting/finance. I have to tell a programmer what I want in my database and what “machinations” I want done in it. Can you suggest a pro-forma document I could provide to the programmer? ( maybe direct me to an example).
Thanks.

Thanks Jeff.
What reports will be produced…source of the data for each report. Some forms will be multi-level…need to enter an asset then another one and so on.
Some fields will have drop-down choices. How do I present all of this in a document to the programmer? That’s what I’m looking for. Hope you can help.
Luc

I would create a requirements doc for each report where you describe the business problem you’re trying to solve. You could mock up screenshot examples, but trust the developer to handle the implementation/tech stuff.

I was able to add drill down report to a child (inserted … within tag for child) but it appeared in a menu for parent report as well and when I put several child reports there and defined drill down for them the same as for other children, for parent report I got it duplicated several times

I just need more people asking for it. I’m already sold on the idea. We have some exciting stuff for reports in 4.0 due soon. This won’t be in it, but maybe we can stir up some excitement for multiple-level reporting.

Maybe people just don’t know about the user defined reporting in SQL Developer?! Pity,… it is such a great feature. Only limiting SQL Developer to Parent-Child reports is one reason why people at our site don’t use it (it simply doesn’t provide enough drill down capabilites).

Thanks for the info on SQL Developer 4. I must say that reporting enhancements is the first thing I look for in the new features list.

I spend a lot of time on promoting existing features to users. Folks use what they know, do what works, etc. If you look for it though, it’s very hard to miss. It’s a popular feature for many folks, so I think anything we do here has large potential to benefit many people. By that I mean, for the folks that like the reporting, they build LOTS of reports. I can think of 2 things you’ll like A LOT in 4.0, stay tuned.

I’ll try to remember to email you when it’s out so you don’t miss it, or the features.

BTW, I miss the pumpkin soup and toast that is so good in NZ 🙁 I need to make it back someday.

Get me an invite, and I’ll have a hard time saying ‘no!’ This has been my secret plan for awhile. Just waiting for the kids to get a bit older before I leave town for a few weeks with their momma back to our favorite place.

Hi Jeff,
I just added a few tweaks to make it generally usable. It makes sure that the dbid, instance_number and startup_time are the same for both queries. I hope it is usable for others too. It would not hurt to make awr reports accessible for humanoids using SQLDeveloper. It’s a nice tool with growing power.