Setting Variable Parameters in a SSRS subscription

Question

I would like to set up a subscription that has two date parameters, I would like the end_date to be today and the start_date to be (today - 1 Month). The interface does not seem to support expressions?

 

I saw some documentation that said to use defaults in the report but that does not help because I may want multiple subscriptions with different params like 1 person may want (today - 2 months) as the start date ...

 

Any help would be appreciated

Answer 1

 

You can create a custom table in the backend and setup a data driven subscription. The custom table can have the different users mapped to the number of months they'd go back to when viewing the report.

Answer 2

Hi,

 

I had a simular problem a while ago and i solved it by creating an extra paramter for the report.

I call it SubscriptionParam. I set it to datatype integer. Then i created a few non queried values like:

Label                            Value

Free                              0

Today                            1

Yesterday                      2

Last week                      3

......

you can create as much time selection as you want.

 

I put this parameter as my first report parameter (so above my start and end date)

 

Then set the default value of the start date as:

=switch(Parameters!SubscriptionParam.Value=0,today(),Parameters!SubscriptionParam.Value=1,today(),Parameters!SubscriptionParam.Value=2,dateadd("d",-1,today()),Parameters!SubscriptionParam.Value=3,dateadd("d",-7,today()))

 

and the default value of the end date as:

=switch(Parameters!SubscriptionParam.Value=0,today(),Parameters!SubscriptionParam.Value=1,today(),Parameters!SubscriptionParam.Value=2,dateadd("d",-1,today()),Parameters!SubscriptionParam.Value=3,dateadd("d",-1,today()))

 

Now in the subscription you can select a specific parameter that will set your start and end date to the selected time

period.

 

Hope this helps.

Vinnie

Answer 3

Yes Data-Driven seems to be the way to go.

 

Thanks,

 

Phil

 

Answer 4

Vinnie,

This solution works.  However, is there anyway to use the parameter to change the dates when running the report at runtime?

For example, if I first execute the report and select "Yesterday", the dates are populated correctly.  However, if I then change the parameter to "Last Week", the dates do not get refreshed.

Is there anyway to accomplish this?

Thanks,
rachlh22

Answer 5

Easiest way I found was to set the required values as the default dates in the actual report.
That way they will be pre-populated with those dates for the subscription but anyone running it manually can change the dates to what they require.

E.G. to have the start_date as 1 month previous you would put the following in the Non-queried section of the report parameter.
=DateAdd("m", -1, Today())

Answer 6

Hi VSempoux

I tried your method. I am getting error

---

This is the Parameter Defination i have set

Thanks & Regards,
Manjunath C Bhat,
http://manjunathcbhat.blogspot.com
http://manjunathcbhat.wordpress.com