Oracle Analytics Cloud and Server

How to create a custom sorting in OBIEE 11G

Received Response
884
Views
6
Comments

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

Ac

Mobile

Tv

Webcams

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

Might be like

Products

Webcams

Ac

Tv

Mobile

I was checking all possible solutions in google. 

Please help me out in fixing the requirement.

Answers

  • 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:

    Capture.PNG

    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
    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.

  • 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
    Thomas Dodds ✭✭✭✭✭

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

  • 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.

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