Column Selector with Global Filter — 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 with Global Filter

Received Response
1
Views
5
Comments
choracy69
choracy69 Rank 6 - Analytics Lead
edited Aug 13, 2024 7:08PM in Oracle Analytics Cloud and Server

Hi,

I have column selector with: Week, Month and Quarter. I used them in Pivot Table.

I want to interact this column selector with global filter.

For example:

When I choose "Time"."Week" I want to set global filter: DAY_NUM_OF_WEEK = 7

choose "Time"."Month" - > DAY_NUM_OF_MONTH = 31

choose "Time"."Quarter" - > DAY_NUM_OF_QUARTER = 90

How can I do this?

Maybe I can create formule (maybe with CASE WHEN) in global filters, but how can I refer to the selection column week/month/quarter?

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    You can't really interact with a column selector to know which column your user selected, so not really doable with a column selector.

    But you can do it with a normal prompt...

    You create a prompt containing your 3 columns and store the value in a presentation variable, in that way you can use the variable in the filter to do the logic you need with a CASE WHEN and also use it in the formula of your "dynamic column" to act as a column selector.

    http://www.siebelhub.com/main/2016/02/obiee-prompt-columns.html

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Hi Gianni,

    Thanks for reponse.

    Ok, I create variable prompt:

    Presentation variable is: Dim

    And two Custom Values: Month, Quarter

    pastedImage_0.png

    It's working fine when I use this prompt instead column selector (but now implement formule in filters).

    And how can I create formule in filters like this pseudocode:

    CASE

    WHEN @{Dim} = Month THEN DAY_NUM_OF_MONTH=30

    WHEN @{Dim} = Quarter THEN DAY_NUM_OF_QUARTER=90

    END ?

  • Gianni Ceresa
    Gianni Ceresa Mod

    You will need something a bit more complex for the filter as it must still be a SQL-like condition ....

    So you can try a filter like :

    ( '@{Dim}{xxx}' = 'Month'

      AND

      DAY_NUM_OF_MONTH = 30 )

    OR

    ( '@{Dim}{xxx}' = 'Quarter'

      AND

      DAY_NUM_OF_QUARTER = 90 )


    (Just to be sure: this is a filter you want to use in your Criteria tab in the filter area, right? )

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Thanks, I will try this condition.

    Yes, I use this filter in Criteria.

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Ok, it's working fine.

    Thanks Gianni for help.