Categories
- All Categories
- 4 Oracle Analytics Videos
- 13.7K Oracle Analytics Forums
- 5.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 57 Oracle Analytics News
- 32 Oracle Analytics Trainings
- 53 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
How to create a custom sorting in OBIEE 11G
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:
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)
0 -
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.
0 -
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
0 -
Painful experience conveyed in the master's words ... +1 @Gianni Ceresa
0 -
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.
0 -
@Sandesh AG : did you give up on that one?
0