Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

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

OBIEE Presentation Variable Not working in Filter

Received Response
171
Views
3
Comments
User_QVJMI
User_QVJMI Rank 3 - Community Apprentice
edited Aug 13, 2024 5:14PM in Oracle Analytics Forum

Respected Members,

Can someone please resolve my issue trying to built MTD report(Date select from prompt):

1- I have Date column "Calendar Date"."Calendar Date" presentation variable @{P_DATE}

2- When I select Date from front end Prompt it should run for all dates in the condition

Filter:

"Calendar Date"."Calendar Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, 0, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH(date '@{P_DATE}{2016-03-25}') * -(1) + 1,date '@{P_DATE}{2016-03-25}'))

AND date '@{P_DATE}{2016-03-25}'

But unfortunately it gives result of only 1 row of the date which I have selected from PROMPT.

Whereas, if I replace the presentation variable with CURRENT_DATE it gives the correct result.

Please help.

Tagged:

Answers

  • Joel Acha
    Joel Acha Rank 8 - Analytics & AI Strategist

    Have you checked the physical SQL generated by the BI Server? Is that generating the correct BETWEEN clause?

  • User_QVJMI
    User_QVJMI Rank 3 - Community Apprentice

    Thank you Joel for your reply I am using correct SQL statement. Column prompt variable is not working for this filter whereas a standard presentation variable worked. But I want to integrate Column prompt presentation variable for this.

  • Gianni Ceresa
    Gianni Ceresa Mod

    You maybe misread what Joel was saying: what does the physical SQL looks like?

    It must be a valid query as you get something back, but what is it like? Because if the physical query is correct the result can't be wrong (ok, it can if you have selection steps or other things happening at the BI Server level, but you didn't say anything letting us believe something like that could exist).