Grandparent – Parent – Child Reports in SQL Developer
- by thatjeffsmith
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 Followers:
@thatjeffsmith any luck with the #Oracle awr reports in #SQLDeveloper?This is easy with multi generation parent>child Done in #dbvisualizer
— Ronald Rood (@Ik_zelf) August 26, 2012
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 build-it XML editor in SQL Developer.
SQL Developer Reports in their raw XML glory!
Right after the PDF element in the XML document, we can start a new ‘child’ report by inserting a DISPLAY element.
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.
Also I needed to update the binds tags to reflect the names from the child versus the original parent report. This is pretty easy to figure out on your own actually – I mean I’m no real developer and I got it pretty quick.
<?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 tricky.
We’ll need to use aliases for the SNAP_ID column. Since we’re using the same column name from 2 different queries, we need to use different bind variables. Fortunately for us, SQL Developer’s clever enough to use the column alias as 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
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
And here’s where it gets even trickier – you can’t reference a bind from outside the parent query. My grandchild report can’t reference a value from the grandparent report. So 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 have a column name with a ‘:’ in it, or SQL Developer will get confused when I try to reference the value of the variable with the ‘:’ – and ‘::Name’ doesn’t work. 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!
