nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Column selector - sorting for details

aPsikus

Hi,

Using OBIEE 12c.

Report with column selector. One of the possible options is Tenure group (length of service grouped) containing CASE statement:

CASEWHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <=0 THEN 'Future date'WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <12 THEN '<1 year'WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <48 THEN '1-3 years'WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <72 THEN '4-5 years'WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <132 THEN '6-10 years'WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <192 THEN '11-15 years'WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <252 THEN '16-20 years'WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <312 THEN '21-25 years'WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') >=312 THEN '25+ years'ELSE 'Not defined'END

Result is standard alphabetical sorting:

Is it possible to somehow implement own sorting for this details and not affect any other selections?

In report where there is no Column selector I could add hidden column named "Sorting" with CASE statement assigning numbers for each detail.

If I will add such column in the report with column selector layout will be collapsed.

Find more posts tagged with

Accepted answers

All comments

Robert Angel

Hi,

if you can have the case logic in a dimension in the rpd then you can also apply sorting based on another column.

Not sure if there is anyway to achieve this with column selector that is 'pure' analysis based solution otherwise, other than prefixing your column with A. < 1 Years, B. 1-3 Years etc...

aPsikus

Hi,

I can create dimension in RPD with CASE. Thank you for the hint with setting sorting in RPD also.

And what do you mean by prefixing? Should it be created in the analysis?

Robert Angel

Hi,

the two comments are separate.

If you make the case logic in the rpd then you don't need the prefixing, that was my comment for if you do not have access to change the rpd and can only make the case logic in the analysis, there I was suggesting that by making your prefix on the column give you the right order it was a small presentational sacrifice to get what you wanted.

The rpd solution is superior on a number of fronts, I would go with that suggestion if you are able.

Joel Acha

I'm sure @Robert Angel will agree with me when I suggest that you create a dimension with a sort column and the CASE statement logic as a 2nd column whee the 1st is used to set the order.

The prefix solution is a bit more crude in which you'd append your case statement with A,B,C,D etc to force a sort on based on the 1st letter appended to your column. If you can create the dimension then use that option.

Robert Angel

Yes, he will!

In case (no pun intended) I was less than clear, I had meant to create a parallel column using the same statement but with numbers returned for the sort order, and then to use this on the displayed column with the original case logic.

For the sort order column; -

CASE
WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <=0 THEN 0
WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <12 THEN 1
WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <48 THEN 2
WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <72 THEN 3
WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <132 THEN 4
WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <192 THEN 5
WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <252 THEN 6
WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <312 THEN 7
WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') >=312 THEN 8
ELSE
END

aPsikus

Robert Angel wrote:Yes, he will!In case (no pun intended) I was less than clear, I had meant to create a parallel column using the same statement but with numbers returned for the sort order, and then to use this on the displayed column with the original case logic.For the sort order column; -CASE WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <=0 THEN 0
WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <12 THEN 1WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <48 THEN 2WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <72 THEN 3WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <132 THEN 4WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <192 THEN 5WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <252 THEN 6WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') <312 THEN 7WHEN timestampdiff(sql_tsi_month,"Person"."Vesting Date",date '@{PV_ENDDATE}{2018-04-30}') >=312 THEN 8ELSE  END

As I have mentioned in my initial post, if you will do this on analysis side, this will work only when you do not have Column selector, in standard analysis.

If you will add this code as separate column, even if will be hidden will make a split for any other selection in column selector.

I will try to check if in RPD as sorting column you can use CASE statement, or it will require to create two seperate domensions.

I have clearly understood you first propositions, but was not sure what do you mean by prefix. Now I know. Just name the values in CASE with additional alphabetical letters at the beginning.

Robert Angel

Hi,

so you create two new dimension attributes in your rpd,

1. Is the original case statement 'Vesting Group'

2. Is the sort order column 'Vesting Sort Order'

You then change the property on 'Vesting Group' for sort order to make it sorted by 'Vesting Sort Order'

Then whenever column1 appears in any analysis it will be sorted in the 'Vesting Sort Order'

No need for additional hidden columns or any other logic 'tricks'.

Make sense?

Robert Angel

And prefix is only applicable to the 'nasty little analysis workaround' - I would recommend my rpd centric solution.