Data Driven Subscription in SSRS
Here in this article we can learn something useful about Data driven subscription which was
available in SSRS(Sql Server Reporting Services).
Data Driven Subscription Means:
Build a query that retrieves subscription data, and map the result set to report and delivery options.
verify that SQL Server Agent service is running, before starting your process. If it is not running, you cannot save the subscription.
Steps to follow:
Start the Data-Driven Subscription Wizard
1. In Report Manager, click Home, and navigate to the Employee Sales Summary report.
2. Open the report.
3. Right-click Subscriptions, and select New Data Driven Subscription. If you do not see this button, you do not have Content Manager permissions.
4. Type a description for the subscription.
5. For Delivery method, select Report Server File Share as the delivery method, and then click Next.
1. Click A custom data source.
2. In Type, select Microsoft SQL Server as the data source type.
3. In Connection string, type the following connection string:
data source=localhost; initial catalog=Subscribers
4. Click Credentials stored securely on the report server.
5. In User Name and Password, type your domain user name and password. Include both the domain and user account when specifying User Name.
Important Credentials used to connect to a subscriber data source are not passed back to Management Studio. If you modify the subscription later, you must retype the password used to connect to the data source.
6. Select Use as windows credentials when connecting to the data source, and then click Next.
1. In the query pane, type the following query:
Select * from UserInfo
2. Click Validate, and then click Next.
1. For File name, in Setting Source, select Get the value from the database. In Setting Value, select LastName.
2. For Path, in Setting Source, select Specify a static value. In Setting Value, type the name of a public file share for which you have write permissions (for example, \\mycomputer\public\MySampleReports).
If you not sure how to create a public folder, see How to: Create a Shared Folder for Report Server File Share Delivery.
3. For Render Format, in Setting Source, select Get the value from the database. In Setting Value, select Format.
4. For Write mode, in Setting Source, select Specify a static value. In Setting Value, select AutoIncrement. Click Next.
5. For File Extension, in Setting Source, select Specify a static value. In Setting Value, select True.
6. For User name, in Setting Source, select Specify a static value. In Setting Value, type your domain user account. Enter it in this format: domain\account
Note: Include tags before and after domain and account.
7. For Password, in Setting Source, select Specify a static value. In Setting Value, type your password. Be sure that you type the password carefully. The wizard does not validate the password.
8. Click Next.
1. Use the default parameter values for Month and Year.
2. For Employee, select Get the value from the database. In Value, select EmployeeID. Click Next.
1. Click On a schedule created for this subscription, and then click Next.
2. In Schedule Details, click Once.
3. Specify a start time that is a few minutes ahead of the current time.
4. Click Finish.
When the subscription runs, three report files will be delivered to the file share you specified, one for each subscriber in the Subscribers data source.
Each delivery should be unique in terms of data (the data should be employee-specific), rendering format, and file format.
You can open each report from the shared folder to verify that each version is customized based on the subscription options you defined.
Thanks, good article.
Thanks For your comment