How to fetch/use grouping for a XML element that has multiple rows

0
votes

I am trying to create an Oracle BI Publisher report (using the Microsoft Word plugin). The report is being created from an XML file which is created from PeopleSoft. The XML files contains many rows (I've included some sample data). I have a field named A.OFFER_AMT that I am trying to display this field for the row that has another field named A.DESCRSHORT = 'SignOn', as well as when A.DESCRSHORT = 'BaseSalary'. So I am trying to display 2 different values from A.DESCRSHORT, and I imagine I need to use some sort of grouping, but I have tried a few different groupings with the Plug in and am only able to get the 1st row fetched from the query value to display on the report output.

I have added IF conditional statements in the template, within the text block that I am trying to get the specific value, however when I preview the report I only get data from the 1st row. Thanks for any assistance.

<?xml version='1.0'?>
<query numrows="3" queryname="GH3_HRS_OFFER_DS" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="">
<row rownumber="1">
<A.HRS_PERSON_ID>5773</A.HRS_PERSON_ID>
<A.HRS_RCMNT_ID>303670</A.HRS_RCMNT_ID>
<A.HRS_OFF_ID>7527</A.HRS_OFF_ID>
<A.POSITION_NBR><![CDATA[41622315]]></A.POSITION_NBR>
<A.OFFER_DT>2019-11-21</A.OFFER_DT>
<A.HRS_PROP_ST_DT>2019-12-01</A.HRS_PROP_ST_DT>
<A.HRS_OFR_DT_EXP>2019-11-28</A.HRS_OFR_DT_EXP>
<A.HRS_ONLN_OFR_POSTD><![CDATA[]]></A.HRS_ONLN_OFR_POSTD>
<A.JOBCODE><![CDATA[3902]]></A.JOBCODE>
<A.HRS_HIRING_MGR_ID><![CDATA[CH602575]]></A.HRS_HIRING_MGR_ID>
<A.RECRUITER_ID><![CDATA[552717]]></A.RECRUITER_ID>
<A.WC_COUNCIL_ID><![CDATA[]]></A.WC_COUNCIL_ID>
<A.OFFER_AMT>37</A.OFFER_AMT>
<A.CURRENCY_CD><![CDATA[USD]]></A.CURRENCY_CD>
<A.OFFER_FREQUENCY><![CDATA[H]]></A.OFFER_FREQUENCY>
<A.ERNCD><![CDATA[]]></A.ERNCD>
<A.EARNS_AMT>0</A.EARNS_AMT>
<A.LOCATION><![CDATA[]]></A.LOCATION>
<A.OFFER_COMPONENT><![CDATA[BASSAL]]></A.OFFER_COMPONENT>
<A.EFFDT>1901-01-01</A.EFFDT>
<A.DESCR50_1><![CDATA[Base Salary]]></A.DESCR50_1>
<A.DESCRSHORT><![CDATA[BaseSalary]]></A.DESCRSHORT>
<A.OFFER_TYPE><![CDATA[BAS SAL]]></A.OFFER_TYPE>
<A.MONETARY_IND><![CDATA[Y]]></A.MONETARY_IND>
<A.APP_PER_STATUS><![CDATA[A]]></A.APP_PER_STATUS>
<A.EMPLID><![CDATA[709741]]></A.EMPLID>
<A.HRS_EXT_TRN><![CDATA[N]]></A.HRS_EXT_TRN>
<A.POI_TYPE><![CDATA[00000]]></A.POI_TYPE>
<A.COUNTRY_NM_FORMAT><![CDATA[001]]></A.COUNTRY_NM_FORMAT>
<A.NAME><![CDATA[Sara Simplehead]]></A.NAME>
<A.NAME_INITIALS><![CDATA[]]></A.NAME_INITIALS>
<A.NAME_PREFIX><![CDATA[]]></A.NAME_PREFIX>
<A.NAME_SUFFIX><![CDATA[]]></A.NAME_SUFFIX>
<A.NAME_ROYAL_PREFIX><![CDATA[]]></A.NAME_ROYAL_PREFIX>
<A.NAME_TITLE><![CDATA[]]></A.NAME_TITLE>
<A.LAST_NAME><![CDATA[Simplehead]]></A.LAST_NAME>
<A.FIRST_NAME><![CDATA[Sara]]></A.FIRST_NAME>
<A.MIDDLE_NAME><![CDATA[A.]]></A.MIDDLE_NAME>
<A.NAME_DISPLAY><![CDATA[Sara Simplehead]]></A.NAME_DISPLAY>
<A.NAME_FORMAL><![CDATA[Sara Simplehead]]></A.NAME_FORMAL>
<A.ADDRESS_TYPE><![CDATA[HOME]]></A.ADDRESS_TYPE>
<A.COUNTRY><![CDATA[USA]]></A.COUNTRY>
<A.ADDRESS1><![CDATA[140 test drive]]></A.ADDRESS1>
<A.ADDRESS2><![CDATA[]]></A.ADDRESS2>
<A.ADDRESS3><![CDATA[]]></A.ADDRESS3>
<A.ADDRESS4><![CDATA[]]></A.ADDRESS4>
<A.CITY><![CDATA[anytown]]></A.CITY>
<A.NUM1><![CDATA[]]></A.NUM1>
<A.NUM2><![CDATA[]]></A.NUM2>
<A.HOUSE_TYPE><![CDATA[]]></A.HOUSE_TYPE>
<A.COUNTY><![CDATA[chemung]]></A.COUNTY>
<A.STATE><![CDATA[NY]]></A.STATE>
<A.POSTAL><![CDATA[14845]]></A.POSTAL>
<A.DESCR><![CDATA[New York]]></A.DESCR>
<A.SEQUENCE_NUMBER>2</A.SEQUENCE_NUMBER>
<A.HRS_DESCR><![CDATA[Cash]]></A.HRS_DESCR>
<A.HRS_JOB_OPENING_ID>115298</A.HRS_JOB_OPENING_ID>
<A.DESCR1><![CDATA[Anytown Hospital]]></A.DESCR1>
<A.ADDRESS1_2><![CDATA[One Test Drive]]></A.ADDRESS1_2>
<A.CITY2><![CDATA[Anytown]]></A.CITY2>
<A.STATE2><![CDATA[NY]]></A.STATE2>
<A.POSTAL2><![CDATA[14830]]></A.POSTAL2>
<A.DESCR2><![CDATA[Registered Nurse]]></A.DESCR2>
<A.DESCR3><![CDATA[Nursing Unit - LDRP]]></A.DESCR3>
<A.CASE_IDENTIFIER><![CDATA[Full Time]]></A.CASE_IDENTIFIER>
<A.STATUS_DT>2017-02-07</A.STATUS_DT>
<A.NAME52><![CDATA[Susie Smith]]></A.NAME52>
<A.JOBTITLE><![CDATA[Sr. Recruiter]]></A.JOBTITLE>
<A.CASE_NUMBER_AUS><![CDATA[Non-exempt]]></A.CASE_NUMBER_AUS>
<A.CASE_NUMBER_NZL><![CDATA[ ###-###-###]]></A.CASE_NUMBER_NZL>
<A.STD_HOURS>36</A.STD_HOURS>
<A.GRADE><![CDATA[99]]></A.GRADE>
<A.STD_HOURS_NEW>.9</A.STD_HOURS_NEW>
<A.GH_SESSION_DATE></A.GH_SESSION_DATE>
<A.SAL_ADMIN_PLAN><![CDATA[UNH]]></A.SAL_ADMIN_PLAN>
<A.STEP_FROM>0</A.STEP_FROM>
<A.DATE>2019-11-22</A.DATE>
</row>
<row rownumber="2">
<A.HRS_PERSON_ID>5773</A.HRS_PERSON_ID>
<A.HRS_RCMNT_ID>303670</A.HRS_RCMNT_ID>
<A.HRS_OFF_ID>7527</A.HRS_OFF_ID>
<A.POSITION_NBR><![CDATA[41622315]]></A.POSITION_NBR>
<A.OFFER_DT>2019-11-21</A.OFFER_DT>
<A.HRS_PROP_ST_DT>2019-12-01</A.HRS_PROP_ST_DT>
<A.HRS_OFR_DT_EXP>2019-11-28</A.HRS_OFR_DT_EXP>
<A.HRS_ONLN_OFR_POSTD><![CDATA[]]></A.HRS_ONLN_OFR_POSTD>
<A.JOBCODE><![CDATA[3902]]></A.JOBCODE>
<A.HRS_HIRING_MGR_ID><![CDATA[CH602575]]></A.HRS_HIRING_MGR_ID>
<A.RECRUITER_ID><![CDATA[552717]]></A.RECRUITER_ID>
<A.WC_COUNCIL_ID><![CDATA[]]></A.WC_COUNCIL_ID>
<A.OFFER_AMT>36.5</A.OFFER_AMT>
<A.CURRENCY_CD><![CDATA[USD]]></A.CURRENCY_CD>
<A.OFFER_FREQUENCY><![CDATA[H]]></A.OFFER_FREQUENCY>
<A.ERNCD><![CDATA[]]></A.ERNCD>
<A.EARNS_AMT>0</A.EARNS_AMT>
<A.LOCATION><![CDATA[]]></A.LOCATION>
<A.OFFER_COMPONENT><![CDATA[PROB]]></A.OFFER_COMPONENT>
<A.EFFDT>1903-01-01</A.EFFDT>
<A.DESCR50_1><![CDATA[Probationary Rate]]></A.DESCR50_1>
<A.DESCRSHORT><![CDATA[Probationa]]></A.DESCRSHORT>
<A.OFFER_TYPE><![CDATA[PROB]]></A.OFFER_TYPE>
<A.MONETARY_IND><![CDATA[Y]]></A.MONETARY_IND>
<A.APP_PER_STATUS><![CDATA[A]]></A.APP_PER_STATUS>
<A.EMPLID><![CDATA[709741]]></A.EMPLID>
<A.HRS_EXT_TRN><![CDATA[N]]></A.HRS_EXT_TRN>
<A.POI_TYPE><![CDATA[00000]]></A.POI_TYPE>
<A.COUNTRY_NM_FORMAT><![CDATA[001]]></A.COUNTRY_NM_FORMAT>
<A.NAME><![CDATA[Sara Simplehead]]></A.NAME>
<A.NAME_INITIALS><![CDATA[]]></A.NAME_INITIALS>
<A.NAME_PREFIX><![CDATA[]]></A.NAME_PREFIX>
<A.NAME_SUFFIX><![CDATA[]]></A.NAME_SUFFIX>
<A.NAME_ROYAL_PREFIX><![CDATA[]]></A.NAME_ROYAL_PREFIX>
<A.NAME_TITLE><![CDATA[]]></A.NAME_TITLE>
<A.LAST_NAME><![CDATA[Simplehead]]></A.LAST_NAME>
<A.FIRST_NAME><![CDATA[Sara]]></A.FIRST_NAME>
<A.MIDDLE_NAME><![CDATA[A.]]></A.MIDDLE_NAME>
<A.NAME_DISPLAY><![CDATA[Sara Simplehead]]></A.NAME_DISPLAY>
<A.NAME_FORMAL><![CDATA[Sara Simplehead]]></A.NAME_FORMAL>
<A.ADDRESS_TYPE><![CDATA[HOME]]></A.ADDRESS_TYPE>
<A.COUNTRY><![CDATA[USA]]></A.COUNTRY>
<A.ADDRESS1><![CDATA[140 test drive]]></A.ADDRESS1>
<A.ADDRESS2><![CDATA[]]></A.ADDRESS2>
<A.ADDRESS3><![CDATA[]]></A.ADDRESS3>
<A.ADDRESS4><![CDATA[]]></A.ADDRESS4>
<A.CITY><![CDATA[anytown]]></A.CITY>
<A.NUM1><![CDATA[]]></A.NUM1>
<A.NUM2><![CDATA[]]></A.NUM2>
<A.HOUSE_TYPE><![CDATA[]]></A.HOUSE_TYPE>
<A.COUNTY><![CDATA[chemung]]></A.COUNTY>
<A.STATE><![CDATA[NY]]></A.STATE>
<A.POSTAL><![CDATA[14845]]></A.POSTAL>
<A.DESCR><![CDATA[New York]]></A.DESCR>
<A.SEQUENCE_NUMBER>1</A.SEQUENCE_NUMBER>
<A.HRS_DESCR><![CDATA[Cash]]></A.HRS_DESCR>
<A.HRS_JOB_OPENING_ID>115298</A.HRS_JOB_OPENING_ID>
<A.DESCR1><![CDATA[Anytown Hospital]]></A.DESCR1>
<A.ADDRESS1_2><![CDATA[One Test Drive]]></A.ADDRESS1_2>
<A.CITY2><![CDATA[Anytown]]></A.CITY2>
<A.STATE2><![CDATA[NY]]></A.STATE2>
<A.POSTAL2><![CDATA[14830]]></A.POSTAL2>
<A.DESCR2><![CDATA[Registered Nurse]]></A.DESCR2>
<A.DESCR3><![CDATA[Nursing Unit - LDRP]]></A.DESCR3>
<A.CASE_IDENTIFIER><![CDATA[Full Time]]></A.CASE_IDENTIFIER>
<A.STATUS_DT>2017-02-07</A.STATUS_DT>
<A.NAME52><![CDATA[Susie Smith]]></A.NAME52>
<A.JOBTITLE><![CDATA[Sr. Recruiter]]></A.JOBTITLE>
<A.CASE_NUMBER_AUS><![CDATA[Non-exempt]]></A.CASE_NUMBER_AUS>
<A.CASE_NUMBER_NZL><![CDATA[ ###-###-###]]></A.CASE_NUMBER_NZL>
<A.STD_HOURS>36</A.STD_HOURS>
<A.GRADE><![CDATA[99]]></A.GRADE>
<A.STD_HOURS_NEW>.9</A.STD_HOURS_NEW>
<A.GH_SESSION_DATE></A.GH_SESSION_DATE>
<A.SAL_ADMIN_PLAN><![CDATA[UNH]]></A.SAL_ADMIN_PLAN>
<A.STEP_FROM>0</A.STEP_FROM>
<A.DATE>2019-11-22</A.DATE>
</row>
<row rownumber="3">
<A.HRS_PERSON_ID>5773</A.HRS_PERSON_ID>
<A.HRS_RCMNT_ID>303670</A.HRS_RCMNT_ID>
<A.HRS_OFF_ID>7527</A.HRS_OFF_ID>
<A.POSITION_NBR><![CDATA[41622315]]></A.POSITION_NBR>
<A.OFFER_DT>2019-11-21</A.OFFER_DT>
<A.HRS_PROP_ST_DT>2019-12-01</A.HRS_PROP_ST_DT>
<A.HRS_OFR_DT_EXP>2019-11-28</A.HRS_OFR_DT_EXP>
<A.HRS_ONLN_OFR_POSTD><![CDATA[]]></A.HRS_ONLN_OFR_POSTD>
<A.JOBCODE><![CDATA[3902]]></A.JOBCODE>
<A.HRS_HIRING_MGR_ID><![CDATA[CH602575]]></A.HRS_HIRING_MGR_ID>
<A.RECRUITER_ID><![CDATA[552717]]></A.RECRUITER_ID>
<A.WC_COUNCIL_ID><![CDATA[]]></A.WC_COUNCIL_ID>
<A.OFFER_AMT>5000</A.OFFER_AMT>
<A.CURRENCY_CD><![CDATA[USD]]></A.CURRENCY_CD>
<A.OFFER_FREQUENCY><![CDATA[O]]></A.OFFER_FREQUENCY>
<A.ERNCD><![CDATA[]]></A.ERNCD>
<A.EARNS_AMT>0</A.EARNS_AMT>
<A.LOCATION><![CDATA[]]></A.LOCATION>
<A.OFFER_COMPONENT><![CDATA[SIGNON]]></A.OFFER_COMPONENT>
<A.EFFDT>1901-01-01</A.EFFDT>
<A.DESCR50_1><![CDATA[Sign On Bonus]]></A.DESCR50_1>
<A.DESCRSHORT><![CDATA[SignOn]]></A.DESCRSHORT>
<A.OFFER_TYPE><![CDATA[SIGNON]]></A.OFFER_TYPE>
<A.MONETARY_IND><![CDATA[Y]]></A.MONETARY_IND>
<A.APP_PER_STATUS><![CDATA[A]]></A.APP_PER_STATUS>
<A.EMPLID><![CDATA[709741]]></A.EMPLID>
<A.HRS_EXT_TRN><![CDATA[N]]></A.HRS_EXT_TRN>
<A.POI_TYPE><![CDATA[00000]]></A.POI_TYPE>
<A.COUNTRY_NM_FORMAT><![CDATA[001]]></A.COUNTRY_NM_FORMAT>
<A.NAME><![CDATA[Sara Simplehead]]></A.NAME>
<A.NAME_INITIALS><![CDATA[]]></A.NAME_INITIALS>
<A.NAME_PREFIX><![CDATA[]]></A.NAME_PREFIX>
<A.NAME_SUFFIX><![CDATA[]]></A.NAME_SUFFIX>
<A.NAME_ROYAL_PREFIX><![CDATA[]]></A.NAME_ROYAL_PREFIX>
<A.NAME_TITLE><![CDATA[]]></A.NAME_TITLE>
<A.LAST_NAME><![CDATA[Simplehead]]></A.LAST_NAME>
<A.FIRST_NAME><![CDATA[Sara]]></A.FIRST_NAME>
<A.MIDDLE_NAME><![CDATA[A.]]></A.MIDDLE_NAME>
<A.NAME_DISPLAY><![CDATA[Sara Simplehead]]></A.NAME_DISPLAY>
<A.NAME_FORMAL><![CDATA[Sara Simplehead]]></A.NAME_FORMAL>
<A.ADDRESS_TYPE><![CDATA[HOME]]></A.ADDRESS_TYPE>
<A.COUNTRY><![CDATA[USA]]></A.COUNTRY>
<A.ADDRESS1><![CDATA[140 test drive]]></A.ADDRESS1>
<A.ADDRESS2><![CDATA[]]></A.ADDRESS2>
<A.ADDRESS3><![CDATA[]]></A.ADDRESS3>
<A.ADDRESS4><![CDATA[]]></A.ADDRESS4>
<A.CITY><![CDATA[anytown]]></A.CITY>
<A.NUM1><![CDATA[]]></A.NUM1>
<A.NUM2><![CDATA[]]></A.NUM2>
<A.HOUSE_TYPE><![CDATA[]]></A.HOUSE_TYPE>
<A.COUNTY><![CDATA[chemung]]></A.COUNTY>
<A.STATE><![CDATA[NY]]></A.STATE>
<A.POSTAL><![CDATA[14845]]></A.POSTAL>
<A.DESCR><![CDATA[New York]]></A.DESCR>
<A.SEQUENCE_NUMBER>3</A.SEQUENCE_NUMBER>
<A.HRS_DESCR><![CDATA[Cash]]></A.HRS_DESCR>
<A.HRS_JOB_OPENING_ID>115298</A.HRS_JOB_OPENING_ID>
<A.DESCR1><![CDATA[Anytown Hospital]]></A.DESCR1>
<A.ADDRESS1_2><![CDATA[One Test Drive]]></A.ADDRESS1_2>
<A.CITY2><![CDATA[Anytown]]></A.CITY2>
<A.STATE2><![CDATA[NY]]></A.STATE2>
<A.POSTAL2><![CDATA[14830]]></A.POSTAL2>
<A.DESCR2><![CDATA[Registered Nurse]]></A.DESCR2>
<A.DESCR3><![CDATA[Nursing Unit - LDRP]]></A.DESCR3>
<A.CASE_IDENTIFIER><![CDATA[Full Time]]></A.CASE_IDENTIFIER>
<A.STATUS_DT>2017-02-07</A.STATUS_DT>
<A.NAME52><![CDATA[Susie Smith]]></A.NAME52>
<A.JOBTITLE><![CDATA[Sr. Recruiter]]></A.JOBTITLE>
<A.CASE_NUMBER_AUS><![CDATA[Non-exempt]]></A.CASE_NUMBER_AUS>
<A.CASE_NUMBER_NZL><![CDATA[ ###-###-###]]></A.CASE_NUMBER_NZL>
<A.STD_HOURS>36</A.STD_HOURS>
<A.GRADE><![CDATA[99]]></A.GRADE>
<A.STD_HOURS_NEW>.9</A.STD_HOURS_NEW>
<A.GH_SESSION_DATE></A.GH_SESSION_DATE>
<A.SAL_ADMIN_PLAN><![CDATA[UNH]]></A.SAL_ADMIN_PLAN>
<A.STEP_FROM>0</A.STEP_FROM>
<A.DATE>2019-11-22</A.DATE>
</row>
</query>

enter image description here

UPDATE:

I have got this pulling in multiple rows now, however on the output on the below text block for Overtime, I noticed it is pushing the A.OFFER_AMT field below onto it's own line, and breaking up the paragraph. How can I fix this? Thanks!

Template: enter image description here Actual Output: enter image description here

2
xmlxsltxpathxquerybi-publisher
Please fix the XML data. It's not valid. Some rows start with -. - Based
Sorry about that, I've fixed it. - Nick

2 Answers

1
votes

You are checking <?if:A.DESCRSHORT='PROB'?> and <?if:A.DESCRSHORT='BASAL'?>, but in the XML sample provided A.DESCRSHORT is BaseSalary, Probationa or SignOn.

A.OFFER_COMPONENT and OFFER_TYPE are BASSAL, PROB or SIGNON.

If you want to check all rows, you will need to add a repeating group:

<?for-each:row?>
    <?if:A.OFFER_COMPONENT='PROB'?>
        Probationary Rate of Pay: <?A.OFFER_AMT?>
    <?end if?>
    <?if:A.OFFER_COMPONENT='BASSAL'?>
        Base Rate of Pay: <?A.OFFER_AMT?>
    <?end if?>
    <?if:A.OFFER_COMPONENT='SIGNON'?>
        Sign-on Bonus: <?A.OFFER_AMT?>
    <?end if?>
<?end for-each?>

Additional advice: You have a lot of duplicate data between rows. Out of 77 fields, only 6 have unique data related to the offer. Consider using connected queries for parent-child relations in data.

0
votes

For the problem of the newline getting added within the paragraph, this occurs due to the if condition. The if condition always adds a new line. If you want to prevent this , use if@inlines

Some other examples of if, then, else