Report subscriptions are processed on the Report Server. They reach end users through delivery extensions that are deployed on the Report Server.
There are two kinds of subscriptions:
• Standard /Event-Driven Subscriptions
• Data-Driven Subscriptions
Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing.
Data-driven subscriptions are dynamic in that the presentation, delivery, and parameter values are retrieved at run time from a data source. You might use data-driven subscriptions if you have a very large recipient list or if you want to vary report output for each recipient. To use data-driven subscriptions, you must have expertise in building queries and an understanding of how parameters are used. Report server administrators typically create and manage these subscriptions
Subscriptions use delivery extensions to determine how to distribute a report and in what format. When a user creates a subscription, he or she can choose one of the available delivery extensions to determine how the report is delivered. Reporting Services includes support for e-mail delivery and delivery to a file share. Developers can create additional delivery extensions to route reports to other locations. Another delivery method is called the null delivery provider. This method is not available to users. Null delivery is used by administrators to improve report server performance by preloading the cache. Subscription information is stored with individual reports in a report server database. You cannot manage subscriptions separately from the report to which they are associated.
E-Mail Delivery in Reporting Services:
Reporting Services includes an e-mail delivery extension that provides a way to e-mail a report to individual users or groups. The e-mail delivery extension is configured through the Reporting Services Configuration Manager tool and by editing the Reporting Services configuration files.
Select the Email Settings--> Sender Address, Current SMTP Delivery Method and SMTP Server.
To distribute or receive a report by e-mail, you define either a standard subscription or a data-driven subscription.
Note: You can subscribe to or distribute only one report at a time. You cannot create a subscription that delivers multiple reports in a single e-mail message. You click the “New Subscription” button, ready to go add an email address.
An E-Mail will be generated and sent to the email addresses found in the recipients’ field.
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.
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.
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.
• 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.
Schedule the subscription by clicking Schedule Details: Here you can select hourly, daily, weekly, monthly and many more options. Ok and here now your subscription is created. Wait for the particular time which you schedule.
Another place where you can see the subscription delivery is in the Microsoft SQL Server Management Studio under SQL Server Agent under Jobs Activity monitor: Connect to SQL Server --> SQL Server Agent -->Job Activity
Windows File Share Delivery in Reporting Services:
Reporting Services includes a file share delivery extension so that you can deliver a report to a folder. The file share delivery extension is available by default and requires no additional configuration. To distribute a report to a file share, you define either a standard subscription or a data-driven subscription. You can subscribe to and request delivery for only one report at a time. When defining a subscription that uses file share delivery, you must specify an existing folder as the target folder. The report server does not create folders on the file system. The folder that you specify must be accessible over a network connection. When specifying the target folder in a subscription, use Uniform Naming Convention (UNC) format that includes the computer's network name. Do not include trailing backslashes in the folder path. The example illustrates a UNC path: \
File Name: – This is the name the file will be saved as. Just as a note, you can’t use the same parameters that you can with email. I’ve tested it, and they just appear as @ReportName and @TimeExecuted in the file name. You can, however, use the parameter @timestamp. This will add the time the report was saved to the file name. This is useful for creating uniquely named files.
Add a file extension when the file is created – If this is selected, the file will be assigned a 3-letter extension corresponding to the file format you select. If this is not selected, the 3-letter extension isn’t assigned to the file, although it will still open in the selected format.
Path: – The location on the network the file will be saved to. The folder must already exist – Reporting Services won’t create it. Also, you must use the UNC format: file://servername/foldername/subfolder.
Render Format: – The file format the report will be saved in. The formats you can pick from vary based on your version of Reporting Services.
• XML file with report data – the report is saved as an .xml file.
• CSV (comma delimited) – the report is saved as a .csv file.
• Acrobat (PDF) file – the report is saved as a .pdf file.
• HTML 4.0 – the report is saved as an .html file. Note: if there are images in your report, they will not show in this format.
• MHTML (web archive) – the report is saved as a .mhtml file.
• Excel – the report is saved as an .xls file.
• RPL Renderer – the report is saved as an .rpl file. What is .rpl? RPL (Report Page Layout Format) is a rending format used in newer Reporting Services version for forcing the client to share some of the workload of rendering viewer controls. This enhances performance in the loading process of reports
• TIFF file – the report is saved as a .tif file.
• Word – the report is saved as a .doc file.
Credentials used to access the file share: – This must be a network account, with access to the folder you are saving the file to.
Overwrite options: – These are options for what will happen if the report has previously been saved to the network location.
• Overwrite an existing file with a newer version – Just what it says! If a file with the same name and extension exists in that folder, it will be overwritten. If I save “Sales Report.xls” at 10:00 AM on Monday, then save “Sales Report.xls” at 10:00 AM on Tuesday, only the file from Tuesday will exist.
• Do not overwrite the file if a previous version exists – Again, this is just what it says! If a file with the same name and extension exists in that folder, the file will not save. If you look at the report subscription Status, you will see the message, “A file named “ReportName” already exists in the destination folder. To continue, do one of the following: remove the existing file, specify a different name, or set overwrite options to allow the replacement of the existing file with a newer”.
• Increment file names as newer versions are added – The first time the file is saved, it is as “Report Name.doc”. The next time, it is “Report Name_1.doc”, then “Report Name_2.doc”, etc. Another way to achieve the same effect is to use the @timestamp parameter in the File Name, and set the Overwrite option to “Do not overwrite…”
Schedule the subscription by clicking Schedule Details: Here you can select hourly, daily, weekly, monthly and many more options.
After the scheduled time expires verify the status as you did in the email delivery example. Also go and verify that the report has arrived in the shared folder.
CREATING A DATA-DRIVEN SUBSCRIPTION:
A data-driven subscription provides a way to use dynamic subscription data that is retrieved from an external data source at run time. A data-driven subscription can also use static text and default values that you specify when the subscription is defined. You can use data-driven subscriptions to do the following:
• Distribute a report to a fluctuating list of subscribers. For example, you can use data-driven subscriptions to distribute a report throughout a large organization where subscribers vary from one month to the next, or use other criteria that determines group membership from an existing set of users.
• Filter the report output using report parameter values that are retrieved at run time.
• Vary report output formats and delivery options for each report delivery.
Data-driven subscriptions send out reports to not just one individual or two but to a large number of recipients whose desired format (among other details) are maintained in a database on the server. This is report delivery targeted at mass distribution. In order to work with this hands-on exercise you need to have a database of recipients ready. When the subscription is processed, the Report Server customizes the output for each of the recipients maintained on the database.
Note: To use Data Driven Subscriptions, you must have Evaluation, Developer or Enterprise version of SQL Server. This feature is not supported in Standard or Express.
Create a Subscription database in SQL Server 2008
Before you can send out the email to a number of recipients you need to have information about their email addresses in the correct format in your SQL Server database which stores subscriber information
Connect to SQL Server Management StudioCreate New database Subscribers
CREATE TABLE [dbo].[RecipientInfo]
(
[SubscriptionID] [int] NOT NULL PRIMARY KEY,
[RecipientID] [int] ,
[Email] [nvarchar] (50) NOT NULL,
[FileType] [bit],
[Format] [nvarchar] (20) NOT NULL ,
)
INSERT INTO [dbo].[RecipientInfo] (SubscriptionID, RecipientID,
Email, FileType, Format) VALUES ('101', '289', ' EmailAddress.com', '1', 'Excel')
INSERT INTO [dbo].[RecipientInfo] (SubscriptionID, RecipientID, Email, FileType, Format) VALUES ('201', '284', ' EmailAddress.com','2', 'Word')
INSERT INTO [dbo].[RecipientInfo] (SubscriptionID, RecipientID, Email, FileType, Format) VALUES ('301', '275', EmailAddress.com','3', 'PDF')
Creating the data-driven subscription by email
Open a report and go to the Subscriptions tab. Click “New Data-driven Subscription”.
Step 1
The first step is to establish a description, delivery method and data source for this subscription.
Description – provide a name, which you will see on the Subscriptions tab for the report. I usually put in something that tells me how often this subscription is run and who or where is it being delivered to.
Specify how recipients are notified – This will determine how the report is delivered.
E-mail – The report will be delivered via email.
Specify a data source that contains recipient information:
Specify a shared data source – choose a data source that exists in the Reporting Services installation. I always use this option, as it makes administration much easier.
Specify for this subscription only – you can set up a data source for this item only.
Step 2
If you chose to use a shared data source, you will see this screen, where you can navigate your folders to find your data source.
Note: - If you get an error saying could not valid then go back and uncheck the use windows credential box or check your connection and credentials.
Step 4
In this step, you will specify your email delivery information.
• To – who the email will be delivered to. Set a static email, or choose a value from your query.
• Cc – who the email will be cc’ed to. Set a static email, or choose a value from your query.
• Bcc – who the email will be bcc’ed to. Set a static email, or choose a value from your query.
• Reply to – by default, replies will go the account the reports are sent from. You can set a different email address here. Set a static email, or choose a value from your query.
• Include Report – you can set a static or dynamic option of True or False.
• Render Format – you can set a static option, get the value from your query, or choose no format.
• Priority – this sets the priority in Outlook. You can set a static option, get the value from your query, or choose no value.
• Subject – the subject line of the email. You can set a static subject, or get the value from your query.
• Comment – this will appear in the body of the email. You can set a static option, get the value from your query, or choose no value.
• Include link – determines if you want to include a link to the report in the body of the email. You can set a static option, get the value from your query, or choose no value.
There are so many creative ways to use this! Perhaps you want the report to have a Priority of High if sales are above or below a certain dollar value. The Subject could be dependent on any field in the query. Perhaps you don’t include the report and the link if certain criteria are not met.
Step 5
If your report includes parameters, you will set them here.
Step 6:
The final step is to define when the subscription will be processed.
• When the report data is updated on the report server – the subscription will be processed when the snapshot is refreshed, if the report is set up to use a snapshot.
• On a schedule created for this subscription – create a schedule for this one report subscription.
• On a shared schedule – use a pre-defined schedule, created in Site Settings.
Your data Driven Subscription got created and waits for to completion of the schedule and see the results that it should send a mail to all recipient which we stored in database.
Null Delivery Provider – The What and Why
A feature of Reporting Services that can be used to improve performance is report caching. To sum up what could be a future post of its own: caching will save a copy of the report in the ReportServerTempDB, which reduces the time it takes for a report to render for a user. This can be very useful if you have a report that takes a long time to generate, and is run frequently with no parameters or the same parameters.
When you set up a report to use caching, the cache is created the first time a user runs it. You set the cache to expire after a certain amount of time. So, say you have a report that takes 5 minutes to generate, and 10 users will run it every day, with the first one coming in at 7:00 AM. You set the report to cache a temporary copy, and expire it every day at 7:00 PM. The first user would come in and run it, and it would take 5 minutes. Every other user that runs it between the time of the first user and the expiration would notice a significant decrease in rendering time.
But, how can you help the users by reducing the time it takes the first user to run it in the morning?
The answer is to set up a report subscription using the Null Delivery Provider. This saves a copy of the report in cache at a time you specify – in this example, perhaps 6:00 AM. When the first user comes in to run the report, the rendering time is reduced.
Data-driven Subscriptions by Null Delivery Provider
Open a report and go to the Subscriptions tab. Click “New Data-driven Subscription”.
Step 1
The first step is to establish a description, delivery method and data source for this subscription.
Description – provide a name, which you will see on the Subscriptions tab for the report. I usually put in something that tells me how often this subscription is run and who or where is it being delivered to.
Specify how recipients are notified – This will determine how the report is delivered.
Choose Null Delivery Provider
Specify a data source that contains recipient information:
Specify a shared data source – choose a data source that exists in the Reporting Services installation. I always use this option, as it makes administration much easier.
Specify for this subscription only – you can set up a data source for this item only.
Step 2
If you chose to use a shared data source, you will see this screen, where you can navigate your folders to find your data source. I usually store mine in the “Data Sources” folder – that makes them easy to find!
If you choose to specify a data source for this subscription only, you will see this screen. Enter a connection string and credentials for your data source.
Step 3
In this step, you define the query that can return parameter values for you.
When using caching for a report, be aware that it works best when you have no parameters, or very few choices. Every distinct combination of parameters will create a different version of the report in cache.
I am going to set my start date, end date, and folder parameters for this report in this step.
You have the option to specify a time-out, in seconds.
You can click the Validate button to ensure the query will run against your data source.
Step 4
There are no delivery settings for Null Delivery Provider. The report is always “delivered” to ReportServerTempDB.
Step 5
If your report includes parameters, you will set them here. You can set a static value for each, or, as with the case below, choose a value from the query.
Step 6
The final step is to define when the subscription will be processed.
When the report data is updated on the report server – the subscription will be processed when the snapshot is refreshed, if the report is set up to use a snapshot.
On a schedule created for this subscription – create a schedule for this one report subscription.
On a shared schedule – use a pre-defined schedule, created in Site Settings.
One thing to keep in mind with Null Delivery is that the copy of the report in cache must be expired before you create a new copy using this method. I would suggest setting up the report cache to expire on a specific schedule, shortly before this subscription runs.
This explanation of Null Delivery Provider is just one piece of report caching. Caching can be a way to increase performance for your users, if used correctly. Consider using both caching and data-driven subscriptions to improve your environment.
