Search code examples
oraclesortingobiee

BI publisher sorting ASCII characters (lower case and ignore uppercase in sort)

I am trying to sort descr fields in BIP rtf template, and this is how I was able to sort it using the command <?sort:DESCR;'ascending';data-type='text'?>:

DESCR:

  • AVPFinance
  • Academic Advising
  • Accounts Payable
  • Alumni Services
  • Anthropology
  • Architectural

I want my sorting to push AVP finance to the end in the following order:

  • Academic Advising
  • Accounts Payable
  • Alumni Services
  • Anthropology
  • Architectural
  • AVPFinance

Is there a way to do it BI Publisher? as I can't edit the query or the xml to do that.

Thanks

Solution

  • BI Publisher sorts uppercase prior to lowercase. So "Z" would before "a".

    You can convert it to uppercase first, that will remove the problem.

    This will sort by DESCR after converting everything to uppercase:

    <?sort:(xdoxslt:convert_case(DESCR,'UPPER'));'ascending';data-type='text'?>

    You may have to go into the Advanced properties to do something more advanced like this.