Create a dynamic date filter that automatically runs a report for last week

[Former Member]

I would like to create a filter to use in my reports that takes todays date minus 1 day for the end date and takes todays date minus 8 days for the beginning date.

I can then schedule my reports to run every week and know that the filter is looking at the correct date range.

Does anyone have the correct way to go about this? (MSQL 2005)

Regards, GK

[Former Member]

Regarding BI you can do something like these:

just take a variable suppose f_DateFilter

now edit the variable like this

f_DateFilter =If(CurrentDate()>=RelativeDate(CurrentDate();-8) And CurrentDate()<=RelativeDate(CurrentDate();-1);1;0)

Now in report designer window, select the whole report and press the Show Hide Filter Pane. and just drag the variable on filter area. A promt will come this time. Just add 1 in the promt box and press OK Button. Just do it. I think it will be worked

[amrsalem1983]

OR YOU CAN USE THIS

MYTAB.MYDATE BETWEEN dbo.DateOnly(getdate())+8 AND dbo.DateOnly(getdate())-1

i think this works fine

good luck

[Former Member]

Database.dbo.Table."Inv Date" BETWEEN (getdate())-8 AND (getdate())-1 did the trick, thanks heaps for pointing me in the right direction. I can now go home..

[amrsalem1983]

where is my points

hahahahahaha

[Former Member]

Oh, I didn't know about the points, you should have some now. Thanks again.

[amrsalem1983]

never mind, i was kidding

you may need to evaluate the answer you get from the users of the forum

you have

helpful,

very helpful

correct

based on ur evaluation you assign some value to the answer.

good luck

[amrsalem1983]

you can create a predefined filter in your universe, and put this folder to the report. so you can schedule the report as you want wihout entering any parameters.

there is a function called getdate() in MS SQL2005

im not much into MS SQL 2005 but you can try this in your filter

MYTAB.MYDATE BETWEEN GETDATE()-8 AND GETDATE-1