5 Replies Latest reply: Nov 2, 2010 4:45 PM by 807528 RSS

    Filtering BI Publisher Reports

    807528
      Hi Gurus,
      I have a requirement in which I need to filter the data obtained from a Intergration Object on the basis of a date in the child Integration component. Is there any way to do that. This is a critical issue andI've already spent a couple of hours figuring this out. So any immediate help would be greatly appreciated.

      Thanks,
      Vasanth
        • 1. Re: Filtering BI Publisher Reports
          Kavipriya-Oracle
          Can you paste your xml structure here?
          • 2. Re: Filtering BI Publisher Reports
            807528
            Sure. Here it is. The last field ssAItemEndDate is the one that needs to be compared with the current date.

            <?xml version="1.0" encoding="UTF-8"?><ListOf_ssServiceA
            <ssServiceA
            <ssAccount
            XYZ</ssAccount
            <ssAEndDate
            12/31/2006</ssAEndDate
            <ssANumber
            S4035711</ssANumber
            <ssAStartDate
            11/15/2005</ssAStartDate
            <ssAStatus
            Amendment</ssAStatus
            <ssAType
            ABC</ssAType
            <ssContact
            </ssContact
            <ssContactFirstName
            </ssContactFirstName
            <ssAbcASubType
            Sales Promo</ssAbcASubType
            <ssAbcAttention
            </ssAbcAttention
            <ssAbcBillToNum
            111</ssAbcBillToNum
            <ssAbcContactCity
            </ssAbcContactCity
            <ssAbcContactCountry
            </ssAbcContactCountry
            <ssAbcContactPhone
            </ssAbcContactPhone
            <ssAbcContactState
            </ssAbcContactState
            <ssAbcContactStreetAddress
            </ssAbcContactStreetAddress
            <ssAbcContactZipcode
            </ssAbcContactZipcode
            <ssAbcFor
            ABC</ssAbcFor
            <ssAbcJdeQuote
            A12345</ssAbcJdeQuote
            <ssAbcShipTo
            11111</ssAbcShipTo
            <ssAbcShipToTerritory
            654</ssAbcShipToTerritory
            <ssId
            2-12O3-1169</ssId
            <ssCreated
            03/08/2006 04:29:58</ssCreated
            <ssUpdated
            05/13/2010 09:55:05</ssUpdated
            <ssCreatedBy
            2-BY7TU</ssCreatedBy
            <ssUpdatedBy
            2-1SUD70</ssUpdatedBy
            <ListOf_ssAbcCutAddress
            </ListOf_ssAbcCutAddress
            <ListOf_ssAbcTerms
            </ListOf_ssAbcTerms
            <ListOf_ssFsAItemSimple
            </ListOf_ssFsAItemSimple
            </ssServiceA
            <ssServiceA
            <ssAccount
            ABC</ssAccount
            <ssAEndDate
            06/30/2006</ssAEndDate
            <ssANumber
            1234</ssANumber
            <ssAStartDate
            01/02/2006</ssAStartDate
            <ssAStatus
            Active</ssAStatus
            <ssAType
            ABC</ssAType
            <ssContact
            </ssContact
            <ssContactFirstName
            </ssContactFirstName
            <ssAbcASubType
            ABC</ssAbcASubType
            <ssAbcAttention
            </ssAbcAttention
            <ssAbcBillToNum
            11111</ssAbcBillToNum
            <ssAbcContactCity
            </ssAbcContactCity
            <ssAbcContactCountry
            </ssAbcContactCountry
            <ssAbcContactPhone
            </ssAbcContactPhone
            <ssAbcContactState
            </ssAbcContactState
            <ssAbcContactStreetAddress
            </ssAbcContactStreetAddress
            <ssAbcContactZipcode
            </ssAbcContactZipcode
            <ssAbcFor
            ABC</ssAbcFor
            <ssAbcJdeQuote
            12345</ssAbcJdeQuote
            <ssAbcShipTo
            22222</ssAbcShipTo
            <ssAbcShipToTerritory
            356</ssAbcShipToTerritory
            <ssId
            2-12O3-117</ssId
            <ssCreated
            03/08/2006 04:29:58</ssCreated
            <ssUpdated
            01/21/2009 05:55:33</ssUpdated
            <ssCreatedBy
            2-BY7TU</ssCreatedBy
            <ssUpdatedBy
            2-G2DMN</ssUpdatedBy
            <ListOf_ssAbcCutAddress
            </ListOf_ssAbcCutAddress
            <ListOf_ssAbcTerms
            </ListOf_ssAbcTerms
            <ListOf_ssFsAItemSimple
            <ssFsAItemSimple
            <ssAItemEndDate
            06/30/2006</ssAItemEndDate
            • 3. Re: Filtering BI Publisher Reports
              Kavipriya-Oracle
              Use date_diff function.
              You can find the example in blog http://blogs.oracle.com/xmlpublisher/2008/09/date_functions.html

              Use
              <?if:xdoxslt:date_diff(‘d’, xdoxslt:current_date($_XDOLOCALE, $_XDOTIMEZONE), ssAItemEndDate,$_XDOLOCALE, $_XDOTIMEZONE)>0?>
              your logic
              <?end if?>

              But for this you need to have ssAItemEndDate in the format ‘YYYY-MM-DD’

              You can use seibel extended functions to get date in that format. Check this out http://bipconsulting.blogspot.com/2010/01/my-date-doesnt-like-me-with-siebel.html
              • 4. Re: Filtering BI Publisher Reports
                807528
                KaviPriya,
                Thanks a lot.

                I need to divide the Total in a report with 100 for particular Items. Is that possible?

                Thanks,
                Vasanth
                • 5. Re: Filtering BI Publisher Reports
                  807528
                  Hi KaviPriya,
                  I was able to figure this out myself. Thanks a lot. Could I have your email address.I wanted to send my template file for a quick review.

                  Vasanth.