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.

A Parent-Child report in Oracle SQL Developer (Departments – Employees)

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.

The Grandparent – Parent – Child Report Source XML

<?xml version="1.0" encoding="UTF-8" ?>
<displays>
<display id="92857fce-0139-1000-8006-7f0000015340" type="" style="Table" enable="true">
    <name><![CDATA[Grandparent]]></name>
    <description><![CDATA[]]></description>
    <tooltip><![CDATA[]]></tooltip>
    <drillclass><![CDATA[null]]></drillclass>
    <CustomValues>
        <TYPE>horizontal</TYPE>
    </CustomValues>
    <query>
        <sql><![CDATA[select * from hr.departments]]></sql>
    </query>
        <pdf version="VERSION_1_7" compression="CONTENT">
            <docproperty title="" author="" subject="" keywords="" />
            <cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
            <column>
                <heading font="Courier" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
                <footing font="Courier" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
                <blob blob="NONE" zip="false" />
            </column>
            <table font="Courier" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
            <header enable="false" generatedate="false">
                <data>
                null                </data>
            </header>
            <footer enable="false" generatedate="false">
                <data value="null" />
            </footer>
            <security enable="false" useopenpassword="false" openpassword="" encryption="EXCLUDE_METADATA">
                <permission enable="false" permissionpassword="" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="true" />
            </security>
            <pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
        </pdf>
    <display id="null" type="" style="Table" enable="true">
        <name><![CDATA[Parent]]></name>
        <description><![CDATA[]]></description>
        <tooltip><![CDATA[]]></tooltip>
        <drillclass><![CDATA[null]]></drillclass>
        <CustomValues>
            <TYPE>horizontal</TYPE>
        </CustomValues>
        <query>
            <sql><![CDATA[select * from hr.employees where department_id =  EPARTMENT_ID]]></sql>
            <binds>
                <bind id="DEPARTMENT_ID">
 <prompt><![CDATA[DEPARTMENT_ID]]></prompt>
 <tooltip><![CDATA[DEPARTMENT_ID]]></tooltip>
 <value><![CDATA[NULL_VALUE]]></value>
                </bind>
            </binds>
        </query>
            <pdf version="VERSION_1_7" compression="CONTENT">
                <docproperty title="" author="" subject="" keywords="" />
                <cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
                <column>
                    <heading font="Courier" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
                    <footing font="Courier" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
                    <blob blob="NONE" zip="false" />
                </column>
                <table font="Courier" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
                <header enable="false" generatedate="false">
                    <data>
                    null                    </data>
                </header>
                <footer enable="false" generatedate="false">
                    <data value="null" />
                </footer>
                <security enable="false" useopenpassword="false" openpassword="" encryption="EXCLUDE_METADATA">
                    <permission enable="false" permissionpassword="" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="true" />
                </security>
                <pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
            </pdf>
      <display id="null" type="" style="Table" enable="true">
        <name><![CDATA[Child]]></name>
        <description><![CDATA[]]></description>
        <tooltip><![CDATA[]]></tooltip>
        <drillclass><![CDATA[null]]></drillclass>
        <CustomValues>
            <TYPE>horizontal</TYPE>
        </CustomValues>
        <query>
            <sql><![CDATA[select * from hr.jobs where job_id = :JOB_ID]]></sql>
            <binds>
                <bind id="JOB_ID">
 <prompt><![CDATA[JOB_ID]]></prompt>
 <tooltip><![CDATA[JOB_ID]]></tooltip>
 <value><![CDATA[NULL_VALUE]]></value>
                </bind>
            </binds>
        </query>
            <pdf version="VERSION_1_7" compression="CONTENT">
                <docproperty title="" author="" subject="" keywords="" />
                <cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
                <column>
                    <heading font="Courier" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
                    <footing font="Courier" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
                    <blob blob="NONE" zip="false" />
                </column>
                <table font="Courier" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
                <header enable="false" generatedate="false">
                    <data>
                    null                    </data>
                </header>
                <footer enable="false" generatedate="false">
                    <data value="null" />
                </footer>
                <security enable="false" useopenpassword="false" openpassword="" encryption="EXCLUDE_METADATA">
                    <permission enable="false" permissionpassword="" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="true" />
                </security>
                <pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
            </pdf>
    </display>
</display>
</display>
</displays>

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.

Here’s what I mean:

Grandparent Query

SELECT snap_id start1, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1 asc

‘snap_id’ here is aliased as ‘start1′

Parent Query

SELECT snap_id stop1, begin_interval_time, end_interval_time, :START1 carry
FROM dba_hist_snapshot
WHERE snap_id > :START1
ORDER BY 1 asc

‘snap_id’ here is aliased as ‘stop1′

And the ‘tricky’ continues!

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!

Grandchild Query

Select Output From Table(Dbms_Workload_Repository.Awr_Report_Text(1298953802, 1,:CARRY, :STOP1));

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.

The Raw XML for this AWR Report

<?xml version="1.0" encoding="UTF-8" ?>
<displays>
<display id="927ba96c-0139-1000-8001-7f0000015340" type="" style="Table" enable="true">
	<name><![CDATA[AWR Start Stop Report Final]]></name>
	<description><![CDATA[]]></description>
	<tooltip><![CDATA[]]></tooltip>
	<drillclass><![CDATA[null]]></drillclass>
	<CustomValues>
		<TYPE>horizontal</TYPE>
	</CustomValues>
	<query>
		<sql><![CDATA[SELECT snap_id start1, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1 asc]]></sql>
	</query>
	<display id="null" type="" style="Table" enable="true">
		<name><![CDATA[Stop SNAP_ID]]></name>
		<description><![CDATA[]]></description>
		<tooltip><![CDATA[]]></tooltip>
		<drillclass><![CDATA[null]]></drillclass>
		<CustomValues>
			<TYPE>horizontal</TYPE>
		</CustomValues>
		<query>
			<sql><![CDATA[SELECT snap_id stop1, begin_interval_time, end_interval_time, :START1 carry
FROM dba_hist_snapshot
WHERE snap_id > :START1
ORDER BY 1 asc]]></sql>
		</query>
      <display id="null" type="" style="Table" enable="true">
		<name><![CDATA[AWR Report]]></name>
		<description><![CDATA[]]></description>
		<tooltip><![CDATA[]]></tooltip>
		<drillclass><![CDATA[null]]></drillclass>
		<CustomValues>
			<TYPE>horizontal</TYPE>
		</CustomValues>
		<query>
			<sql><![CDATA[Select Output From Table(Dbms_Workload_Repository.Awr_Report_Text(1298953802,
                                                            1,:CARRY, :STOP1 ))]]></sql>
			</query>
	</display>
	</display>
</display>
</displays>

Should We Build Support for Multiple Levels of Reports into the User Interface?

Let us know! A comment here or a suggestion on our SQL Developer Exchange might help your case!

Share on Tumblr

7 Comments

Ronald
Sep 19, 2012 @ 09:07:06

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.

horizontal

horizontal

:START1
and startup_time = :SUPT1
and instance_number = :INUM1
and dbid = BID1
ORDER BY 1 asc]]>

horizontal

Stuart
May 01, 2013 @ 16:07:38

Hi Jeff,

Thanks for sharing this. I had a go, and what a difference it made having the ability to drill down to another level.

Are we ever going to see this type of feature (nested child reports, grandparent reports) built into SQL Developer? I’d be hanging out for that release if/when it came…

Stuart.

- thatjeffsmith
  May 01, 2013 @ 16:12:39
  
  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.
  
  - Stuart
    May 01, 2013 @ 16:22:44
    
    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.
    
    Stuart.
    
    - thatjeffsmith
      May 01, 2013 @ 16:40:54
      
      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.
      
      - Stuart
        May 01, 2013 @ 16:50:54
        
        Maybe get a free trip down under by being a guest speaker t the next NZOUG?
        
        We just had one in March, so expect the next one to be late 2014.
      - thatjeffsmith
        May 03, 2013 @ 09:58:31
        
        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.