Evaluate function to convert presentation variable to date not working..
724120Jan 5 2010 — edited Jan 6 2010Hi,
I have a calendar prompt on my dashboard and the presentation variable for this prompt is PV1.
I am using this variable in my report to create a calculated column. The condition for the calc column runs something like
case WHEN "Time"."Day" >= ANY(SELECT saw_3 FROM(SELECT "Time"."Start Date" saw_3 FROM "xyz_subject_area" WHERE "Time".Day = CAST(EVALUATE('TO_DATE(%1,%2)' AS DATE, '@{PV1}{@{system.currentTime}[MM/DD/YYYY]}', 'MM/DD/YYYY HH:MI:SS AM' ) AS DATE)) EX1) then "ABC"."Units" else "XYZ"."Units" end
However when I checked the expression EVALUATE('TO_DATE(%1,%2)' AS DATE, '@{PV1}{@{system.currentTime}[MM/DD/YYYY]}', 'MM/DD/YYYY HH:MI:SS AM' ) is not returning the date always. I am not sure why for some rows it is working correctly and for some it is not returning any value.
Please let me know if there another better way to convert the presentation variable value to date.