X

An Oracle blog about BI Publisher

  • RTF
    September 4, 2008

Cool String Sorting

Got a good internal question from Nikos one of our Product managers this week, it was a sorting question but a tough one. He had the following data:

<ROWSET>

<ROW>

<YEAR>Total Year</YEAR>

<MARKET>East</MARKET>

<PRODUCT>Audio</PRODUCT>

<SALES>75241.0</SALES>

</ROW>

<ROW>

<YEAR>Total Year</YEAR>

<MARKET>East</MARKET>

<PRODUCT>Visual</PRODUCT>

<SALES>143054.0</SALES>

</ROW>

<ROW>

<YEAR>Total Year</YEAR>

<MARKET>West</MARKET>

<PRODUCT>Audio</PRODUCT>

<SALES>108053.0</SALES>

</ROW>

<ROW>

<YEAR>Total Year</YEAR>

<MARKET>West</MARKET>

<PRODUCT>Visual</PRODUCT>

<SALES>171242.0</SALES>

</ROW>

<ROW>

<YEAR>Total Year</YEAR>

<MARKET>South</MARKET>

<PRODUCT>Visual</PRODUCT>

<SALES>110312.0</SALES>

</ROW>

<ROW>

<YEAR>Qtr1</YEAR>

<MARKET>East</MARKET>

<PRODUCT>Audio</PRODUCT>

<SALES>18132.0</SALES>

</ROW>

<ROW>

<YEAR>Qtr1</YEAR>

<MARKET>East</MARKET>

<PRODUCT>Visual</PRODUCT>

<SALES>32847.0</SALES>

</ROW>

...

</ROWSET>

You get the picture, unsorted sales data with funky values for the YEAR. What he wanted to do was get an output that sorted by Market then Product then by the YEAR value ie Jan, Feb,Mar, Qtr1 ... Total Year.

He then wanted to highlight the Qtr and Year rows with other background colors.

Something like this:

Stringsort1.jpg

The first two criteria are easy, just use the for-each-group functionality. The last is a bit tougher, you could hard code the YEAR values into a table, that would get what he wanted but not very satisfying from a 'Templaters' point of view.

A quick Google and I had a plan - hat tips to XSL miesters, Michael Kay and Jeni Tennison.Still a little hard coding but nothing like the other hard coding option and a bit more satisfying. It relies some string calculations to come up with the sort order. Here's the sort command:

 <?sort: string-length(

substring-before ('JanFebMarQtr1AprMayJunQtr2JulAugSepQtr3OctNovDecQtr4Total Year'

,YEAR));

'ascending';

data-type='number'?>



You need to list out all the possible YEAR values in the order in which you want them sorted ie JanFebMarQtr1...DecQtr4Total Year.Then you find current YEAR value that you are currently looping over, in that string and then find the length of the string before that position. So you get :









































Length

Period

0 Jan
3 Feb
6 Mar
9 Qtr1
13 Apr
16 May
19 Jun
22 Qtr2




then you sort by the string length ... coool! Now you just plug that into the template.

<?for-each:current-group()?>

<?sort: string-length(

substring-before ('JanFebMarQtr1AprMayJunQtr2JulAugSepQtr3OctNovDecQtr4Total Year'

,YEAR))

;'ascending';data-type='number'?>

Hok Min from the dev team made a good point here. To avoid that ugly and potentially unmanageable (if you wanted to use it again in the template) sort string. Just create a variable to hold the string and then reference it in the sort command:

<xsl:variable name="mnthtbl">JanFebMarQtr1AprMayJunQtr2JulAugSepQtr3OctNovDecQtr4Total Year</xsl:variable>

Add to the sort by:

<?sort:string-length(substring-before($mnthtbl, ./YEAR));'ascending';data-type='number'?>

For the conditional highlighting of the 'Qtr' and 'Total Year' rows we can use the string manipulation again.

For the QtrX

<?if@row:substring(YEAR,1,1)='Q'?><?attribute@incontext:background-color;'#80FFFF'?><?end if?>

For the Total Year

<?if@row:substring(YEAR,1,1)='T'?><?attribute@incontext:background-color;'#00CACA'?><?end if?>

All Im doing is testing the first character of the YEAR value and looking for a 'Q' or a 'T'. Using the @row gets the formatting applied to the whole row. If you want the source files you can get them here.


You can of course apply this sort to any set of strings, its going to come at a small price thou. Its far far better to sort in the XML extract if at all possible.

So what have we learned? this XSL stuff is cool! Google is a 'Templaters' friend and there is always an easier way!

Join the discussion

Comments ( 4 )
  • Donal Slemon Friday, November 6, 2009
    I really appreciate finding this post. Why, oh why does the BIP documentation or XDO user guide not have info on this? It's inexcusable. This 'substring-before' post was my last-ditch try and finally helped me overcome date sorting where my date format is '25-Jan-2009'. Up to this point, the default sorting BIP would apply when I used 'data-type='date' ' would sort the days correctly, but put 'Feb' before 'Jan' - thus proving it was sorting this part of the date alphabetically!! What a mess. And it should be so simple to acheive.
  • Tim Dexter Friday, November 6, 2009
    Hi Donal
    Sorry about that, BIP still does not handle date sorting very well at all. Alternatives ought to be documented. Its a case of writing up all the new features and then trying to squeeze in updates. I guess thats how the blog came about, but if you dont know about it ...
    regards
    tim
  • kalyan Thursday, March 4, 2010
    Would this conditional formatiing work for Excel? PDF works great but not excel.
  • Tim Dexter Thursday, March 4, 2010
    Kalyan
    this is not conditional formatting, its sorting. What do you mean?
    Tim
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.