none
Are @ReportName and @ExecutionTime the only variables available to Subscriptions?

    Question

  • Hi

    I've had a search around for this and consulted my text book and I've found nothing.

    When you create a new subscription for email, the default subject line is "@ReportName was executed at @ExecutionTime".

    Are there any other variables that I can use?  Or can I perform any logic against these variables?

    I would like the subject line to read "
    @ReportName was as of" and then for the date to be one day before the execution time.  Its exactly the kind of thing you'd normally do with a dateAdd(dd, -1, @ExecutionTime) in SQL.

    Thanks in advance, I hope you guys can help/
    Thursday, July 03, 2008 3:46 PM

All replies

  •  

    I would also like to know the answer to this question!
    Friday, July 18, 2008 4:39 PM
  • Hi,

     

    Have you had any success on solving this? I am also facing the same problem.

     

    Cheers.

     

    Wednesday, November 19, 2008 5:42 AM
  • Gonna guess there is no way around the parameter

    I google'ed quite a bit :)
    Jerry Hung
    Thursday, April 23, 2009 1:40 PM
  • two years later, i'm looking too....
    Wednesday, May 11, 2011 3:32 PM
  • You can do this by using a data-driven subscription instead of a regular subscription.

    First, craft a query that returns one row for each report you want to generate. In your case just converting from a regular subscription, a simple "SELECT 1" would do the job. But you're going to take it a step further.

    One of the values that you can set using a data-driven subscription is the Subject. Start by returning the subject in a column of the rowset. Here's a sample to do what you were asking for:

    SELECT 'ReportName was as of ' + Convert(varchar(100), DateAdd(Day, -1, GetDate())) Subject;
    

    Then, bind this column to the Subject in the subscription wizard.

    There are a host of options that you can set dynamically. If you want to set a value dynamically, just put an expression in your SELECT statement that will return the value you want and bind it in the subscription setup wizard to the report item.

    Data-driven subscriptions are a great way to send reports conditionally, since if you return no rows at all then no reports are sent. You can also craft reports that are intended for single recipients, then by returning one row for each recipient with that person's email address, have the report delivered separately to each person (perhaps even with a different parameter set for each one).

    Another way to use data-driven subscriptions is to reduced the brittleness of scheduled items. Let's say you have some important report that runs at month-end. What if during the time the report is supposed to run, you have an unexpected down time with your database or the SSRS web server? You can insulate against this like so:

    1. Create a table that contains the data necessary to represent the fact that a particular time period's report has been processed. If it is a monthly report then you will make it a date, probably the first of the month in which the report was properly sent.

    2. Modify your report to have a hidden @SaveToDatabase or @FulfillReport Boolean parameter with the default set to false. If your report uses a SELECT statement, convert it to a stored procedure. Then, modify the stored procedure to accept this parameter. Inside the stored procedure, insert to the report-fulfillment tracking table the correct value only if this parameter is set. This parameter lets you preview the report all you like and run it on demand without affecting your official reporting schedule.

    3. Finally, create a data-driven subscription that runs *daily*. In the data-driving query you will query for whether a report is needed based on the current date and the presence or absence of the corresponding month's report value in your report-fulfillment tracking table. When there are no reports needed, return an empty rowset. When a report IS needed, return one row for each outstanding unfulfilled report, with a column set to the correct month for that item, to be bound to the month parameter to run each report with. Set the @FulfillReport parameter to True in the data-driven subscription.

    4. Now, even if your report server is down for an entire month (unlikely, but perhaps it's a daily report or a weekly report) when it finally comes back up, the reports that were missed will pop out right away, with the correct month parameter.

    This should give you ideas about how powerful data-driven reports can be.

     




    • Edited by ErikEckhardt Tuesday, June 07, 2011 7:07 PM just fixing missing holes, clarity
    • Proposed as answer by HomeCookN Friday, February 17, 2012 3:15 PM
    Tuesday, June 07, 2011 7:05 PM
  • What a great, great post, Eric. I stumbled upon it seaching for answers to both issues your detailed explanaitons covered so well. Can't believe it you received no responses to this!

    HomeCookN

    Friday, February 17, 2012 3:18 PM
  • Only one problem: data driven subscriptions are only available in Enterprise edition.

    Dave.

    Tuesday, May 22, 2012 11:31 AM
  • Thursday, July 25, 2013 1:01 PM