Sorting (database results differs from BO results)

building reports desktopintelligence
1

There is a sorting problem with Business Objects such that the sorted results from a database is different from the sorted results within Business Objects (Desktop Intelligence or Web Intelligence).

For example, consider this sample list of last names: Dennis, de Boer, Obbanty, Danson, O Brien, Davis
(NOTE: the space in O Brien)

database results sorted: Danson, Davis, Dennis, O Brien, Obbanty, de Boer
BO results sorted: Danson, Davis, de Boer, Dennis, Obbanty, O Brien

There is no way to change this sort order in BO, even if a new object is introduced solely for the purpose of sorting.

You can get close by sorting with the ascii value of the object using the Asc function in BO, but that function only returns the ascii value of the first character in the string. Moreover, a custom function could be written to sum up the ascii values of each character in a string but eventually you run into a problem if two strings have the same sum.

There are no parameters within a universe that turns off/disables this very strange sort mechanism within Business Objects.

So please, BO community, how does this problem get solved? This is a very, very bad bug. BAD bug. And its the source of many troubles with our user community. This is extremely frustrating within my development community.

PLEASE HELP!

Immanuel Gooding (BOB member since 2010-02-22)

2

Why don’t you do a custom sort?
custom sort.doc (27.0 KB)

nithya_raj (BOB member since 2007-02-03)

3

custom sort are only helpful when your result set is discreet - is that a true statement?

In case of last names column, the result set is not discreet.

umpa (BOB member since 2005-08-15)

4

Immanuel,

Firstly, Welcome to B :mrgreen: B!
Its an interesting one and I will give it some more thought.

Maybe there is a language pack, regional setting, you can apply to change this, or the translation manager in the universe itself, version 3.0 and above.

Just a couple of - initial - avenues for ideas, that come to mind.

Mak 1 :uk: (BOB member since 2005-01-06)

5

Hi Immanuel,

I think that the question is why do you think that the sort in BO is wrong. People may prefer to have all names that start on ‘D’ (regardless of whether it’s ‘D’ or ‘d’) sorted before all names that start on ‘O’ (either ‘O’ or ‘o’).

More about different ways of string/number ordering:

Marek Chladny :slovakia: (BOB member since 2003-11-27)

6

I was going to get to, just that, next, Marek :).
I suspect that this is a particular nationalities preference. I personally, prefer the data to be consistant, as it makes things easier!

Mak 1 :uk: (BOB member since 2005-01-06)

7

Extra Extra :: PROBLEM SOLVED

Mak, Thanks for the tips. I’ll look into those.

Marek, you have a point, yes. After all we are talking about a reporting tool. However, this a reporting tool that is coupled to/with a DBMS of choice. In that case, when comparing result sets from the database and the reporting tool for sorted order, it would be nice to see some consistency. Thereafter, the reporting tool user is free to produce a sort order of choice based on options available. The issue here is that BO implements a sorting algorithm (by default) that breaks the consistency of results produced by a very basic sql script output WITHOUT providing a way to “turn off” said algorithm. If the basic sorted data in a report table is based on some SQL script, it is not far-fetched to expect the same data (in same order) when the SQL script is executed in/on the database itself.

(Maybe one of Mak’s recommendations is the solution to disabling the fancy sorting algorithm employed by BO… I haven’t researched those yet)

But for those who are experiencing a similar problem, I have a workaround for you. Its not painful at all. Perform a dense rank on your problem data (of course, remember to convert that to an object :slight_smile: ). You can sort your report by the dense rank object, and the problem will be solved.

For example (this is what becomes the script for your object):
SELECT DENSE_RANK() OVER (ORDER BY (LASTNAME))
FROM MYNAMESTABLE

Note: I omitted sort order (ASC/DESC) from order by clause deliberately. It can be done in DeskI or WebI.

Hope this helps.

Immanuel Gooding (BOB member since 2010-02-22)

8

Hi,

Nice workaround. Thanks for sharing.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

9

Immanuel,

Well, you have found one answer…:).

Further to this, I would look at the regional settings you have on Oracle. When you run the SQL there are you using the BO user account and middleware?
Also, you could try looking at either the universe parameters, in the universe, not very well documented, I’m afraid.
There is also extra configuration you can do with an Oracle connection, itself, in the universe.
In addition you could look at the prm file for Oracle under the data access folder, where, again you can set paremeters for BO to interact with your middleware.

As stated, before, I don’t know the answer, however, this is where I would be looking.

Cheers,

Mark.

Mak 1 :uk: (BOB member since 2005-01-06)

10

Hy Imamanuel,

I had the same problem.

If i had sorted from the sql, in report the sorts were wierd .

Sort from Bo, is the exact sort like “order by” in sql and it’s less painfull when you have those complex queries.

masterg (BOB member since 2010-02-19)

11

Did not run query in Oracle as BO user. In this case, it really didn’t matter.
Also, did go through all the universe parameters. That was first step I took. No luck there. More importantly, did not need to tweak the shared db just for the BO issue. But the “extra configuration you can do with an Oracle connection, itself, in the universe” has me very curious, so I’ll start on that. Thanks.

Masterg, please share the details of how you solved your problem.

Immanuel Gooding (BOB member since 2010-02-22)

12

I was asking this to rule the actual BO connection, itself, out of the picture…;).
The prm file used to have more parameters than the ones, accessable, under the universe. I would have a look at these too.
This post will help with the location, if you do not already know it, of course.
https://bobj-board.org/t/88836

Again, not very documented, I’m afraid :nonod: .
I’m sure the answer lies somewhere, I have suggested, out of ideas now :P.

Mak 1 :uk: (BOB member since 2005-01-06)