Schedule report two different time frames

building reports webintelligence
1

I need to schedule a report

requirements

  1. Run twice a day once at 8AM and then at 12.00 PM

  2. When run at 8 AM it should run it for REC_DATE between 12 PM prior day and 8 AM today

  3. when run at 12 pm it should run it for REC_DATE between 7 AM today till 12 pm today

The way i am doing this is by two reoccouring schedules using the schedule option of Once a day . I created a custom filter for recv_date for each report.

Is there a better way to do this ?

honkeyhindu (BOB member since 2007-03-29)

2

looking through the forum i found a suggestion to create an object using a case statement with a prompt to be able to select different values for a date or anything else, which would be useful when having to schedule one report --different prompts.


CASE @Prompt('Rev Date Period','A',{'Mondays','Midday','Morning'},mono,constrained,persistent,) 
   WHEN 'Mondays' THEN OUTBOUND_STAGING.MR_RECIEVE_DATE between ((trunc(SYSDATE) +7/24)) and (trunc(SYSDATE) +12/24) 
   WHEN 'Midday' THEN OUTBOUND_STAGING.MR_RECIEVE_DATE between (trunc(SYSDATE -1) +12/24) AND (trunc(SYSDATE) +7/24)
   WHEN 'Morning' THEN OUTBOUND_STAGING.MR_RECIEVE_DATE between (trunc(SYSDATE -3) +12/24) AND (trunc(SYSDATE) +7/24)
END

however when i try to parse this i get an error Invalid Definition (unv0023) error. ?

honkeyhindu (BOB member since 2007-03-29)

3

well after hours of trying to get this done in a case statement i switched tatics and went with a filter ( where statement)

(OUTBOUND_STAGING.MR_RECIEVE_DATE between ((trunc(SYSDATE) +7/24)) and (trunc(SYSDATE) +12/24) and @Prompt('Rev Date Period','A',{'Mondays','Midday','Morning'},mono,constrained,persistent,)='Midday') or (OUTBOUND_STAGING.MR_RECIEVE_DATE between ((trunc(SYSDATE -1) +12/24)) and (trunc(SYSDATE) +7/24) and @Prompt('Rev Date Period','A',{'Mondays','Midday','Morning'},mono,constrained,persistent,)='Morning')or(OUTBOUND_STAGING.MR_RECIEVE_DATE between ((trunc(SYSDATE -3) +12/24)) and (trunc(SYSDATE) +7/24) and @Prompt('Rev Date Period','A',{'Mondays','Midday','Morning'},mono,constrained,persistent,)='Mondays')

this code allows me to select three promts when scheduling a report

  1. Mondays the report runs between ((trunc(SYSDATE -3) +12/24)) and (trunc(SYSDATE) +7/24)
  2. MIdday runs between (SYSDATE) +7/24)) and (trunc(SYSDATE) +12/24)
    3.Mornings runs between ((trunc(SYSDATE -1) +12/24)) and (trunc(SYSDATE) +7/24)

honkeyhindu (BOB member since 2007-03-29)