Oracle Analytics and AI

How to create a custom sorting in OBIEE 11G

Sandesh AG

Hi Guys

I have a requirement to do custom sorting on one of the column in the report.

Under the product column it has to display based on the custom sorting.

For Ex.

Products

Mobile

Webcams

But as per the requirement it has to display in custom order.

Might be like

Products

Webcams

Mobile

I was checking all possible solutions in google.

Please help me out in fixing the requirement.

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Hi,

OBIEE can sort ascending or descending, as you said (and saw).

If you want a different sorting you must provide OBIEE with something it can use to sort on (ascending or descending again).

So you need a sort column.

If that custom sort order is the one you want all the time you can set that column in the RPD:

If you want to be free to sort ascending or descending or based on your custom order you can keep it as a separate column and add it to your analysis as hidden but sort on it.

If your sorting is just a one-shot you can also just make a fake column in the analysis using CASE WHEN and returning numbers for each product you want to sort in a given order, make that column hidden and sort on it.

Of course that kind of logic works as long as you have a fixed and known list of products while generating that sorting column in your DB with your ETL load process would be safer as it's always there and will always have values based on the same business rules defined in the ETL.

To make it short: it's done by hand at some point (ETL logic or CASE WHEN)

3410125

Create another column in the report and in the column formula write below case statement :

CASE WHEN product='WebCam' then '1'

WHEN product='AC' then '2'

WHEN product='Tv' then '3'

WHEN product='Mobile' then '4'

else '5'

end

This will generate digits in that column. Please right click on the column and select the ascending order and hide the column so that extra column will be visible in the report.

Gianni Ceresa

3410125 wrote:This will generate digits in that column.

Nope, this will generate varchars in that column, so if the OP needs to sort over more than 10 elements the sorting will be '1', '10', '11', ..., '2', '3', etc.

So the CASE WHEN was one of the options suggested at the beginning, but must be done correctly

Also an extra detail: because it's on a single column a SWITCH is shorter to write, and it's a good practice to leave "holes" between the numbers in case you have to change it afterwards and do not want to change everything. For example:

CASE "your table"."your column"

WHEN 'Webcams' THEN 10

WHEN 'Ac' THEN 20

WHEN 'Tv' THEN 30

WHEN 'Mobile' THEN 40

ELSE 999

END

Thomas Dodds

Painful experience conveyed in the master's words ... +1 @Gianni Ceresa

JustTheFacts

As someone who once had to do coding in BASIC or COBOL, I find it difficult to number anything in sequential integers. Even my logical table source priority groups are 10, 20, 30, etc. You just never know when you're going to need 15 in there.

Gianni Ceresa

@Sandesh AG : did you give up on that one?