neelesh-shah

mostly about database development using oracle and application express

Tuesday, June 28, 2011

 

Oracle APEX - Page Stitching

I was presenting a session at the ODTUG on 'page-stitching' and half way through thought something went wrong even when nothing did and kind of derailed the session (I dont know why I was thinking I was getting 2 ORDER regions when I was actually getting them correctly as ORDER and ORDER_ITEM !). This is an attempt to fix it and get across what I wanted to share.


A note to, once again, credit Anton Nielsen for coming up with this way of displaying master details records at multiple levels.


To get a report in the following format in Oracle APEX

Master
-->Detail Level 1
------> Detail Level 2

use the 'page-stitching' technique described below.

All pages are developed in the hosted APEX site at
http://apex.oracle.com
using the DEMO_CUSTOMERS, DEMO_ORDERS and DEMO_ORDER_ITEMS tables.

Create an application, name is as desired, accept all defaults.

Create a page with a report to list records from DEMO_CUSTOMERS.

Page: 1
Report Query:
select * from demo_customers
This can be an interactive report and will be used to drill down to one customer.
Make the customer id column as a link passing the value and redirecting to page 2.
The value will be passed to a hidden item called P2_CUSTOMER_ID.

The result should display a report like this.


Create another page, 2. This will be page that will bring all data and all 'stitching' happens here.

This page will also have the same query as page 1, but will be filtering the result set to just the customer id passed. The value to filter will be passed from the first page via a column link.


Page :2
Report Query :
select c.*
from demo_customers c
where c.customer_id = :P2_CUSTOMER_ID

Create another report region in this page. This will display all orders of the selected customer and will eventually display the orders and order items but for now create it to show only the order_id column. We will replace this with the entire set of detailed records.

Page 2:
Report Query:
select o.order_id
from demo_orders o
where customer_id = :P2_CUSTOMER_ID


As you can see there is only one column. The result should look like this.



Add the following javascript code to the page.

function C2PageStitch(pPage,pItem,pValue,pDivID) {

var get = new htmldb_Get(null,$x('pFlowId').value,null,pPage);
get.add(pItem,pValue)
gReturn = get.get(null,'','');
get = null;
$x(pDivID).innerHTML = gReturn;
}

We will use this javascript to make an AJAX call a page (with detailed rows), bring the content and replace some div's innerHTML.

That some div will be the individual rows in the single column report created above. So set the HTML expression of the order_id column in the above report to the following:


<div id="orderWrapper#ORDER_ID#">
<img src="#IMAGE_PREFIX#ws/ajax-loader.gif" /></div>
<script type="text/javascript">
C2PageStitch('3','P3_ORDER_ID','#ORDER_ID#','orderWrapper#ORDER_ID#');
</script>

The img used in the script above can be any image you want to show when the page is loading.

This will set the order_id within a div tag and immediately call the javascript written earlier to replace the div with the content of the page called by the AJAX call.


Now, create another page 3, with two reports with the following queries

Report 1:
select *
from demo_orders
where order_id = :P3_ORDER_ID
(create a hidden item P3_ORDER_ID in this page)

Report 2:
select oi.quantity, oi.order_id ,pi.product_name, oi.unit_price,
oi.unit_price*oi.quantity line_total
from demo_order_items oi, demo_product_info pi
where pi.product_id = oi.product_id
and oi.order_id = :P3_ORDER_ID

To keep the template simple in this page, make a copy the page template, assign it to this page and remove everything from BODY and replace with

<c2:c2PageStitch>#BOX_BODY#</c2:c2PageStitch>

Running this page with an order_id passed in the url will look like this.


Thats it. Since the stitch page (2) has the code to replace the single column row in the order report with this entire content, running page 2 will display the report shown below.



The sample application can be accessed using
http://apex.oracle.com/pls/apex/f?p=47161:1

Another example can be seen here.
http://apex.oracle.com/pls/apex/f?p=41779:4
An attendee, Richa Kodipelli found this a perfect solution to one of her needs.

Please note that sorting and partial page refresh/pagination needs to be disabled on all the stitched reports.

This can be extended for updates with the usage of apex_items and appropriate submit processing.

I have uploaded the presentation and the sample apps in ODTUG.

Thanks
Neelesh

Labels: , , , , , ,


Archives

June 2011  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]