December 5, 2008 at 8:29 am
hi,
i have set up a report which is having startdate and enddate parameter. I have set up a subscription which should send the data of last week every friday. I need to specify that value into parameter. something like getdate()-11 to getdate()-5. I cant write that into value of parameter.
I can set it up using datadriven subcription but it is difficult to handle the recepient into that query and complex to understand for other people. Is it possible to specify the expression into parameter of simple subscription?
thanks,
vijay
December 5, 2008 at 1:52 pm
vj,
Not sure what you mean by 'last week every friday'.
I have several report being subscribed with start and end month/date parameters.Example,Start Month is [first day of last month,Nov 1] and End Month is [last day of last month,Nov 30] Here is what I have done to create a data driven subscription.
DECLARE @StartMonth VARCHAR(100)
DECLARE @EndMonth VARCHAR(100)
SELECT @StartMonth=DATEADD(d,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) -Nov 1
SELECT @EndMonth = DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) -Nov 30
SELECT @EndMonth AS EndMonth, @StartMonth as StartMonth
Maybe this might help you.
December 6, 2008 at 2:11 am
Hi,
thanks for your reply. Yes i can do it using data driven subscription. there is no problem. But i was thinking to do it via subscription (simple subscription).
I have one report which should go on daily basis via subscription along with weekly basis.
I have created one new parameter called type - weekly and daily. Now when i pass this parameter into stored proecedure according to type value i will retrieve the data into report.
Then i have created two separate subscription one with type - weekly and another subscription with type - daily. SP will sort out the output of the report. and it will be sent to different people.
It is good solution for me. thanks for your help.
regards,
vijay
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply