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!
