Mikhail from the PeopleSoft reporting team came up with a good question today. A customer that converted one of their Crystal Reports to BIP was having some issues. The report was not sorting the same way the Crystal report used to. They wanted the data sorted by Numbers , then uppercase followed by lower case strings.
It reminded me of the Cool Sorting post from 2 years ago. Dang, we have written a lot! With a little change I came up with a similar solution.
You need to use a trick in the sort
<?for-each:row?><?sort:string-length(substring-before ('01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
,substring(PRODUCT_ID,1,1))) ;'ascending';data-type='number'?>
I have a the string '01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' to provide the required sort order ie numbers , then upper case then lower case values.
The substring-before finds the sub string in the big string based on the first character of the PRODUCT_ID e.g. value starting with '3' will return '012'
The string-length then calculates the length of the string e.g. '012' returns 3.
We then sort by that string length value.
To simplfy the code a little I would put the string into a variable
<?variable: srtStr; "'01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'"?>
thats a " surrounding the single quotes. Then reference it
<?sort:string-length(substring-before ($srtStr,substring(PRODUCT_ID,1,1))) ;'ascending';data-type='number'?>
Bit neater and more portable ie you can use the same variable for multiple sorts. RTF available here.