In our last post, we showed you how to create drill down SSRS reports so you can create incredibly dynamic reports. Drill down reports create dashboard-like functions right within SSRS. For those credit unions trying to maximize their BI investment, initially using SSRS over third-party BI visualization tools can minimize your upfront costs.
What is an SSRS Subscription?
This post will introduce you to creating SSRS subscriptions. But, what is an SSRS subscription? Think of any other subscription you have in your life to a blog, newsletter, or magazine. Your subscription allows you to receive something that you want at a specified time. SSRS subscriptions are very similar; you can subscribe to a particular report and have it delivered to you at a specified time and location.
SSRS allows you to receive reports in a few different ways. The most common delivery methods are to have the report sent to a windows file share or to your email. If you have Microsoft SharePoint in your organization, you can have SSRS reports delivered directly to SharePoint as well. In this particular post, we will show you to how to create a windows file share subscription for a single report. There are some very inventive ways to use SSRS subscriptions beyond what we will discuss in this post. Head over to Microsoft’s SSRS Subscription and Delivery page to learn more if you are interested!
Why use SSRS Subscriptions?
Suppose we have a key weekly lending report that is currently being created the old-fashioned way: manually through Excel. The person creating that report in Excel will complete the report and send it to three or four people. Those three or four people save the copy locally to their drive thus creating multiple copies of the same document. Along with having multiple copies of the same document stored on the shared drive, we are also using unnecessary space on the mail server. IT network and server staff deal with this dilemma regularly.
With SSRS, you could create that report and have the data dynamically retrieved from a SQL database like we have done in past examples. Then, we create an SSRS subscription that places the report output in a specified location at 7 AM every Monday morning. Now, those three or four individuals who used to receive the report in their inbox can simply head to the designated location to view the report. They never have to ask for the report and the person who used to develop the report never has to manually produce and send it again.
Are you that person stuck developing a report manually and then sending it out to various people who request it? Then this post is for you!
Setting up the Report Security
To create subscriptions, we need to first navigate to the report browser. This may be the first time you have done this. To do so, ask your IT/BI area for the SSRS URL. It typically has the form: http://[server name]/Reports. This is the URL that you use to view reports and manage subscriptions. Once you are there, you may need to upload the reports that we created if you haven’t deployed them already.
First, create a new folder on the home page (if you have permission to do so) named “Credit Union Reports”.
Once the folder is created, click on it to be brought to the page for the “Credit Union Reports” folder. In the SSRS menu, there is an option title “Upload File”. Click that button, browse to where you saved your SSRS reports from previous posts, and select the .rdl files for each report. I will be uploading two reports: “Share Account by Branch” and “Share Accounts Opened”.
If you have done this correctly, the “Credit Union Reports” page of SSRS in your browser will look like the image below.
If you click on any of the reports, you should be able to view the output of those reports. This is similar to clicking the “Preview” button for a report in SSDT or BIDS.
Hover over the report named “Share Accounts by Branch”. You should see a dropdown arrow to the right of the report name. Click this arrow and choose “Manage”.
When the browser changes, select the “Data Sources” from the left side of the screen. If the radio button next to “A custom data source” is not selected, select it now. Under “Connect Using:” select the radio button next to “Credentials stored securely in the report server”. Subscriptions require that credentials be stored securely in the report properties. It is recommended that you have a dedicated username with the proper privileges – something like “Data_Warehouse_Reports”. That way, you don’t have to use your own personal credentials in the report data source along with having some other benefits.
Once you choose the radio button next to “Credentials stored securely in the report server”, enter either your personal credentials or the credentials for your dedicated report/data warehouse account (this is more of an IT thing so you may or may not have one). Click the check box next to “Use as Windows credentials when connected to the data source” (this assumes you are using Windows credentials through active directory – which is most common – instead of a SQL Authentication account created for the SQL instance).
Press “Test Connection” towards the bottom of the screen to ensure that all properties and credentials have been entered correctly and that you have the necessary privileges. If everything was entered properly, green text under the “Test Connection” button will appear saying “Connection created successfully”.
Press “Apply” to save your changes.
Using the breadcrumb trail at the top of the browser page, head back to the “Credit Union Reports” page.
Creating the SSRS Subscription
Now that we have stored the credentials in the report, we can now create the subscription. From the “Credit Union Reports” page of the report server, hover over the “Share Accounts by Branch” report. When the dropdown arrow appears, click on it and then choose “Subscribe”. The subscription delivery options page should appear:
This screen allows you to specify how, where, and when you would like the report delivered. In the “Delivered by:” dropdown, choose “Windows File Share”. This method will allow us to send the report to a location on our network. When you choose “Windows File Share” from the menu, the screen should change significantly:
We can leave the “File Name” text box with the default value of the report’s name. The “Path” text box is where we specify the location that we would like the report to be placed. There is an important subtlety to note here: the path must be in UNC (Universal Naming Convention) form. If we wanted to place the report on our C drive, we couldn’t use the path “C:\”. Instead, we would have to type it with the form “\\[Computer or Server Name]\c$”
To keep things simple, let’s place this report on our C drive. In the “Path” text box, type “\\[Computer or Server Name]\c$” where the [Computer or Server Name] is replaced with the name of your computer or server you are working on. For example, if I wanted to place this file on a computer named “Brewster”, I would use the path “\\Brewster\c$”.
In the “Render Format:” dropdown, choose “Excel”. Before we enter credentials, click on “Select Schedule” towards the bottom of the screen.
This screen allows us to specify when we would like this report to run. We can be very specific with the scheduling process which gives us some nice, flexible options. We want to set this report to run in about five or ten minutes from whenever you are completing the subscription. So, if it is 2pm on a Wednesday when you are following along with this post, be sure to select the checkbox next to “Wed” and set the “Start Time” to about 2:05 pm.
Press “OK” once you have this set up. Again, we want this to run within the next few minutes (if you are following on), so be sure you have your times set up correctly.
Now, you should be brought back to the “Subscription” screen. Go ahead and enter your credentials in the “User Name:” and “Password:” text boxes. I am writing this post at approximately 1:20 pm, so I have scheduled the subscription to execute at 1:25 pm.
With everything completed as you need, press “OK”. As long as there were no errors immediately flagged by the report server, your SSRS subscription should be scheduled. You should be returned to the “Credit Union Reports” page in SSRS.
Now, wait the few minutes left until the subscription should be executed. In my case, this means waiting until 1:25 pm. While you wait, you can click the “My Subscriptions” hyperlink at the top of the page.
When you click that hyperlink, you will be brought to a screen that contains all of your subscription details. Since this is the first subscription we have set up, you should see just a single row with the “Status” field set to “New Subscription”.
Once the subscription executes, this screen will be updated with the results of the last subscription execution. I have waited a few minutes, so it is past 1:25 pm which is when I had the subscription scheduled to execute. I am going to refresh the “My Subscriptions” page.
If you look at the “Status” column now, you will see that the value has changed to reflect the status of the last execution. Since this subscription executed without issue, it tells me that “The file ‘Share Accounts by Branch.xlsx’ has been saved to the “\\XXXXX\c$” file share.”.
To be sure, let’s navigate to the C drive where we told SSRS to place the report.
We can see that the “Share Accounts by Branch” report has been placed to the C drive in an Excel format just like we expected!
SSRS subscriptions can be incredibly helpful when we need to automate the delivery of reports that we have created. One of the key components of business intelligence is automating and improving the efficiency of our reporting operations. By taking advantage of the inherent features of SSRS, we can drastically reduce the manual effort involved in completing and submitting reports throughout our credit unions.
