How To Build A Report with Multiple Children

I was asked recently if SQL Developer supported multiple children reports. The answer is ‘yes’, and here is a post showing how to achieve this.

First I need to clarify the answer a bit.

Reports have a ‘master’ record set. These records can be used to ‘look up’ related records in a ‘detail’ set. This detail set becomes the ‘Child Report.’ A parent can have 1:Many children. However, children cannot be parents (at least not in SQL Developer, yet.)

To see an example of this in action, simply open the ‘Tools’ – ‘Monitor Sessions’ screen. This is in fact a SQL Developer report with 6 child reports attached.

Here’s how to build your own Master-MultipleChild Report

Using the SH demo schema, I am going to build a report showing the ‘Top 25 Customers’ in terms of total total sales. Detail reports showing ‘Customer Info’, ‘Top 5 Purchased Products’, and ‘Profits’ will give even more insight into our business and customers.

To get started, we need to create a new User Defined Report. I’m going to share all the SQL that I hacked together for this blog post, but only if you promise to blindly trust my code.

WITH
temp_t1 AS (SELECT SUM(amount_sold) "Total Sales", cust_id FROM sh.sales
GROUP BY cust_id
ORDER BY SUM(amount_sold) DESC
)
SELECT 'Top 25 Customers', cust_id, "Total Sales"
FROM temp_t1
WHERE ROWNUM < 26

I’m cheating a little bit here. I’m using a WITH to build a subquery containing all the sales data, sorted in descending order by sum of sales data. Then in the main query I’m using ROWNUM < 26 to only grab the first 25 records. It may have been cheaper to use analytics or a ‘tricky’ HAVING clause, but I ran with this because it was easy and the point of this blog is not to discuss proper SQL but show you how to build charts. To know for sure I’d have to compare the plans for each, but my gut tells me my method is probably more expensive. Now rest assured that I have already been publicly flogged for this SQL Travesty! Anyways, once you have your report created, we can back into it and manage the report properties and start having children (hehehehehe.) Adding a child report one at a time using the ‘Add Child Report’ button is just like adding a new regular report:

Each time you add a child report, a new page will be added to the bottom panel of the report. Remember, to tie a child report record set, you need to use the :BIND syntax as previously covered in my previous post.

Children reports can be be grid, chart, gauge, etc – there’s no limitation. So you could have a chart report that’s used to drill into multiple children chart reports, although I’d say that might be kind of ugly.

Here’s the code for each of my child reports:

Customer Info

SELECT cust_last_name
|| ', '
|| cust_first_name "Name",
cust_city "City",
cust_state_province "State",
sh.countries.country_name "Country"
FROM SH.customers,
SH.countries
WHERE sh.customers.country_id = sh.countries.country_id
AND cust_id = :CUST_ID

Top 5 Products

WITH
temp_t2 AS (SELECT cust_id, SUM(quantity_sold) "QTY", prod_id
FROM SH.sales
GROUP BY prod_id, cust_id
ORDER BY cust_id, SUM(quantity_sold) DESC)
SELECT QTY, prod_name "NAME", prod_list_price "List Price"
FROM temp_t2, sh.products
WHERE temp_t2.cust_id = :CUST_ID AND ROWNUM < 6
AND temp_t2.prod_id = sh.products.prod_id

–Just like in the real world, inefficient code is easily copied and pasted throughout your application. Remember, this is just a blog and not the ‘Real World.’

Profits

SELECT (SUM(unit_price) - SUM(unit_cost)) Profits,
(SUM(unit_price) - SUM(unit_cost)) Profits2
FROM SH.profits
WHERE cust_id = :CUST_ID
GROUP BY cust_id

And here’s what the report looks like –

Clicking into the the other pages will auto-run and fetch the results. A particularly active and paranoid business may want to take advantage of the ‘Refresh’ property.

And remember, your brain is a sucker for pictures, charts, and pretty colors, so we’ll end on a Gauge report –

Too lazy to implement this yourself?

That’s OK, I’ve trained you to be ‘lazy’ and demand more! Since SQL Developer allows you to export reports, I’m going to share my report. Now you can download it and play with it to get a gist of the reporting in SQL Developer.

Here’s the report. — It’s an XML file, so use the ‘Save Link As’ option in your browser to save it to a file instead of opening as a web page. I’m lazy, so I want to save you a copy of clicks.

Save it to a file, then use the ‘Open Report’ on the ‘User Defined Reports’ mouse context menu to add it to your copy of SQL Developer.