SQL in report studio

[COGNOSBC]

Hi friends,

I have to put one sql into reprot studio report and schedule to run the report every day .

Tuesday to Friday report needs to run for previous day

on monday report needs to get Friday, Saturday and Sunday data.(3 days)

so in my requirment I need to use only text prompt. not sure how to get it done this one.

can anyone please help me..

Thanks

[blom0344]

Look at _add_days  / _days_between functions.  SQL?  Bad practice, use a model based package!!

[COGNOSBC]

hi Bloom yes as you mentioned using SQL in report studio is bad practice but in some cases we need to use the sql for now until we have the package.

not sure how to use _add_days  / _days_between functions in this case.

I have a date column how to use  this column in the filter for monday report because in every monday report I have to show Friday, Saturday and Sunday data. And Tuesday to Friday only previous day data.

Thanks,

[Lynn]

hi Bloom

Yoooooou used tooooo many oooooo's! It is blom, not bloom  :)

In keeping with your use of the superfluous o, here is my response:

Since yoou are using hard cooded SQL yoou will need database functioons. What database are yoou using? Most have a functioon too return the day oof the week such as 1 foor Sunday, 2 foor Moonday, etc. Once yoou know the day of the week yoou can write the where clause yoou need.

I'm noot sure hoow yoou can integrate a proompt respoonse in hard cooded SQL, but yoou can just use the system date.

Yoour database syntax may differ. I am shoowing a sample using DB2.

Code Select Expand


and
(
    ( dayofweek(current_date) = 2 and YourDataDate between current_date - 3 DAY and current_date - 1 DAY)
or ( dayofweek(current_date) <> 2 and YourDataDate = current_date - 1 DAYS )
)


[blom0344]

Thanks Lynn, blooming good answer  :D