Prompts ignoring time values

[Former Member]

We have two prompts to get start and end date.The input to the prompt is taken from a calendar.

Enter Event Time(Start):  1/27/2013 12:00:00 AM

Enter Event Time(End):  1/27/2013 11:00:00 PM

prompts are considering only the date and ignores the time specified.

Below is one of the query generated. We can clearly see that it picks only the date(Marked in Red).

SELECT

  DIM_E_SGEH_RAT.RAT_DESC,

DIM_E_SGEH_ATTACHTYPE.ATTACH_TYPE_DESC,

COUNT(EVENT_E_SGEH_RAW.DATETIME_ID),

  SUM(CASE WHEN EVENT_E_SGEH_RAW.EVENT_RESULT=0 THEN 1 ELSE 0 END),

  SUM(CASE WHEN EVENT_E_SGEH_RAW.EVENT_RESULT=1 THEN 1 ELSE 0 END),

  SUM(CASE WHEN EVENT_E_SGEH_RAW.EVENT_RESULT=2 THEN 1 ELSE 0 END),

  SUM(CASE WHEN EVENT_E_SGEH_RAW.EVENT_RESULT=3 THEN 1 ELSE 0 END),

  EVENT_E_SGEH_RAW.IMSI

FROM

  DIM_E_SGEH_ATTACHTYPE INNER JOIN EVENT_E_SGEH_RAW ON (DIM_E_SGEH_ATTACHTYPE.ATTACH_TYPE=EVENT_E_SGEH_RAW.ATTACH_TYPE)

   INNER JOIN DIM_E_SGEH_RAT ON (DIM_E_SGEH_RAT.RAT=EVENT_E_SGEH_RAW.RAT)

WHERE

  (

EVENT_E_SGEH_RAW.IMSI  =  454063307033944

   AND

EVENT_E_SGEH_RAW.EVENT_ID  =  0

   AND

EVENT_E_SGEH_RAW.EVENT_TIME  BETWEEN  {d '2013-01-27'}  AND  {d '2013-01-27'}

   AND

   (

    ( 1 = 1 )

    AND

    ( 1 = 1 )

   )

  )

GROUP BY

  DIM_E_SGEH_RAT.RAT_DESC,

  DIM_E_SGEH_ATTACHTYPE.ATTACH_TYPE_DESC,

  EVENT_E_SGEH_RAW.IMSI

How can I resolve this problem and why is it happening?

[Former Member · Accepted solution]

Hi,

there is a setting responsible for the default date/hour format in the where clause. Check the property

USER_INPUT_DATE_FORMAT of the corresponding PRM file. More information here:

http://help.sap.com/businessobject/product_guides/boexir4/en/xi4sp5_info_design_tool_en.pdf#page=367

Best regards,

Victor

[former_member182521]

Try to to formulated you date object in prompt to inline with the table column(EVENT_E_SGEH_RAW.EVENT_TIME)