Data-driven Subscriptions in SSRS

Data-driven Subscriptions is a very powerful feature of Sql Server Reporting Services.

It 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.

In this article I will show you how to create Data-driven Subscriptions in SSRS.

1. For data driven subscription, you must require a separate data source. So we created one sample database named as Subscribers in our local system. Execute following script:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

USE Subscribers CREATE TABLE [dbo].[UserInfo] (     [SubscriptionID] [int] NOT NULL PRIMARY KEY ,     [EmployeeID] [int] ,     [LastName] [nvarchar] (50) NOT NULL ,     [FileType] [bit],     [Format] [nvarchar] (20) NOT NULL , ) ON [PRIMARY] GO INSERT INTO [dbo].[UserInfo] (SubscriptionID, EmployeeID, LastName, FileType, Format) VALUES ('1', '289', 'Bhushan', '1', 'IMAGE') INSERT INTO [dbo].[UserInfo] (SubscriptionID, EmployeeID, LastName, FileType, Format) VALUES ('2', '284', 'Jasmin', '1', 'MHTML') INSERT INTO [dbo].[UserInfo] (SubscriptionID, EmployeeID, LastName, FileType, Format) VALUES ('3', '275', 'Abhi', '1', 'PDF') GO SELECT * FROM [dbo].[UserInfo]

2. Now open Internet Explorer and go to Report Manager URL. Then click on your SSRS Project. Now click on down arrow on the report which you want to subscribe and select Manage. Then click on Subscriptions from the left pane. In that click on New Data-driven Subscription.

3. In that screen enter the description, Select Windows File Share for Specify how recipients are notified and select Specify for this subscriptions only. Then click on Next Button.

4. In next step, select Data source type and enter Connection String. Also give Credentials if required. Then click on Next Button.

5. In next step, specify a query which returns a list of recipients. You can also validate query by clicking on Validate button. Then click on Next Button.

6. In next step, provide subscription settings as shown in below screen shot :

7. In next step, provide report parameter values as shown below. Then click on Next button.

8. In next step, specify when the subscription is processed. select options as shown in below screen shot. Then click on Next Button.

9. In next step provide details of schedule as shown in below step. And then click on Finish Button.

10. So we have done with Data-driven Subscription in SSRS. You can also see the history of subscription as shown in below screen.

Congratulations! We successfully created Data-driven Subscriptions in SSRS.

Aim :- Learn how to create Report subscriptions in SSRS (a reporting tool).

Description :- I am sure our previous articles on SSRS helped you to learn the basics of SSRS. From now onwards, we will move to the Intermediate level. In this article, we are going to learn how to create Report subscriptions by using “SSRS Report Manager”. We will divide this into 2 parts – In 1st part, we will see How to create a user with admin access. Than we will add that user to SQL Server and will provide Server roles. In 2nd part, we will see How to subscribe Reports and various  subscription methods. Before going into the depth, we should know basics.

1. What do we mean by Subscription?
2. How many Types of Subscriptions are available in SSRS?

After learning the basics, we will discuss about the mandatory steps which needs to be followed to create Report Subscriptions in SSRS.

Que 1. What do we mean by the word Subscription?

Answer 1. According to MSDN :- A subscription is a standing request to deliver a report at a specific time or in response to an event, and in an application file format that you specify in the subscription. Subscriptions provide an alternative to running a report on demand. On-demand reporting requires that you actively select the report each time you want to view the report. In contrast, subscriptions can be used to schedule and then automate the delivery of a report.

Subscriptions are processed on the report server and are distributed through delivery extensions that are deployed on the server. By default, you can create subscriptions that send reports to a shared folder or to an e-mail address. If the report server is configured for SharePoint integrated mode, you can also send a report to a SharePoint library.

I hate long definitions, let’s apply KISS principle (Keep it Short and Simple). Subscriptions are used to deliver the report to the end user.

Que 2. What are the Different types of Subscriptions?

Answer 2. We have two types of subscriptions –

1. Push (Using email, shared folder).
2. Pull (using online).

The following are the subscription details, which we will follow at any time of Report creation.

• Which Report (Which report the end user wants)?
• Who needs the Report (Ex:-user)?
• When they need the Report (This information is used for scheduling purpose)?
• How they need the Report (It means in which format they want Ex: – pdf, excel etc.)?

Note :- In this article, I will run all the things with Admin Access. It means SQL Server must run this in Administrator mode.

1. Create a user in windows with Admin access. In our case I created one user with Admin access named as TEST.
2. Add above user (TEST) in SQL server and provide server roles (Means Admin access).
3. We must Create Shared Data source and Shared Dataset.

All these steps are explained below. Read below for more information.

Step 1 :- Create a user TEST with Admin access.

I hope you all know how to create user in Windows and how to give Admin permissions to that user.I am not going to create and show all those steps to you now.if any body don’t know how to create user and how to add admin permissions to that user in Windows just follow these steps.

• Create User name TEST, if u want to see how to create user in Windows 7 just follow this link.http://www.bleepingcomputer.com/tutorials/create-new-user-account-in-windows-vista-7/.
• After that just follow these Steps to give Admin permissions. Type USER name :- TEST ; Password : – test ; and Confirm password : – test
• Open User Accounts by clicking the Start button, clicking Control Panel, clicking User Accounts and Family Safety, clicking User Accounts, and then clicking manage another account.  If you’re prompted for an administrator password or confirmation, type the password or provide confirmation.
• Click the account you want to change, and then click Change the account type.We are having two options here one is standard and another one is Administrator.
• Select the account type as Administrator then click Change Account Type.

Step 2 :- Add user (TEST) in SQL SERVER and then provide Server roles.

Follow the below steps to Add a user and give Permissions to that user in SQL Server.

• Goto SQL Server management System (SSMS). Expand Security and Choose “New Login” option.

• Once you click on “New Login” the following wizard will appear. In this wizard, click on ‘Search’ button.

• Once you clicked on Search, the following wizard will apprear.

• In the above Wizard, Enter the Object name to select :- TEST.
• After entering username, click Check Names button. Finally it will appear like “AVINASHREDDY\TEST” as shown above. Click OK button.
• At the left side of the wizard we have few options just click on “Server roles”. Check “sysadmin”. If you have any doubt just follow below wizard. Finally click OK button.

• Again Go to Security in SSMS. Expand logins. Double click on AVINASHREDDY\TEST as shown below –

• Once you click on “AVINASHREDDY\TEST” user, the following wizard will appear.

• Click on “User mapping” option. Once you click on user mapping, the list of Databases will be displayed. Check one User defined database (In my case it is PHPRING).
• After checking user defined database, Go to Database role membership for :- PHPRING.
• Here check “db_owner option”.
• Follow the below snapshot if you are confused with theory.

• Repeat the same step to Report server Database. To do this, just check Report server db. Go to “Database role
  membership for: Report server db” as Check “db_owner”.
• Again repeat the same step to Report server Temp db. Just check Report server Temp db. Go to “Database role membership for :- Report server Temp db ” as check “db_owner”.
• Finally click on OK button.

 Summary :-

1. We learned what do we mean by Subscriptions.
2. Understood different types of Report Subscriptions in SSRS
3. We Created a User named TEST in windows .
4. We provided Admin permissions to that user.

This is the end of article . If anybody have doubt regarding this please drop them as comments. I hope you all enjoyed this 1st part. I like this reporting tool a lot as it gives me wide area for customization. In our 2nd part, we will see How to subscribe Reports and various  subscription methods. So stay tuned for 2nd and final part of Report Subscription in SSRS.

This article is further to my previous post on “Creating Report subscription in SSRS”. To see the 1st part of this article, Visit here – Report Subscription in SSRS – Part 1. In this post, i have shown how good is this reporting tool (SSRS).

Previously we created a user named TEST with Admin access and provided SQL Server roles to that user. In this article, we will start with creating an SSRS project. Then, we will create Shared Data source and Shared Dataset. After this, we will Deploy that report to the Report server. Once the Deployment is successfully completed, we will create Report subscription and schedule it as per the business requirement. After scheduling, we will start SQL Server agent to see the scheduled Reports being generated.

Step 1 :- Report Creation

• Open BIDS (Business Intelligence Development Studio). Then, Create a SSRS project.
• Once we create a New project then Report designer will appear. Under the Report project name we have two options – Shared Data source and Shared Dataset.
• To Create Shared Data source – Click on Shared datasource. Now, Right click and choose ‘Add New Datasource’. At the time of creating Shared datasource, give Server name as ‘localhost’ and Database name as ‘PHPRING’.
• To Create Shared Dataset – Click on Shared dataset. Right click and choose ‘Add new Dataset’. Then write the following query in that dataset :- Select * from emp. This “emp” table exists in our PHPRING database. I am just going to call it in Dataset.
• Once Shared Data source and Shared Dataset creation is over, Go to “Reports”. Right click on it and under Add, Choose “New Item”. Follow the below screen shot if you all have any doubts regarding Shared datasource and Shared dataset.

• Once you create a Report, go to Report data pane. Click on Data source. Add Datasource and then choose used shared Datasource reference. Choose Shared Datasource name (i.e. Datasource1).
• Now, To create a new Dataset. Click on new dataset. Choose use a Shared dataset option. Select “Dataset1″. This is our dataset name. Finally click OK button.
• Go to Data region. Choose Region as table to display our data. Then Add column fields in that table just like below Screen shot.

Step 2 :- Deploy report to Report Server in SSRS

• Once you are done with report creation, again open BIDS with Admin mode to deploy you reports to “Report server”. Firstly, Click on Report server project name. Then right click and choose Deploy option.
• Open IE (internet explorer in Admin mode) with following url (http://localhost/reports).

Note :- The above url is the Report manager url. We will get this at the time of installing Report configuration wizard. The IE appears like this –

• In the above wizard, click on Data Sources. Once you click on Data sources, following window will appear –

• In the above screen shot we can see “DataSource1″.  This is our shared datasource name. Simply click on this datasource1.The following window will appear –

• In the above window check “Credential stores securely in the report server”. After that give user name and password as – Username=test ; Password=test.
• Now, Click on Test connection and click Apply button.

Step 3 :- Report Subscription in SSRS

• Click on Home area in Report manager. Once we click on home the following window appear.

• In the above window just click on “Report project1″. Once you click on this, following window will appear.

• Select the Report SAMPLE (This is our report created in BIDS for this demonstration) and then click on Subscribe. Follow the below screen shot if you have any doubt.

• Once we click on “Subscribe”, the following window will appear on our screen.

• By observing above screenshot, we have only one option to choose in Delivered by section i.e. Choose Windows File Share.

Note :- If we have SMTP connection, one more option would have come into picture, but here we have only one. Because I do not have any “SMTP” connection right now. That’s why I “choose Windows File Share”.

• Once we choose “Windows file share “option, below window will appear.

• In the above screen shot we have different options we can go through each one of them now. Give file name =sample and For path name I can go to  my ‘E’ drive and create one folder name as ‘Subscription’.
• This is an empty folder. After this we can share this folder with TEST user. For doing this just right click on folder ‘Subscription’ and go to Share with and click specific people.

• Once we click on specific people the following wizard will appear. Just click on user TEST and click Add. Finally click on share button. The following message will appear.

• Just copy and paste the above path “\\AVINASHREDDY\subscription” at path place in subscription window.

• Provide other details as Render format = Excel; Give credentials of TEST user;  Username=test; Password=test.
• Click on select schedule. Once we click on schedule the following window will appear.

• In the above window, check Day as “MON” and Time as 6:30 PM. Now, click on “HOUR”. Once we click on hour the following wizard will appear. Give Run schedule every 2 minutes and start time 6:30 PM.

• Click OK button consequently two times. Once we complete all the settings, window will look like this –

• In the above window we can see all the settings. Follow these settings and click OK button.

Step 4 :- Executing Subscribed Report in SSRS

• The above schedule will run by SQL server agent .Make sure that this service is ON.
• Open SSMS and look whether SQL Server agent is in ON mode or OFF mode. If it is in OFF mode make it to ON mode.
• Follow the below screen shot, In my SSMS it is in off mode so am going to start now.

• Once we start the SQL server agent it looks green in color. Before we have seen RED mark but now we can see GREEN mark.
• Finally wait for schedule time. In our case it is 6:30 pm. This SQL server agent will run our subscription for every 2 minutes.
• Open our folder path and see the files generated by SQL server agent in the form of Excel.

• In the following picture we can clearly observe that we don’t have any files Before 6:30 PM. You can also see the time it displayed in the bottom of this screen shot,it shows 6:27 pm.
• At 6:30 pm, we will receive scheduled report –

• See after 2 minutes again –

• By seeing above screen shots, we can conclude that our subscription is working according to our schedule. In our schedule we gave 2 min time. So for every two min one file will be incremented automatically.This is the beauty of Subscriptions.
• After 18 Minutes, we will have in total 10 subscribed reports in our folder –

• Finally open up one excel file to see what data it contains actually.

Summary :-

• Part 1 : – Report Subscription in SSRS – Part 1
• Created SSRS project and then created shared datasource and shared dataset.
• Deployed that Report to Report server.
• Opened IE with admin access.
• Cretated Subscription and Scheduled the report with some time.
• Started SQL server agent.
• Finally saw the reports that are generated according to our scheduled time (for this Open shared folder path and see for every 2 min, we got one excel file).

This is the end of Report Subscription in SSRS article. This reporting tool (SSRS) provides many other features which we will post I hope you all enjoyed both the parts. If anybody still have doubt regarding this then please drop them as comments.

1.     Background

The purpose of this article is to provide a way of implementing Email subscription for a report using SQL server reporting services. Email Subscription of reports provides a way of delivering the reports in Email to individual users or groups in the required format and at a specific point of time.

2.     What is Subscription?

A subscription is a standing request to deliver a report at a specific time or in response to an event, and then to have that report presented in a way that you define. Subscriptions provide an alternative to running a report on demand. Subscriptions can be used to schedule and automate the delivery of a report.

3.     Delivery Extensions provided by SSRS

Delivery extensions are used to determine how to deliver a report and in which format. SQL Server Reporting services support two types of delivery extensions.

·         File Share Delivery : To deliver report to a folder

·         Email Delivery: To deliver a report to email recipient

4.     Pre-requisite for Email Subscription    

·         SQL Server Agent service should run.

·         Email address that is going to use as Sender address should have send mail permission on the SMTP server. Go to Reporting services configuration Manager è Email Settingsè Check whether correct Email Address and SMTP Server are mentioned or not:

·         In Reporting service configuration Manager, go to Execution Account tab and check whether unattended account mentioned is your domain account that is used for Report Data source connection, Report processing and sending emails:

·         Credential required connecting to Data source should store securely in the report server.

Below is the screenshot from report server showing that we are storing credential in the report server itself:

·         Run the report from Report Manager to ensure Report is working fine. Below is the screenshot of report preview showing report is working fine:

5.     Step by Step procedure to implement Email subscription:

                    I. Go to Report Manager URL e.g http://servername/reports  è go to folder which is having reports.

                  II. Below is the screenshot from my Report manager URL showing that there is a report named “ProductSalesReport” under MyReports folder.

                III.   Before creating subscription, run the report to ensure that Report is working fine.

                IV.   Go to “ProductSalesReport” report and click on down arrow and select Subscribe

It will open the Report subscription form. Below is the screenshot of Report Subscription form:

                  V. Fill the required details for Email Subscription and Click Ok.

                VI.  We are going for email subscription with below details:

              VII. Click Ok to create the subscription

            VIII.  Go to Report Manager home page and click on “My Subscription”. It will show the list 

              of  all the subscription created by you.

 

                IX. For Demo purpose, I have schedule the subscription per minutes. We can set the subscription as per the business requirement.

                  X. Click on “My Subscription” to check the status of the subscription

 

                XI. The above screenshot is showing Report is delivered to vishaljharwade@sqlcircuit.com

              XII. Open the mail and check whether report is delivered to mail or not.

6.     Conclusion

By using above steps, we can create email subscription for a report and we can deliver the report to an individual as well as group at a specific point of time in a required format.

###########################################################################

Email Subscriptions for SSRS Reports

Posted on MARCH 21, 2013 by BHUSHAN SHAH

Email Subscriptions for SSRS Reports

In this article, I will show you how to create Email Subscriptions for SSRS Reports. Before going for this please refer following article on Email Configuration in SSRS with SMTP in Windows Server 2008R2:

http://bhushan.extreme-advice.com/email-configuration-in-ssrs-with-smtp-in-windows-server-2008r2/ 

After doing Email Configuration, Now open Internet Explorer and enter your report manager URL.

In my case it is like below:

http://localhost/Reports

1. Now click on your Report Project as show in below screen shot:

2. After that click on Down Arrow which is on the right side of Report and SelectManage.

3. Now select Subscriptions from left pane and then click on New Subscription.

4. Now select E-Mail in Delivered by list. Then enter e-mail address in To, Cc & Bcc as par your requirement. Also select Render Format as shown in below screen shot. After that click on Select Schedule button.

5. In next screen, select Schedule details as par your requirement. Here I select following details. Then click on OK button.

Again click on OK button.

6. Now your subscriptions screen looks like following screenshot.

7. Now after your subscription executed, you will see its details as shown in following screenshot.

Congratulations! We successfully completed Email Subscriptions for SSRS Reports.

If any query than let me know.

For contact information, go to following link :

http://bhushan.extreme-advice.com/about-bhushan-shah/

----------------------------------------------------End of Document---------------------------------------------------

A subscription is a standing request to deliver a report at a specific time or in response to an event, and in an application file format that you specify in the subscription. Subscriptions provide an alternative to running a report on demand. On-demand reporting requires that you actively select the report each time you want to view the report. In contrast, subscriptions can be used to schedule and then automate the delivery of a report.

In this article, I will show you how to use Report Subscription(Windows File Share Delivery) in SSRS.

1. First of all open Internet Explorer and go to Report Manager URL which is something  like below:

http://bhushan-pc/Reports2012

Your internet explorer tab looks like below :

2. Click on your SSRS project. In my case it is Start SSRS. So now it will show you list of reports which are deployed on your report server.

3. Now click on down arrow on the report which you want to subscribe and selectManage as shown in below screen shot.

4. Then select Subscriptions from left pane and Click on New Subscription as shown in below screen shot.

5. In next screen, select Windows File Share in list of Delivered By.

6. Then specify Network Path on which you want to save your report. In my case it is \\BHUSHAN-PC\Bhushan Reports.

Also specify render format in which you want your report and give credentials to access that specific folder. Here we select PDF.

7. Then click on Select Schedule to give specific time to deliver your report. In below screen you will see that options. After that click on OK.

8. You can also specify parameter’s value if any parameter is used in your report.

Now click on OK button. So we have done with Our Subscription.

9. You will see the history of your subscriptions under the tab of Subscriptions.

10. After your subscription is executed, you will find that report in Network Path which you specify at the time of Subscription Creation.

Congratulations! We successfully completed use of Report Subscription(Windows File Share Delivery) in SSRS.

1.     Background

The purpose of this article is to describe how to create a file share subscription for a report using SQL Server Reporting services. By using File share subscription, we can deliver a report to a folder at a specific point of time in a required format.       

2.     What is Subscription

A subscription is a standing request to deliver a report at a specific time or in response to an event, and then to have that report presented in a way that you define. Subscriptions provide an alternative to running a report on demand. Subscriptions can be used to schedule and automate the delivery of a report.

3.     Delivery Extensions provided by SSRS

Delivery extensions are used to determine how to deliver a report and in which format. SQL Server Reporting services support two types of delivery extensions.

·     File Share Delivery : To deliver report to a folder

·     Email Delivery : To deliver a report  to email recipient

4.     Pre-requisite for File Share Subscription

· SQL Server Agent Service should run.

· Service account that is using for file share subscription should have write access to shared folder.

· Users who will use these reports must have read only access to the shared folder.

5.   Step by Step procedure to implement file share subscription:

                      i.      Go to Report Manager URL e.g  (http://vishal-pc/reports ). Below is the screenshot of Report Manager which is showing Data Source, Dataset and Report folder.

                    ii.            Go to Report folder e.g “MyReport” and see what the reports available are. We can see one report named “EmployeeDetailReport”

                     iii.            Run the report to ensure that report is working fine.

Report is working fine so we can go ahead for subscription.

                        iv.  For File Share Subscription, go to Report Manager URLè Report Folderè Select the Report that need to subscribeè click on drop buttonèSelect Subscription

                         v.  It will open the Subscription page for the report. Select the Delivery By as “Windows File Share”.

                        vi. Fill the information for below mentioned fields required for subscription.

·         Path : Shared folder where reports need to deliver

·         Render format: Select the format in which report needs to be delivered. Below are the formats in which we can render a report:

·         Credentials used to access the file share: Pass the UserName and password of a user who will access the reports.

·         Based on requirement, we can select overwrite option

·         Subscription Processing option: for Selecting the schedule for subscription.

                        vii.   We are using the below settings for the subscription:

                      viii. Click ok to create subscription.

                      ix.  To Check whether subscription is created or not, go to Report Manager home page and click on “My Subscription”.

                          x. Subscription page will show the newly created subscription:

                         xi. We have created subscription to run every minute for demo purpose only. We can schedule the report as per requirement. So let’s see whether subscription worked successfully or not. To check whether a subscription working successfully or not, again click on “My Subscription”. It will show the subscription execution details.

Subscription status: Report is delivered in Pdf format in shared folder.

                       xii. Go to shared folder and check whether report is delivered or not

                         xiii. Open the pdf file and check whether report delivered properly or not.

  

6.     Conclusion

By using file share subscription, we can deliver SSRS report in folder at a specific point of time in a required format.

----------------------------------------------------End of Document---------------------------------------------------