Grouping in RTF

924768

Hi,

I am trying to make a create a report, for which I have two groups in XML file

Group 1 has Bill ID, RefID.

Group 2 has Code,Bill Amt, Total Amt

I cannot flatten the query and convert it to one query as relation between Bill ID,RefID and code is 1 to N ie one Bill can have many codes

so sub query gives me error like "ORA-01427: single-row subquery returns more than one row"

so I have created two query Group's

When I Try to achieve this by grouping in RTF Format template it gives me either

1) Data of only group 1

2) Either Data of Only group 2

3) or No data

based on position of start and end of the group.

Can any one help me on how to group this in RTF

my report structure is like

BID	REFID	Code	Bill Amt	Total Amt
I123	S345	s1	124	124
		T32	34	34
		Total	XXX	XXX

Venkat Thota - BIP

Is there any common column between two queries ? if possible please provide query structure so that we can try to achieve requirement either in query level or data model level or in RTF template.also two data model has common column then you can achieve this at data model level.Thanks

please upload xml and template.

924768

Thanks for reply

I tried by making 3 queries as well but same result.

Actually there are few more columns which are based on Status column as shown below. in Q3 of below query (for eg GLOG_UTIL.REMOVE_DOMAIN(ILIR.INVOICE_LI_REFNUM_QUAL_GID)='SERVICE' WILL BE ONE MORE COLUMN)

Please find below Query Template

<?xml version="1.0" encoding="UTF-8"?>

<dataTemplate name="Demo_Invoice_Report" defaultPackage="pkg_invoice_rep" version="1.0">

<properties>

        <property name="xml_tag_case" value="upper" />

</properties>

    <parameters>

        <parameter name="P_GL_USER" dataType="character" defaultValue="DBA.ADMIN" />

        <parameter name="P_ROLE_ID" dataType="character" defaultValue="ADMIN" />

        <parameter name="P_L_INVOICE_ID" dataType="character" defaultValue="1=1" />

       

    </parameters>

    <lexicals/>

<dataQuery>

<sqlStatement name="Q1">

<![CDATA[

SELECT I.INVOICE_XID AS "INVID" from INVOICE I WHERE I.INVOICE_GID=:P_L_INVOICE_ID

]]>

</sqlStatement>

<sqlStatement name="Q2">

<![CDATA[

SELECT GLOG_UTIL.REMOVE_DOMAIN(INS.SHIPMENT_GID) AS "SHIPMENTNO" FROM INVOICE_SHIPMENT INS,INVOICE I WHERE INS.INVOICE_GID=I.INVOICE_GID AND I.INVOICE_GID=:P_L_INVOICE_ID

]]>

</sqlStatement>

<sqlStatement name="Q3">

<![CDATA[

SELECT ILIR.REFERENCE_NUMBER AS "MFG",sum(INL.FREIGHT_CHARGE) AS "BASEAMT"

FROM INVOICE_LINEITEM_REFNUM ILIR,INVOICE_LINEITEM INL,INVOICE I

WHERE GLOG_UTIL.REMOVE_DOMAIN(ILIR.INVOICE_LI_REFNUM_QUAL_GID)='MAT_FREIGHT' and INL.INVOICE_GID=I.INVOICE_GID

AND ILIR.INVOICE_GID=I.INVOICE_GID AND I.INVOICE_GID=:P_L_INVOICE_ID

group by ILIR.REFERENCE_NUMBER

]]>

</sqlStatement>

</dataQuery>

<dataTrigger name="afterParameterFormTrigger" source="pkg_invoice_rep.afterpform" />

<dataTrigger name="beforeReportTrigger" source="pkg_invoice_rep.beforereport" />

<dataStructure>

<group name="G1" dataType="varchar2" source="Q1">

  <element name="INVID" dataType="varchar2" value="INVID" />

</group>

<group name="G_2" dataType="varchar2" source="Q2">

  <element name="SHIPMENTNO" dataType="varchar2" value="SHIPMENTNO" />

</group>

<group name="G_3" dataType="varchar2" source="Q3">

  <element name="MFG" dataType="varchar2" value="MFG" />

  <element name="BASEAMT" dataType="varchar2" value="BASEAMT" />

</group>

</dataStructure>

<dataTrigger name="afterReportTrigger" source="pkg_invoice_rep.afterreport()" />

</dataTemplate>

Format template

INVID	SHIPMENTNO	MFG	BASEAMT
I INVID	S   SHIPMENTNO	M MFG	BASEAMT EM   ES EI

No data is retrieved

Kindly Advice.