yesterday and today with subscriptions

I want to have a subscription for a report that shows activity for a day (and week). how do i put in the parameters at the report server?

what i want logically would look something like:

begin date: today() - 1
end date: today()

AND

begin date: today() - 7
end date: today()



Joe Janka

I determine the dates in the SP according to the passed in parameter:


DECLARE @MyStartDate SMALLDATETIME
DECLARE @MyEndDate SMALLDATETIME

IF @Parameter = 1 -- yesterday
SET @MyStartDate = DATEADD(d, DATEDIFF(d, 0, GETDATE()-1), 0)

ELSE -- last week
SET @MyStartDate = DATEADD(d, DATEDIFF(d, 0, GETDATE()-7), 0)


SET @MyEndDate = DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)


In this case, you can create two subscriptions. First one pass in 1 as parameter for yesterday, second one pass in 2 as parameter for last week.