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

Variable prompt on date column in 11G

Received Response
41
Views
5
Comments
3201108
3201108 Rank 1 - Community Starter
edited Aug 13, 2024 7:04PM in Oracle Analytics Cloud and Server

Hi Experts,

I have 2 quick queries but before that let me explain the scenario.


I have two variable prompt on my dashboard one for start date and another one is for end date.In report i have two columns calendar date and revenue.


query 1: i want output at my dashboard in such a way that like if i am selecting start date and end date in prompt, then output should be between that two particular dates only.


query 2: i want output at my dashboard in such a way that like if i am selecting start date and end date in prompt, then output should be for that two particular dates only.

Answers

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    Hi,

    First of all, why are you using 2 variable prompts and not a normal column prompt? Do you only have a calendar date column or do you also have a start date and end date columns?

    For your 1st query, if you use a normal column prompt with your calendar date column, you can just set the operator to "is between" and just add a filter in your report that will pick up those values.

    date between.png

    However, for the 2nd query it gets a bit trickier especially if you only have a calendar date column and no other date columns. One way that you can do it is to create those 2 variable prompts you mentioned using the calendar date column and set 2 presentation variables (pv_startdate and pv_enddate). Then in your report you will need to set up a filter such as:

    Calendar date is equal/is in @{pv_startdate}

    OR

    Calendar date is equal/is in @{pv_enddate}

    Cheers

  • 3201108
    3201108 Rank 1 - Community Starter

    Hi Pedro,

    First of all thanks for your valuable suggestion.

    For 1st query i used column prompt and its worked completely as you suggested.

    However for 2nd query i am getting SQL error. I used below SQL code in filter:

    "Time"."T00 Calendar Date" IN (@{PV_startdate})

    OR

    "Time"."T00 Calendar Date" IN (@{PV_enddate})


    I am getting below error in result tab by using above SQL code:

    SQL Issued: @{call NQSGetLevelDrillability('SELECT "Time"."T00 Calendar Date" saw_0, "Base Facts"."1- Revenue" saw_1 FROM "A - Sample Sales" WHERE "Time"."T00 Calendar Date" IN (@{PV_startdate}) OR "Time"."T00 Calendar Date" IN (@{PV_enddate})')}


    Can you please suggest me how to get through above error.


    Thanks!!

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    Create 2 variable prompts like the one in image below, one for pv_startdate and one for pv_enddate.

    dp.png

    Then, create a report where you have a filter such as the one below.

    filter.png

    Put everything in a dashboard, it works fine.

    report.png

  • 3201108
    3201108 Rank 1 - Community Starter

    Hi Pedro,

    Again thanks for your valuable time.

    I tried the above steps but still i am getting the same error as the previous one.

    Please find below the screenshot of filter condition and error message.

    Filter condition.PNG

    Error message:

    Error message.PNG

    Please suggest me how to overcome from this.

    Thanks!!

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    Can you put here a screenshot of how you defined the columns in your dashboard prompt?