cancel
Showing results for 
Search instead for 
Did you mean: 

How to sort data in a report

Former Member
0 Kudos

Hi,

I am using Business Objects XI 3.1 and I've searched for a way to define a sort on a given query. The data I want to sort is alphanumeric and, if I try to sort it in Oracle I get the order I want. However, in Web Intelligence I have no choice on how to do this. All I could find was to define a custom sort that is not persistent i.e., when I purge my results my defined custom sort gets flushed. How can I achive this?

Also, is it possible to sort the data in a chart? I mean, is it possible to define an order by which data should be displayed along the X axis?

Thanks in advance to any contributions you might give.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

First of all thank you for your contributions.

Dineshkumar89, I've read that it is possible to sort objects at the universe level but I have yet to see how it's done. Nevertheless, if you think of it, it only makes sense to sort objects at the query level seeing as only then are you able to tell the sorting your results should come. At least, that's what I think but I might be wrong and I might be missing some other cases.

Marianne Wagt, I did not know that option. Thank you. I made a mistake previously and I'm sorry. The data I'm trying to sort is not alphanumeric, it is always a character string. I have a dimension that represents cost intervals (such as '0 - 5K' or '10K - 20K' and others) and it is something like this that I would like to sort in the X axis of a chart. Presently I am only able to sort it if I create a temporary table with the same data where I define the custom sort. Are there any other ways to accomplish this?

Thanks again.

MariannevL
Advisor
Advisor
0 Kudos

That clarifies a bit...

To custom sort directly on the x-axis object of a chart, switch to structure view and click the object on the x-axis.

If it is an object, custom sorting (and any other sorting) is now possible. Switch back to results and the sort is reflected.

Another option is to make sure it sorts right by adding a derived object or adjusting the syntax.

So if you have '0K - 5K' or '10K - 20K' you could reformat as '00K - 05K' and it will sort right (but not look as nice).

You can also create a detail variable on this dimension object contents containing only the numeric part

(and make it a number by using ToNumber()). If this is first in the list of objects on the x-axis, it will first sort on the numeric.

Hope this helps,

Marianne

Former Member
0 Kudos

Thanks again for your help. I had never thought of trying to sort the data in the structure view.

I would like to take this to ask one final question on this topic:

- when I define a custom sort if I type in some temporary values so that all my possible values get sorted will they get picked up by the custom sort? And also, a custom sort will be kept even after a purge of the associated query or will I have to define it all over again?

Best regards

MariannevL
Advisor
Advisor
0 Kudos

Hi,

If Oracle can give you back the right sort order without any custom sorting,

that means you should be able to get the same results in WebI.

If the object that you pull from the universe is the wrong type (like numeric but you want it alphanumeric),

then you can either request you designer to give you an object with the right data type,

or you can change it by creating a report variable using

FormatNumber([original object],"0")

this will format the number to a number without leading zeroes, thousand separators or decimal point.

Now you can use the object as if it came from the universe.

So you can also use it on the x-axis of a chart.

Btw. you can also do a custom sort on the x-axis(as long as it is an object or variable, not a formula)

alternative, if you define a custom sort in a table block, you can state it should be used througout the document.

(so also in the charts.

Hope this helps,

Marianne

Former Member
0 Kudos

you can sort the data in the objects using in universe itself

sorting can be done in universe level

when u create that object in universe , there is edit option, there u can sort it.