none
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

    Wednesday, November 07, 2007 9:40 PM

Answers

  • 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

    Monday, November 12, 2007 2:33 PM
  •  

    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.
    Monday, November 12, 2007 12:25 PM

All replies

  •  

    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.
    Monday, November 12, 2007 12:25 PM
  • 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

    Monday, November 12, 2007 2:33 PM
  • Yes Data-Driven seems to be the way to go.

     

    Thanks,

     

    Phil

     

    Monday, November 12, 2007 8:42 PM
  • 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
    Tuesday, February 24, 2009 4:07 PM
  • 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())
    Tuesday, February 02, 2010 1:49 PM
  • 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


    Saturday, January 19, 2013 6:47 PM
  • This works, but for some reason, even with always refresh selected, it only seems to work once.  However in subscriptions, it will update each time it is selected.  Odd.

    Friday, September 18, 2015 1:10 AM