Requirement: (OBIEE 11g)
A dashboard should have two prompts - one calendar prompt and one text field prompt giving number of days. There is a timestamp column (SHIP DATE) in the analysis. There should be a between limit in this timestamp field. Logic to be implemented is - Calendar Prompt DateTime <= SHIP DATE < (Calendar Prompt DateTime + No. of days from text field).
Solution Tried:-
Using the dashboard prompt, two prompts and two corresponding presentation variables were created. First presentation variable is for calendar prompt timestamp (Date1) and other one is for no. of days (Num_Days) text field prompt.
Then in the analysis, two filters were created on the Ship Date. First filter is set as 'Is Prompted' and it takes the values '<=' from calendar prompt in dashboard.
For the other filter, I converted it to SQL and wrote the following:-
"Time"."Ship date" <= (timestamp '@{variables.Date1}['YYYY-mm-DD HH:MM:SS']{2013-01-01 00:00:00}') + @{variables.Num_Days}{30}
The analysis runs fine for the above filters, but when run from the dashboard, it throws up an error.
Error getting drill information: SELECT "Time"."SHIP DATE" saw_0, "Base Facts"."QUANTITY" saw_1 FROM "SHIPPING DATA" WHERE ("Time"."SHIP DATE" <= (timestamp '''2013-00-01 00:10:SS''') + 30) AND ("Time"."SHIP DATE" >= timestamp '2013-10-01 00:00:00').
From the above it seems that error is coming because I am unable to set properly the calendar prompt presentation variable in a format which can be read by the analysis. Since analysis is running fine from the default value of Date1, I was thinking of somehow converting the calendar prompt presentation variable in the similar prompt.
Any pointers to the correct approach?
Thanks
Kundan