Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Master Detail Report - XML output is incorrect.

Received Response
41
Views
6
Comments
Jacobelia S
Jacobelia S Rank 5 - Community Champion
in Oracle Analytics Cloud and Server

Summary

Master Detail Report - XML output is incorrect.

Content

Hello,


I have the following contents in the datatemplate.xml

<?xml version="1.0" encoding="UTF-8"?><dataTemplate name="PRODS" defaultPackage="" version="1.0">   <parameters>      <parameter name="p_prod" dataType="character" />   </parameters>   <dataQuery>      <sqlStatement name="Q_MAST"><![CDATA[SELECT product_id, prod_seq, product_name FROM prod_mast   WHERE product_id=:p_prod]]></sqlStatement>      <sqlStatement name="Q_DET"><![CDATA[   SELECT product_id,prod_det_seq,product_detail,prod_mast_seq FROM prod_det WHERE product_id=:p_prod   ]]></sqlStatement></dataQuery>   <dataStructure>      <group name="G_MAST" source="Q_MAST">         <element name="product_id" value="product_id" />         <element name="prod_seq" value="prod_seq" />         <element name="product_name" value="product_name" />         <group name="G_DET" source="Q_DET">            <element name="product_id" value="product_id" />            <element name="prod_det_seq" value="prod_det_seq" />            <element name="product_detail" value="product_detail" />            <element name="prod_mast_seq" value="prod_mast_seq" />         </group>      </group>   </dataStructure></dataTemplate>

The XML output generated based on the above is as follows

<?xml version="1.0" encoding="UTF-8"?><PRODS>   <LIST_G_MAST>      <G_MAST>         <PRODUCT_ID>SB1002</PRODUCT_ID>         <PROD_SEQ>1002</PROD_SEQ>         <PRODUCT_NAME>Square Boxes</PRODUCT_NAME>         <LIST_G_DET>            <G_DET>               <PRODUCT_ID>SB1002</PRODUCT_ID>               <PROD_DET_SEQ>1002.1</PROD_DET_SEQ>               <PRODUCT_DETAIL>12 inch box</PRODUCT_DETAIL>               <PROD_MAST_SEQ>1002</PROD_MAST_SEQ>            </G_DET>            <G_DET>               <PRODUCT_ID>SB1002</PRODUCT_ID>               <PROD_DET_SEQ>1002.2</PROD_DET_SEQ>               <PRODUCT_DETAIL>24 inch box</PRODUCT_DETAIL>               <PROD_MAST_SEQ>1002</PROD_MAST_SEQ>            </G_DET>            <G_DET>               <PRODUCT_ID>SB2006</PRODUCT_ID>               <PROD_DET_SEQ>2006.1</PROD_DET_SEQ>               <PRODUCT_DETAIL>Small size box</PRODUCT_DETAIL>               <PROD_MAST_SEQ>2006</PROD_MAST_SEQ>            </G_DET>            <G_DET>               <PRODUCT_ID>SB2006</PRODUCT_ID>               <PROD_DET_SEQ>2006.2</PROD_DET_SEQ>               <PRODUCT_DETAIL>Medium size box</PRODUCT_DETAIL>               <PROD_MAST_SEQ>2006</PROD_MAST_SEQ>            </G_DET>         </LIST_G_DET>      </G_MAST>      <G_MAST>         <PRODUCT_ID>SB2006</PRODUCT_ID>         <PROD_SEQ>2006</PROD_SEQ>         <PRODUCT_NAME>Misc. Boxes</PRODUCT_NAME>         <LIST_G_DET>            <G_DET>               <PRODUCT_ID>SB1002</PRODUCT_ID>               <PROD_DET_SEQ>1002.1</PROD_DET_SEQ>               <PRODUCT_DETAIL>12 inch box</PRODUCT_DETAIL>               <PROD_MAST_SEQ>1002</PROD_MAST_SEQ>            </G_DET>            <G_DET>               <PRODUCT_ID>SB1002</PRODUCT_ID>               <PROD_DET_SEQ>1002.2</PROD_DET_SEQ>               <PRODUCT_DETAIL>24 inch box</PRODUCT_DETAIL>               <PROD_MAST_SEQ>1002</PROD_MAST_SEQ>            </G_DET>            <G_DET>               <PRODUCT_ID>SB2006</PRODUCT_ID>               <PROD_DET_SEQ>2006.1</PROD_DET_SEQ>               <PRODUCT_DETAIL>Small size box</PRODUCT_DETAIL>               <PROD_MAST_SEQ>2006</PROD_MAST_SEQ>            </G_DET>            <G_DET>               <PRODUCT_ID>SB2006</PRODUCT_ID>               <PROD_DET_SEQ>2006.2</PROD_DET_SEQ>               <PRODUCT_DETAIL>Medium size box</PRODUCT_DETAIL>               <PROD_MAST_SEQ>2006</PROD_MAST_SEQ>            </G_DET>         </LIST_G_DET>      </G_MAST>   </LIST_G_MAST></PRODS>

The output is what is not what is expected,

Ideally, I would like to have the detail for the corresponding or related PROD_SEQ, the expected XML output is as follows

<?xml version="1.0" encoding="UTF-8"?>
<PRODS>
   <LIST_G_MAST>
      <G_MAST>
         <PRODUCT_ID>SB1002</PRODUCT_ID>
         <PROD_SEQ>1002</PROD_SEQ>
         <PRODUCT_NAME>Square Boxes</PRODUCT_NAME>
         <LIST_G_DET>
            <G_DET>
               <PRODUCT_ID>SB1002</PRODUCT_ID>
               <PROD_DET_SEQ>1002.1</PROD_DET_SEQ>
               <PRODUCT_DETAIL>12 inch box</PRODUCT_DETAIL>
               <PROD_MAST_SEQ>1002</PROD_MAST_SEQ>
            </G_DET>
            <G_DET>
               <PRODUCT_ID>SB1002</PRODUCT_ID>
               <PROD_DET_SEQ>1002.2</PROD_DET_SEQ>
               <PRODUCT_DETAIL>24 inch box</PRODUCT_DETAIL>
               <PROD_MAST_SEQ>1002</PROD_MAST_SEQ>
            </G_DET>
         </LIST_G_DET>
      </G_MAST>
      <G_MAST>
         <PRODUCT_ID>SB2006</PRODUCT_ID>
         <PROD_SEQ>2006</PROD_SEQ>
         <PRODUCT_NAME>Misc. Boxes</PRODUCT_NAME>
         <LIST_G_DET>
            <G_DET>
               <PRODUCT_ID>SB2006</PRODUCT_ID>
               <PROD_DET_SEQ>2006.1</PROD_DET_SEQ>
               <PRODUCT_DETAIL>Small size box</PRODUCT_DETAIL>
               <PROD_MAST_SEQ>2006</PROD_MAST_SEQ>
            </G_DET>
            <G_DET>
               <PRODUCT_ID>SB2006</PRODUCT_ID>
               <PROD_DET_SEQ>2006.2</PROD_DET_SEQ>
               <PRODUCT_DETAIL>Medium size box</PRODUCT_DETAIL>
               <PROD_MAST_SEQ>2006</PROD_MAST_SEQ>
            </G_DET>
         </LIST_G_DET>
      </G_MAST>
   </LIST_G_MAST>
</PRODS>

How can I achieve the above output?

Appreciate any insight

Answers

  • Brajesh Shukla-95078
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    I think prod_mast_seq in prod_det contains the proj_seq of master table so you can do the below

    <dataQuery>

          <sqlStatement name="Q_MAST"><![CDATA[SELECT product_id, prod_seq, product_name FROM prod_mast

       WHERE product_id=:p_prod]]></sqlStatement>

          <sqlStatement name="Q_DET"><![CDATA[

       SELECT product_id,prod_det_seq,product_detail,prod_mast_seq FROM prod_det WHERE product_id=:product_id  and prod_mast_seq = :prod_seq

       ]]></sqlStatement>

    </dataQuery>

  • Brajesh Shukla-95078
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    Can you just copy paste your dataQuery only?

  • Brajesh Shukla-95078
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    Do you have prod_seq column in table prod_det?

    If you have you can pass the prod_seq from the parent sql to child sql as below. This will restrict child sql to pick the value related to parent sql

    <dataQuery> 

          <sqlStatement name="Q_MAST"><![CDATA[SELECT product_id, prod_seq, product_name FROM prod_mast

       WHERE product_id=:p_prod]]></sqlStatement> 

          <sqlStatement name="Q_DET"><![CDATA[

       SELECT product_id,prod_det_seq,product_detail,prod_mast_seq FROM prod_det WHERE product_id=:p_prod  and prod_seq = :prod_seq

       ]]></sqlStatement> 

    </dataQuery>

  • Jacobelia S
    Jacobelia S Rank 5 - Community Champion

    I am actual dataQuery is different from what I had posted in the question as the question contain pseudo code, replica of my original.

    Can I email you my original dataQuery if this is not an intrusion?

  • Jacobelia S
    Jacobelia S Rank 5 - Community Champion


    I reckon that bind variable should not be passed as a parameter.

  • Jacobelia S
    Jacobelia S Rank 5 - Community Champion

    Thanks for the update.

    When I tried to provide the bind variable in the detail query, I am not getting the details in the XML output, only master information I appearing.

    Regards