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!

Comments:

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.

Posted by Donal Slemon on November 05, 2009 at 06:48 PM MST #

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

Posted by Tim Dexter on November 06, 2009 at 02:42 AM MST #

Would this conditional formatiing work for Excel? PDF works great but not excel.

Posted by kalyan on March 04, 2010 at 02:07 AM MST #

Kalyan this is not conditional formatting, its sorting. What do you mean? Tim

Posted by Tim Dexter on March 04, 2010 at 02:40 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today