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