SQL Server Performance

subscriptions and parameters

Discussion in 'SQL Server 2005 Reporting Services' started by SQLDBcontrol, Nov 30, 2007.

  1. SQLDBcontrol New Member

    Hi,
    Hoping someone can help me here with creating a subscription that contains dynamic parameter values.
    I've got a report that has two parameters (start_date and end_date). The default values for these parameters are start_date = <today -7 days> and end_date = <today>.
    Now, I've got a report subscription that runs this report with the default parameters (i.e. every monday it will run and email the report, which contains 7 days worth of data).
    I would like to create another subscription to this report that runs on the first day of the month, but rather than use the default parameter values I'd like to use different values so that the start_date = <today -1 month> and the end_date = <today>.
    However, according to BOL you must supply a constant value (or use the default) when creating a subscription.
    Is there a way to create different subscriptions that use dynamic parameter values?
    Thanks,
  2. anandchatterjee New Member

    You can put a check in inside the SP.
    If <First Day of Month>
    StartDate=Getdate() - 1 Month
    EndDate=Getdate()

    This can be a round about way!
  3. SQLDBcontrol New Member

    Thanks for the reply.
    That won't quite work because the 1st day of the month could potentially also be the 1st day of the week. But your suggestion has given me an idea....
    I could add another parameter (@weekly bit). And if that's true then do it for the last week, otherwise, do it for the last month, as you suggested.
    Thanks,

Share This Page