OBIEE Presentation Variable Not working in Filter

Question

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.

Gianni Ceresa · Answer

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).

User_QVJMI · Answer

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.

Joel Acha · Answer

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