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!
