Forum Stats

  • 3,853,257 Users
  • 2,264,198 Discussions
  • 7,905,296 Comments

Discussions

Custom actions in nested displays: can an item be tied to the child display in which it is defined?

user9540031
user9540031 Member Posts: 228 Gold Badge

Hello,

I have defined a report (*) with 2 displays (parent and child), and I'd like to add specific custom actions to each display.

(*) Actually, it started as a report, but because custom actions cannot be created in the Report editor to begin with (nor are they well handled by it once added, as shown in this post) now it's an individual XML extension, of type report.

The report is as follows:

In the parent display, on top, I'll add a custom action named "Process Order..."; in the child display (bottom), the custom action will be named "Process Item...".

So, on with my text editor, into the XML markup... I added the <item> element for the "Process Order..." action to the parent <display> element, and the <item> for the "Process Item..." action to the nested (child) <display> element.

Here's how the "Process Item..." action looks like, if triggered from the 2nd line on the child ("Ordered Items") display:

Everything is fine, so far.

But...

Problem: the "Process Item..." custom action, which was intended for the child display only, also appears in the context menu of the parent display:

And of course, it does not belong there, as it needs information that will only be provided if called from the child display; that information is unavailable in the context of the parent. Here's how the action dialog looks like, if opened from the parent:

The #SEQ_NUM#, #"PRODUCT"#, and #QTY# placeholders could not be substituted, as the corresponding columns don't exist in the parent display.

Is there a way to prevent an item element defined in a nested display element from appearing in the context menu of the parent display?

(Or did I make mistakes when coding it?)

Thanks in advance.

The code of the test report with custom actions is as follows:

<?xml version="1.0" encoding="UTF-8" ?>
<displays>
<folder>
<name><![CDATA[__Test__]]></name>
<folder>
<name><![CDATA[Fancy reports]]></name>
<display id="3fecd27c-0181-1000-8001-c0a8010a89b9" type="" style="Table" enable="true">
    <!--
        Top-level display (aka: "parent" report)
     -->
    <name><![CDATA[Client orders]]></name>
    <description><![CDATA[]]></description>
    <tooltip><![CDATA[]]></tooltip>
    <drillclass><![CDATA[]]></drillclass>
    <CustomValues>
        <TYPE><![CDATA[horizontal]]></TYPE>
    </CustomValues>
    <query>
        <sql><![CDATA[with
orders ( order_id, client_id, order_date ) as (
    select 100  , 9000  , date '1970-01-01'     from dual   union all
    select 101  , 9001  , date '2021-12-31'     from dual   union all
    select 102  , 9001  , date '2022-06-08'     from dual
)
select o.order_id
     , o.client_id
     , o.order_date
  from orders o
 order by o.order_date]]></sql>
    </query>
    <item reload="true" reloadparent="false" removeFromParent="false">
        <!--
            Context-menu action in the top-level display
          -->
        <title>Process Order...</title>
        <help>Sample action at the Order level.</help>
        <prompt type="confirm">
            <label>Confirm action? (Order: #ORDER_ID#, client id: #CLIENT_ID#, date: #ORDER_DATE#)</label>
        </prompt>
        <sql><![CDATA[begin
    /*
       Process this order
       ( order_id: #ORDER_ID#
         client_id: #CLIENT_ID#
         date: #ORDER_DATE# )
     */
    null;
end;]]></sql>
    </item>
    <display id="null" type="" style="Table" enable="true">
        <!--
            Nested display (aka: "child" report)
         -->
        <name><![CDATA[Ordered items]]></name>
        <description><![CDATA[]]></description>
        <tooltip><![CDATA[]]></tooltip>
        <drillclass><![CDATA[]]></drillclass>
        <CustomValues>
            <TYPE><![CDATA[horizontal]]></TYPE>
        </CustomValues>
        <query>
            <sql><![CDATA[with
items ( order_id, seq_num, product, qty, unit_price ) as (
    select 100  , 8000010   , '2% Milk 1L'      , 6     , 1.50  from dual   union all
    select 101  , 8000015   , '4% Milk 1L'      , 1     , 1.65  from dual   union all
    select 101  , 8000020   , 'Apples 1kg'      , 2     , 4.50  from dual   union all
    select 102  , 8000016   , 'Lettuce 0.5kg'   , 2     , 1.40  from dual   union all
    select 102  , 8000017   , 'Orange juice 1L' , 1     , 3.45  from dual
)
select i.order_id
     , :CLIENT_ID                       as client_id
     , i.seq_num
     , i.product
     , i.qty
     , i.unit_price
     , i.qty * i.unit_price             as line_price
     , sum(i.qty * i.unit_price)  
            over (partition by i.order_id
                  order by i.seq_num)   as running_total
 from items i
where i.order_id = :ORDER_ID
order by i.seq_num]]></sql>
            <binds>
                <bind id="CLIENT_ID">
                    <prompt><![CDATA[CLIENT_ID]]></prompt>
                    <tooltip><![CDATA[CLIENT_ID]]></tooltip>
                    <value><![CDATA[NULL_VALUE]]></value>
                    <bracket><![CDATA[null]]></bracket>
                </bind>
                <bind id="ORDER_ID">
                    <prompt><![CDATA[ORDER_ID]]></prompt>
                    <tooltip><![CDATA[ORDER_ID]]></tooltip>
                    <value><![CDATA[NULL_VALUE]]></value>
                    <bracket><![CDATA[null]]></bracket>
                </bind>
            </binds>
        </query>
        <!--
            Context-menu action in the nested display...
            But, can we prevent it from also showing up
            in the top-level display?
         -->
        <item reload="true" reloadparent="false" removeFromParent="false">
            <title>Process Item...</title>
            <help>Sample action at the Ordered Items level.</help>
            <prompt type="confirm">
                <label>Confirm action? (Order: #ORDER_ID#, line seq: #SEQ_NUM#, product: #"PRODUCT"#)</label>
            </prompt>
            <sql><![CDATA[begin
    /*
       Process this item
       ( order_id: #ORDER_ID#
         seq_num: #SEQ_NUM#
         product: #"PRODUCT"#
         qty: #QTY# )
     */
    null;   
end;]]></sql>
        </item>
    </display>
</display>
</folder>
</folder>
</displays>

If you want to try it, save the code as an XML document (e.g. test-client_orders.xml) then add it to SQL Developer as a user-defined XML extensions, of type REPORT (menu Tools -> Preferences.., then Database::User Defined Extensions panel), and restart SQL Developer.

Regards,