X

An Oracle blog about BI Publisher

  • April 13, 2007

My duplicates made duplicates!

A title that will only mean anything to the most ardent Calvin & Hobbes fan ... thats my youngest son in our household. He has taken Calvin as his role model ... for those of you that know him, Calvin that is, its made for some interesting but fun times.



We had a question this week about removing duplicates, assume I have the following tabulated data:





































































Quarter



Software



Hardware



Services



Misc



Q1



200



100



400



500



Q2



200



200



300



400



Q3



200



200



300



600



Q4



200



300



500



600



Straightforward stuff for a BIP template eh? Now how about if my users want this layout:





































































Quarter



Software



Hardware



Services



Misc



Q1



200



100



400



500



Q2



 



200



300



400



Q3



 



 



 



600



Q4



 



300



500



 




Namely, remove the duplicate values in the table so the user can infer that the Software sales in Q1-> 4 were 200. Not so straightforward eh?
We could do some horribley complicated grouping but being the dynamic (read lazeee) person I am I want a short cut thats simple and yet very cunning ... XPATH to the rescue. I've mentioned XPATH in previous posts and its well worth learning a little, theres a primer in the user guide but theres so much more. XPATH lets us do an inline 'if' statement on our XML elements; it also allows us to walk up and down our XML tree and compare values.
Our requirement in psudo might be:
                                                         if current value = previous value| hide current value | end if
In XPATH this becomes:
                                                         <?COLUMNx[not(.=preceding::COLUMNx)]?>

where COLUMNx is the element you want to test.
To break this down:
1. We use '[ ]' to surround the xpath expression
2. 'not()' - we're looking for a false boolean value to be returned from our expression
3.  '.=preceding::COLUMNx'
      - the '.' is a reference to the current element i.e. COLUMNx in the current record of the tree
      - preceding::COLUMNx refers to the value of COLUMNx in the previous row of data.
There are a bunch of other 'Axis Specifiers' allowing you to very efficiently navigate and test siblings and granparent values - its nicely summarized here. If you're looking for examples just google it. Template for the example above available here.



Row Exclusion

Of course we dont just have to test single elements we can do whole records if we wish, we can look for a certain value and exclude whole rows based on the result. We can again use XPATH to do it rather than a clumsy if statement. We used this row exclusion in the Anatomy of a Template series last week. Remember we were working with the AR invoice report. This generated XML for both types of invoice line, TAX and ITEM. In our output we only wanted to see the ITEM lines.
We used something along the lines (excuse the pun):

                                                                    <?for-each:G_LINES[LINE_TYPE='LINE']?>

This time the xpath expression is applied at the group level, only show members of G_LINES where the LINE_TYPE is equal to 'LINE' i.e. exclude the TAX lines ... we all want to do that of course.
You could move up further and exclude whole tables/sections of content just by using XPATH. Check it out, its simple, powerful, performant and best of all, if you use it, you'll look like a BIP Rockstar to your colleagues!

Join the discussion

Comments ( 1 )
  • guest Monday, November 21, 2011

    Thank you the post. It was very helpful to solve part of my problem. But I am trying to remove the duplicates in a column like below,

    The original data from the sql is

    Pallet# PO# Part# Cust.Part# Box GrossWeight

    ----------------------------------------------------------------

    1 PO1 B1234 1234 5 146.67

    1 PO2 B1235 1235 6 200.00

    1 PO3 B1236 1236 7 200.00

    2 PO1 B1234 1234 5 300.86

    2 PO2 B1235 1235 6 300.00

    Output should be:

    Pallet# PO# Part# Cust.Part# Box GrossWeight

    -----------------------------------------------------------------------

    1 PO1,PO2,PO3 B1234 1234 5 546.67

    B1235 1235 6

    B1236 1236 7

    2 PO1,PO2 B1234 1234 5 600.86

    B1235 1235 6

    Is it possbile with an XPATH expression in XML publisher/ BI publisher ?


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.