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

creating dashboard prompt based on column in analysis with case statement

Received Response
13
Views
6
Comments
Joe Choueiri-Oracle
Joe Choueiri-Oracle Rank 5 - Community Champion
edited Aug 13, 2024 7:05PM in Oracle Analytics Cloud and Server

Hi

I am trying to create a dashboard prompt based on column in analysis with case statement

I have a case statement in one of the column let us say to show "sell to " and "sell thru"

the case statement works perfect on the report but now I want to create a prompt so the user can pick either "sell to" or "sell thru"

I was able to do it but then the numbers dont make sense.

If the prompt has both selected the $ is 10,000 (for example) if I select only sell to I get 11,000

any idea?

Thanks

Answers

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    - Do you have any null values in your case statements?

    Kind Regards,

  • Joe Choueiri-Oracle
    Joe Choueiri-Oracle Rank 5 - Community Champion

    I see what you are saying...here is my case statement

    CASE WHEN "Bill Customers"."Bill Customer Party Number" = "Ship Customers"."Ship Customer Party Number" THEN 'Sell to' ELSE 'Sell thru' END

  • Joel
    Joel Rank 8 - Analytics Strategist

    HI @Joe Choueiri-Oracle

    have you tried to look at your NQquery.log file to see what SQL is being generated by the BI Server. Also, you need to make sure your CASE statement formula in the column matches logic in your dashboard prompt (always best to copy/paste from column formula).

  • Joe Choueiri-Oracle
    Joe Choueiri-Oracle Rank 5 - Community Champion

    @Joel Acha

    How do you suggest the prompt should be created- because are few ways of doing right?

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    The easiest way to get the report column and the prompt column the same is use the same presentation column -- you're best served by doing the case in a logical column and putting it in your subject area (sometimes I will create a sub-folder special for prompting and filtering for these types of columns) ...

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    First, this case statement you compare two columns and you get a result this a little weird,

    just think about it OBIEE execute this code

    SELECT

    CASE WHEN "Bill Customers"."Bill Customer Party Number" = "Ship Customers"."Ship Customer Party Number"

             THEN 'Sell to'

              ELSE 'Sell thru' END

    FROM "Your Subject Area"


    The question is why are you using that, you just need 'Sell to' and 'Sell thru', as a workaround you could use this values

    as a hardcode values:


    Prompt.png


    And use your presentation variable in your report with syntax, in your FILTER of your REPORT:


    '@{var_Pr}{DefaultValue}'


    Kind Regards,