Sorting Objects in Designer - NOT in Alphabetic Order

building semantic layer
1

Listers,

I have to create an object in designer that will sort by a particular order, not alphabetic order. Here’s a brief ex: I have an “Order Status Description” object that when the users put on a query gets sorted by alpha order. They want it sorted in “production line” order (the same as the order status code). How do I accomplish this? I have searched the knowledge base but haven’t found it yet.

I don’t want to have to put a custom sort on every query that is created, I want to force the sort through the designer.

Does anyone have an example of how to do this? It would be deeply appreciated by my users!

Nancy

Listserv Archives (BOB member since 2002-06-25)

2

I have done things like this before… set up a string that contains your possible values in the desired order -

Desired order = ‘var1_var2_var3_varN’

You could then sort on the POSITION of your field in that item, using string functions… In the ‘old days’ I used this to sort on character months, works pretty well

Good luck,
Brent

Listserv Archives (BOB member since 2002-06-25)

3

At 03:24 PM 4/18/2000 -0500, Nancy Bloome wrote:

I have to create an object in designer that will sort by a particular order, not alphabetic order. Here’s a brief ex: I have an “Order Status Description” object that when the users put on a query gets sorted by alpha order. They want it sorted in “production line” order (the same as the order status code). How do I accomplish this? I have searched the knowledge base but haven’t found it yet.

I don’t want to have to put a custom sort on every query that is created, I want to force the sort through the designer.

Nancy, it sounds like “order status code” is an object you have (or could have) defined in the universe. Make “Order Status Description” a Detail object under order status code.

You didn’t say whether your users are using full-client BusinessObjects or WebIntelligence.

When a WebI user double-clicks (or drags) “Order Status Description” to the query panel, by default WebI will put its dimension object “order status code” onto the query panel first, and default sorting will be on order status code first. So you’d just have to train WebI users to click the plus-sign on the dimension object, and to only drag/click the detail object to get both, with the object to be sorted on (luckily) taking precedence. Unfortunately, however, the WebI user cannot “hide” the column on the report.

The full-client product works a little differently. If you double-click (or drag) the detail object, BusObj also drags over the dimension object, but it makes the dimension object the second of the two.

So for full-client BusinessObjects users, you’d have to train them to first drag in the “order status code”, and then the “Order Status Description” in order to get default sorting on order status code first.

The “plus” to the full-client tool is that you can teach your users to put an explicit sort on this column, and then “hide” it on the report. First apply the sort to that column. Then, for that table – from the menu, pick Tools - Table (or Crosstab if you’ve pivoted the data), and on the Pivot tab, in the “Used Variables” select the “code” object, and click the “Hide” button, and OK.

I hope this helps,

Anita Craig
Stanford University

Listserv Archives (BOB member since 2002-06-25)