Number-Upper-Lower Sorting

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
.

Comments:

Hi Tim, I have tried this with my report. I am working on a invoice print report in EBS where i have to print invoices sorted based on the bill to country. But the thing is the domestic invoices should come first (domestic is for Denmark). So i tried the above logic. can you please let me know whats wrong about this? Sharmila

Posted by Sharmila on May 05, 2010 at 04:32 PM MDT #

I'm getting lots of interesting info from this website.

Posted by Quentin Arntt on September 12, 2010 at 01:26 AM MDT #

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