Column selector - sorting for details — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Column selector - sorting for details

Received Response
33
Views
8
Comments
aPsikus
aPsikus Rank 6 - Analytics Lead
edited Aug 13, 2024 5:41PM in Oracle Analytics Cloud and Server

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:

tenure_group.JPG

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.

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    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
    aPsikus Rank 6 - Analytics Lead

    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
    Robert Angel Rank 8 - Analytics Strategist

    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
    Joel Rank 8 - Analytics Strategist

    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
    Robert Angel Rank 8 - Analytics Strategist

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

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

  • aPsikus
    aPsikus Rank 6 - Analytics Lead 
    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
    Robert Angel Rank 8 - Analytics Strategist

    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
    Robert Angel Rank 8 - Analytics Strategist

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