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.
- What do we mean by Subscription?
- 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 –
- Push (Using email, shared folder).
- 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.
- Create a user in windows with Admin access. In our case I created one user with Admin access named as TEST.
- Add above user (TEST) in SQL server and provide server roles (Means Admin access).
- 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.
- We learned what do we mean by Subscriptions.
- Understood different types of Report Subscriptions in SSRS
- We Created a User named TEST in windows .
- 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.