scheduling

scheduling XI Scheduler
1

hi,
we are using xi3.1 and oracle 10g and we need to schedule several reports in a custom way.
we need to schedule reports in such a way that the each report should
pick the latest month.
for example . if i schedule a report on 1 dec 2009 , it should take the latest month as Nov 2009 data and run.
similarily, so for each month of scheduling the prompt month should automatically pick the previous month for the user.( user need not select the previous month).

please let us know is there is way to do this.

thanks in advance.
kris

dardedisco (BOB member since 2007-02-10)

2

Yes, build a Predefined Condition in your universe that uses SYSDATE to apply the filter logic.

joepeters :us: (BOB member since 2002-08-29)

3

hi,

Got u but, we want that it should AUTOMATICALLY pick from the
LOV’s.
there are hundreds of reports.
plz let me know if you have any other options.
thanks
kris

dardedisco (BOB member since 2007-02-10)

4

Well, that would be automatic – you’d add the object to the report and just schedule it once.

joepeters :us: (BOB member since 2002-08-29)

5

we have a Prompt as Month& year and lov’s like june 2009,july 2009,aug 2009
etc.
so when user opens a reports the report should pick the latest month and year . ie the report should open by default with previous month as the prompt. and this should happen for each and every month.

if the user want some other month(old months) then he can change the date prompt.
this requirement we need to do as scheduling.
thanks
kris

dardedisco (BOB member since 2007-02-10)

6

You can use additional logic to handle both conditions. For example:

CASE WHEN to_char(@prompt('Month:','D',,Mono,Free)) = '19000101' THEN trunc(add_months(sysdate,-1),'mm') ELSE @prompt('Month:','D',,Mono,Free) END

Users can still enter a valid date when running the report interactively, but you can also use 1/1/1900 to use the previous month.

Joe

joepeters :us: (BOB member since 2002-08-29)

7

Can you please explain be more specific.

will this prompt will work for reports where the prompt is char/number.

some of the reports have char format and some reports have number format as date prompt.
eg Jun 2009 is char

200906 as number prompt

Is is possible with the format you gave or is there any other format that will work for both of these conditions.

thanks in advance for all your efforts…
thanks

kris

dardedisco (BOB member since 2007-02-10)

8

Sure – it’ll work fine with any format, you just have to adjust the formula accordingly. If your date is stored as a number in yyyymm format, then the formula would be:

CASE WHEN @prompt('Month:','N',,Mono,Free)) = 0 THEN to_number(to_char(trunc(add_months(sysdate,-1),'mm'),'yyyymm')) ELSE @prompt('Month:','N',,Mono,Free) END

In this case, you’d enter “0” to use the prior month.

Joe

joepeters :us: (BOB member since 2002-08-29)

9

Hi Joe,

I followed with your solution but we are haveing some issue.
below is the prompt im using:

MonthYear=CASE WHEN @prompt(‘Enter Year Month:’,‘N’,‘classnmae\objectname’
,Mono,Free) = 0 THEN to_number(to_char(trunc(add_months(sysdate,-1),‘mm’),‘yyyymm’)) ELSE @prompt(‘Enter Year Month:’,‘N’,‘classnmae\objectname’
,Mono,Free) END

when i use this prompt, in the table values im not finding the default date eg 200909 but instead its blank and i have to manually pass the date eg 200909 and then it will show the Table values.

plz let me know if my approach is correct.
thanks
kris

dardedisco (BOB member since 2007-02-10)

10

Use 0 instead of blank.

joepeters :us: (BOB member since 2002-08-29)