1 Reply Latest reply: Apr 9, 2014 8:57 AM by Manikandan-S-Oracle RSS

    Conditional count not working

    JeroenS

      I'm working on a BI Publisher report based on a query in PS Financials. It's a list of proposals by emplid, and on the top I want a summary. It should the number of proposals where the emplid was PI, CPI, and the number of proposals that were funded, like this: If I do

       

      <?count(PROPOSAL_ID[])?> then I get the total number of proposals. However, I need to count using conditions, so I tried:

       

      <?count(PROPOSAL_ID[CU_PROP_STATUS='Y'])?> to get the funded ones and <?count(PROPOSAL_ID[CU_P_STATUS='PI'])?> to get the ones where the submitter was PI. These result in 0. What am I doing wrong?

       

      Sample data:

      <query xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" numrows="10" queryname="CU_GM_ALL_PROPS_XML" xsi:noNamespaceSchemaLocation="">

      <row rownumber="1">

      <PROPOSAL_ID>PROPOSAL_ID sample data</PROPOSAL_ID>

      <EMPLID>EMPLID samp</EMPLID>

      <NAME>NAME sample data</NAME>

      <CU_PCT>5517378.176</CU_PCT>

      <CU_P_STATUS>CU_P_</CU_P_STATUS>

      <CU_PRO_BGN_DT>2014-04-09</CU_PRO_BGN_DT>

      <CU_PRO_END_DT>2014-04-09</CU_PRO_END_DT>

      <DESCR254>DESCR254 sample data</DESCR254>

      <CU_PROP_STATUS>C</CU_PROP_STATUS>

      <TOTAL_COST>-8358848713.3668</TOTAL_COST>

      <ALLOCATED_AMT>-965278423405758.783</ALLOCATED_AMT>

      </row>

      <row rownumber="2">

      <PROPOSAL_ID>PROPOSAL_ID sample data</PROPOSAL_ID>

      <EMPLID>EMPLID samp</EMPLID>

      <NAME>NAME sample data</NAME>

      <CU_PCT>5082881.370</CU_PCT>

      <CU_P_STATUS>CU_P_</CU_P_STATUS>

      <CU_PRO_BGN_DT>2014-04-09</CU_PRO_BGN_DT>

      <CU_PRO_END_DT>2014-04-09</CU_PRO_END_DT>

      <DESCR254>DESCR254 sample data</DESCR254>

      <CU_PROP_STATUS>C</CU_PROP_STATUS>

      <TOTAL_COST>-6011393680.7097</TOTAL_COST>

      <ALLOCATED_AMT>-649996722908031.194</ALLOCATED_AMT>

      </row>

       

      CU_P_STATUS can be either PI or CPI.

      CU_PROP_STATUS can be Y or N.

        • 1. Re: Conditional count not working
          Manikandan-S-Oracle

          Need to understand your correct xml data.

           

          But try any one of the below code and see if that works

           

           

          <?count(PROPOSAL_ID[../CU_PROP_STATUS='Y'])?>

           

          OR

           

          <?count(PROPOSAL_ID[./CU_PROP_STATUS='Y'])?>

           

           

           

           

          If you code is inside loop then try this:

           

           

          <?count(current-group()/PROPOSAL_ID[../CU_PROP_STATUS='Y'])?>