You have discovered that you don’t have to run a SQL Server Reporting Services report every time you want the information – you can set up a subscription to receive it automatically! It’s a great feature of Reporting Services. So here you are, at the report screen. You click the “New Subscription” button, ready to go add an email address, pick the Excel format, and send it off at 8:00 AM every Monday.
You click the “New Subscription” button and are looking at the following screen:
Don’t stop here because there are so many things you can do with subscriptions! Let me show you a few tricks I’ve learned over the years.
Report Delivery Options – E-Mail
The first option on the screen is “Delivered by:”, and you have two choices, E-Mail or Windows File Share.
We’re going to focus on the E-mail delivery in this post. An E-Mail will be generated and sent to the email addresses found in the recipients’ field. You will work with the screen you see above. I’ll cover Windows File Share in my next post.
To:, Cc: and Bcc: – fairly standard fields. You can enter the alias if you’re sending to an internal address, and must type the full email address if sending to an external address. More information on that can be found at http://msdn.microsoft.com/en-us/library/ms160334.aspx.
Reply-To: – The email will be sent from the address specified in SQL Server Reporting Services Configuration Manager on the server. However, if you would like replies to be redirected, simply enter a different address. I have a generic email , reports@company.com, that all reports are sent from. In some cases, it’s OK if users reply to that – I monitor the mailbox. However, for external salespeople, I want them to reply to their customer service rep, so I set up that as a reply-to address. It saves me the trouble of having to forward emails every day.
Subject: – what will appear in the subject line of the email. This can be edited to anything you would like. The two parameters provided, @ReportName and @ExecutionTime, are the only parameters that are allowed in this field.
Include Report – This option, if checked, will attach the report in the format you specify. Consider the size of the report, and the format you want to send it in, when choosing this option. If you’re going to be sending a large report, consider mail size limits on the recipient’s mail server. You may need to send a link, instead.
Render Format – If you have chosen to include the report, you can pick a format here. The formats you can pick from vary based on your version of Reporting Services. This is the list of formats available in 2008.
- XML file with report data – the user receives an .xml file as an attachment.
- CSV (comma delimited) – the user receives a .csv file as an attachment.
- Acrobat (PDF) file – the user receives a .pdf file as an attachment.
- MHTML (web archive) – the user receives the report embedded in the body of the email, rather than as an attachment. (I was really excited when I discovered this!)
- Excel – the user receives an .xls file as an attachment.
- TIFF file – the user receives a .tiff file as an attachment.
- Word – the user receives a .doc file as an attachment. This is new in 2008.
Include Link – This option, if checked, will embed a link to the report in the body of the email. Note that if you do this, the report parameters will be embedded in the link.
Priority: – This sets a priority of High, Normal or Low for Outlook. High is the little red exclamation point, and Low is the little blue down arrow.
Comment: – This field controls what will be in the body of the email. Comments will appear above the report, if you selected MHTML, and above the link, if you chose to include it. One of the most exciting things about this, which I’ve found a lot of people don’t know, is that you can format the contents of this with HTML! My blog post “Formatting Comments of a Reporting Services Subscription” goes into more detail about that.
I hope you’ve learned something new!
Read on to learn about Windows File Share delivery, and Processing Options and Parameters.
Great post, Jes! Good source for my people @ work…
Very important and useful information!! thank you!!
how to define “To:, Cc: and Bcc:” dinamically? example … from my table employee.Email
What defines whether it will be a To, a CC or a BCC? Figure that out, then perhaps a CASE statement?
SELECT CASE WHEN Dept = ‘HR’ THEN Employee.Email ELSE ” AS To,
CASE WHEN Dept = ‘Acctg’ THEN Employee.Email ELSE ” as CC…
How do you reach the “report screen”? I am using report services and I have never seen this screen before. Is it from a website when you deploy your report???
Yes, you would see these screens after you install Reporting Services on a server, build a report, and deploy it.
Thanks for your response. I have managed to deploy a report and create a subscription. I can view the report and run it. But when I schedule it the error is “Failure sending mail: The transport failed to connect to the server.”
You need to configure the email settings on Reporting Service Configuration Manager. Sender Address, Current SMTP Delivery Method, and SMTP Server. http://msdn.microsoft.com/en-us/library/ms189342.aspx Then you will be good to go.
i did the same thing for sending email through subscription in ssrs2005 but icouldnt able to send and im getting following error. Please help me about the issue.
“Failure sending mail: The message could not be sent to the SMTP server. The transport error code was 0x800ccc15. The server response was not available”
That’s an error with your SMTP server, or another external process. It’s not SQL Server related. You’ll need to check with a system/Exchange/network admin.
keren banget sobb.. gak ada duanya deh