Scheduling Montly Report

Hi all,

I currently have a report which needs to be scheduled to run on every 1st of the month to get records of last month. For example, on Feb. 01 the report will get all records by the date range of Jan. 01 to Jan. 31. My current report does not have any defined report parameter. It gets all the date range it finds from the table.

Please advise how can I start with this.

Any help is much appreciated! Thanks!

Hi,

Couldnt get it completely.

you want to run a report on monthly basis, then you need to have two datetime parameters i.e. from_time and to_time, then you want default value to be last months dates, use expression for the defualt value....

your expr. for "from time" should be like

=switch(month(now())=1,"01/01/2007",

month(now())=2,"02/01/2007, and in same way you can have it for to time frame....

HTH

Priyank

|||

Hi

As i understand the requirement over here is to have a report which shows the data for previous month

say the report is executed on mar 1st then the report should display data till feb28th correct?

For this a report has to be created with 2 parameters Startdate and EndDate.

However subscription can be created in Report Manager.

Regards

Smitha

|||

You don't need a param for this, although to give you an perfectly appropriate example it would be good to know if you are calling a stored procedure or writing the query directly in the report, or what.

Let's say you are writing the query directly in the report, not a stored procedure.

You already know that you can schedule it to run on the first of the month, right? Here is a query that will do what you want, given that you know you are running it every month on the first:

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,GETDATE()-1,<YOUR DATE COLUMN>) = 0

' the default behavior of the date subtraction is by days, see?

' and you're running the subscription on the first of the month,

' so this works.

Let's make it better. Here is a query that will do what you want, no matter what date of the month it is run on, which is : provide the results for the month *before* the month in which it is run:

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,DATEADD(month,-1,GETDATE()),

<YOUR DATE COLUMN>) = 0

Now, if you prefer, you can add a parameter and give your parameter the default value of Today (you may have to do some CASTing in here, not checking this):

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,DATEADD(month,-1,@.YourParam),

<YOUR DATE COLUMN>) = 0

... hope this helps,

>L<

|||

Thank you all for the helpful solutions.

Yes basically it is just to get a report of previous month when it is scheduled to run.

|||

Lisa that's a cool little function!

Only question I have it whether it would work if you had data for more than one year in the table? i.e If you had 2 rows, one with July 2006 and one with July 2007 wouldn't the DateDiff(month, etc) return 0 for that even though the year is different?

As my own $0.02 I usually use something like

Beginning of last month

DATEADD(month,DATEDIFF(month,0,getdate)-1,0)

Beginning of this month

DATEADD(month,DATEDIFF(month,0,getdate),0)

Beginning of next month

DATEADD(month,DATEDIFF(month,0,getdate)+1,0)

which is sort of based off similar logic. Found it on a forum, and loved it. Can also do days/weeks by just switching the period name.

|||

>> whether it would work if you had data for more than one year in the table

It will actually work fine, D, check it out:

select datediff(month, '1/1/2007','1/1/2004')

will return -36 even though both dates are (obviously) in January. 3 * 12.

It's a *difference of months*. Not a difference of *month numbers* <s>.

>L<

|||Right you are - I should have run it through Query Analyzer first