Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

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

Condition on a Date Range

Received Response
13
Views
2
Comments
3427576
3427576 Rank 4 - Community Specialist
in Oracle Business Intelligence Applications

Hi,

As part of a requirement, a prompt needs to be created with DATE COLUMN  to which a condition need to be applied.
The condition is after selecting the desired value from the FROM PROMPT, the TO PROMPT should allow a date range within 15 days.
For example, in the FROM PROMPT 11/9/2017 is selected the TO PROMPT should allow values below this date 11/24/2017.

I am ok if the rest of the dates are disabled or a notification/alert is prompted with a message  "please select a date with in 15 days range from FROM DATE"

please do the needful. Thanks in Advance.

Answers

  • Souvik Sarkar
    Souvik Sarkar Rank 3 - Community Apprentice

    Hi,

    Use the Presentation variable to perform this task. Create the prompt for the FROM DATE prompt and set a Presentation variable (say PV_FRM_DT) for it. In the same prompt, create another date column prompt which should show values based on the FROM DATE presentation variable (using the sql query method to populate data in the prompt values) to limit the date as per the requirement. When writing the select query for the prompt values, limit the date using PV_FRM_DT and DATEDIFF function. Along with this, create the filter prompts for these two column prompts in the report also. I hope that fulfils the requirement.

  • 3586388
    3586388 Rank 3 - Community Apprentice

    Hi Souvik,

    Thanks for reply. I have done the date range validation.

    Here My validation is up to one Year. If i select 01-01-2016 it will will allow us to select uo to 01-01-2017 only.

    Here

    Report From Column SQL:

    SELECT "Time"."Date" FROM "Loyalty - Transactions"   WHERE  "Time"."Date" <= CURRENT_TIMESTAMP ORDER BY "Time"."Date" DESC FETCH FIRST 65001 ROWS ONLY

    pastedImage_10.png

    Create Report From column based on the date column. Give the Presentation Variable name as Date_From.

    Create one hidden prompt and Use Date_From presentation variable in default selection of the prompt. Again Create presentation variable for the hidden prompt that will be used in To_Date Prompt.

    We can't pass presentation variable from one column to another column in same set of prompt. that is the reason we are passing presentation variable out side of the prompt(Hidden Prompt) then again we re using in To_date Column.

    Default Selection:

    SELECT '@{Date_From}' FROM "Loyalty - Transactions" FETCH FIRST 65001 ROWS ONLY

    pastedImage_17.png

    To_Date Column SQL:

    SELECT "Time"."Date" FROM "Loyalty - Transactions"   WHERE "Time"."Date" BETWEEN @{From_Date} AND TIMESTAMPADD(SQL_TSI_YEAR,1,@{From_Date}) AND "Time"."Date" <= CURRENT_TIMESTAMP ORDER BY "Time"."Date" DESC FETCH FIRST 65001 ROWS ONLY

    pastedImage_13.png

    Use these prompts in same dashboard then only we will get expected behavior.

    Dashboard:

    pastedImage_22.png

    pastedImage_21.png.

    Thanks

    Ramesh Rathod