2

I have a report that displays data for a specified date. The report runs daily and emails a copy to the recipients. The default subject for the email is "@ReportName was executed at @ExecutionTime." Rather than include the execution date, I want to include the date of the report data, which is available in a parameter called @date.

How can I include a report parameter's value in the subject line of the subscription email?

Michael Venable
  • 5,011
  • 3
  • 23
  • 20

4 Answers4

3

I'd recommend looking into data-driven subscriptions. Basically they allow you to write a query to set your parameter value(s) and define additional columns that can be used for subject line, message body, etc. Here's one helpful resource:

Also, I should point out that data-driven subscriptions are only available on certain versions of SQL Server (Evaluation, Developer, Enterprise).

stubaker
  • 1,928
  • 1
  • 23
  • 25
  • Thank you for your suggestion. I've read over a lot of documentation on data-driven subscriptions, and while they let you build the subject line at run-time, it doesn't appear they let you use report parameters in the email. Thanks for your help. I've given you an upvote for your time. – Michael Venable May 28 '14 at 00:33
  • 1
    It's true that you can't pull internal parameters out of a report, but you can put your logic for default values in the DDS and set both your parameter and subject there. Not sure how your @date parameter is being set (so this may or may not be an option in this case) – stubaker May 30 '14 at 23:37
  • That is how I ended up writing it -- putting the logic for the default parameters in the DDS to be used in the subject line. The logic isn't complicated, but I am worried that, in the future, we might change the logic and not realize it must be changed in two places. In any case, it helps to have the opinion of someone more experienced with SSRS state that this is the usual way of solving this problem. Thank you for helping out. – Michael Venable May 31 '14 at 02:27
  • 1
    @MichaelVenable to address your concerns about the default date (or any parameter default) logic changing in the future, you could create a database object that returns you default(s) for your report, and then use that database object in both your SSRS report and data-driven subscription to get the default date for the report. That way if it is updated in one place, it will automatically update your report and subscription. – BateTech Aug 13 '14 at 20:53
  • I found this answer however the first link is dead. Suggest to add content to the answer directly or consider removing as it is no longer complete. – Ryan Peters Sep 16 '16 at 12:34
1

Including the values of report parameters in the subject line of an email subscription does not appear to be possible with SSRS. Data-driven subscriptions allow you to build the subject line dynamically at run-time, but it does not let you query and insert the parameters into the subject line. From what I can tell, this is just not possible.

Michael Venable
  • 5,011
  • 3
  • 23
  • 20
0

It should be possible by not using subscriptions, but e.g. from SSIS to * render the SSRS report and save e.g. as html file, * create an e-mail and put the file from the report either into the body or as attachment, * and set the e-mail subject. This bears again the challenge to read out a value or parameter from the rendered report, where I presently don't have an answer. But here you have all means including .net available, so should be possible.

Mike

Mike
  • 57
  • 7
0

The parameter values are found in the Parameters field of the dbo.Subscriptions table in the ReportServer database. If you update the ExtensionSettings field's subject section with values from the Parameters field, you can achieve this without purchasing the enterprise license. Just remember that ExtensionSettings is ntext so you will need to CAST to NVARCHAR(MAX) to do your REPLACE() statement.

Kungfu
  • 1