Skip to Main Content

Show detail report for IR single row view record

I have previously explain how show detail report when viewing interactive report single row view on OTN forum post. That did work on APEX 3.x, but uses undocumented functions e.g. $a_report.

APEX 4.0 introduced dynamic action. APEX 4.1 introduced "Page Items to Submit" for classic report. Thanks for these enhancements we can do same with documented features.

This example use Product Portal sample application tables.

First create interactive report from query:

SELECT customer_id,
  cust_first_name,
  cust_last_name,
  cust_street_address1,
  cust_street_address2,
  cust_city,
  cust_state,
  cust_postal_code,
  phone_number1,
  phone_number2,
  credit_limit,
  cust_email
FROM demo_customers

Set single row view related attributes:

  • Link to Single Row View: Yes
  • Uniquely Identify Rows by: Unique Column
  • Unique Column: CUSTOMER_ID

Edit page and create hidden item Px_CUSTOMER_ID:

  • Value Protected: No
  • Source Used: Always, replacing any existing value in session state
  • Source Type: Always Null

Create classic report region:

SELECT pi.product_name,
  pi.product_description,
  oi.unit_price,
  SUM(oi.quantity) AS quantity,
  (oi.unit_price * SUM(oi.quantity)) AS extended_price
FROM demo_order_items oi,
  demo_product_info pi,
  demo_orders o
WHERE o.order_id = oi.order_id
AND o.customer_id = :Px_CUSTOMER_ID
AND oi.product_id = pi.product_id
GROUP BY pi.product_name,
  pi.product_description,
  oi.unit_price
ORDER BY pi.product_name

Edit classic report region and add to "Page Items to Submit" hidden item Px_CUSTOMER_ID and to Region Attributes:

style="display:none"

Create dynamic action. Select Advanced:

  • Name: Show detail report
  • Event: Change
  • Selection Type: jQuery Selector
  • jQuery Selector: #apexir_DETAIL
  • Condition: JavaScript Expression
  • Value:
        $v("apexir_current_row").length > 0 && $v("apexir_current_row") !== $v("Px_CUSTOMER_ID")

  • Action: Execute JavaScript code
  • Fire On Page Load: False
  • Code:
        $s("Px_CUSTOMER_ID",$v("apexir_current_row"));

  • False Action: No False Action
  • Selection Type: None

Edit dynamic action and Add True Action:

  • Action: Refresh
  • Fire On Page Load: False
  • Selection Type: Region
  • Region: {select your classic report region}

Add another True Action:

  • Action: Show
  • Fire On Page Load: False
  • Selection Type: Region
  • Region: {select your classic report region}

Create new dynamic action. Select Advanced:

  • Name: Hide detail report
  • Event: Click
  • Selection Type: jQuery Selector
  • jQuery Selector: #apexir_btn_REPORT_VIEW
  • Condition: No Condition
  • Action: Hide
  • Fire On Page Load: False
  • Selection Type: Region
  • Region: {select your classic report region}

Edit new dynamic action and change Event Scope to "Live" and add another True Action:

  • Action: Execute JavaScript code
  • Fire On Page Load: False
  • Code:
$s("Px_CUSTOMER_ID","");

Now run page and access to interactive report single row view to see detail report.

IR Single Row View

See working example.

Comments

  • Johnny 11-FEB-14 10.23.05.000000 PM

    Awesome, that worked for me perfectly.

    It's a nice addition i would not have been able to incorporate w/out your writeup.

  • Jim 31-JUL-12 01.42.25.000000 AM
    Jari, That did work, when I manually created the tabular form. Thank you very much for your help! Best, -Jim
  • Jari Laine 29-JUL-12 11.29.58.000000 AM

    Hi Jim,

    For me it looks like that you can not use wizard created tabular from. I do not know why. It seems to me that tabular form query is "cached" when page loads. Even you change item session state value in where condition, and refresh report using dynamic action, form returns same rows as in page load. I did one new page (999) in your app. you can check that.

    For workaround you can create manual tabular form. That should work.

    Regards, Jari

  • Jim 27-JUL-12 09.44.11.000000 PM

    Login details: workspace: xxx username: xxx password: xxx

  • Jim 27-JUL-12 09.38.06.000000 PM

    Jari, Thanks for the quick reply. Here is a link for what I'm trying to accomplish:

    https://apex.oracle.com/pls/apex/f?p=36949:1

    I created two regions - one using the interactive report and a second using the tabular form. Simply changing the region for the Dynamic Actions created, shows one working (the interactive report) and the second not (the tabular form). It appears that the SQL query is not being updated based on the :PX_ORDER_ID passed, for the tabular form. Thank you, -JIm

  • Jari Laine 27-JUL-12 08.20.16.000000 PM

    Hi Jim,

    Thanks.

    If you create example about problem to apex.oracle.com and share developer login details to workspace I can try see what is problem.

    Regards, Jari

  • Jim 27-JUL-12 07.14.57.000000 PM
    Very good read. I tried to incorporate this same methodology using a tabular form rather than a classic report, without success. Could you provide some guidance on how I might do this?