ThatJeffSmith

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 = :DEPARTMENT_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!