What makes SQL Server 2016 one of my favorite SQL Server release since the release of SQL Server 2012 is the increased number of exciting new features that have been introduced. Whilst my article Top 5 New SQL Server 2016 DMVs for DBAs reviewed some of these new features albeit from a DBA point of view, in this article I continue to review SQL Server 2016 but from the perspective of an SSRS administrator in relation to the changes in report subscriptions.
File Share Account
One of the first changes you will notice in SQL Server Reporting Services (SSRS) 2016 is the new Subscription Settings property that comes up as one of the new configurable items in Reporting Services Configuration Manager, as highlighted in Figure 1.
This new configuration setting enables you to configure a server-wide level file share account that can be used by a Windows file share delivery subscription to upload file artifact onto a given file share. If you recall, in versions prior to SSRS 2016, you had to specify logon credentials every time you setup a Windows file share delivery subscription. In other words, if you had 15 subscriptions that use the same account to upload files to the same file share – you had to retype the same logon credentials 15 times!
As of SSRS 2016, you can configure the file share account once and instead of retyping it for every subscription you just choose the associated radio button option in subscription settings, as shown in Figure 2.
Just some additional general notes on using file share account:
- It is available in native mode only
- If it hasn’t been configured under configuration manager then the associated radio button option will be disabled in subscriptions settings
- Just because it’s been configured doesn’t mean that you always have to use it in your subscription – as shown in Figure 2, you still have the option to manually configure another Windows user credential
- As best practice, do not use file share account as the same account used for running Reporting Services service
Likewise, every new feature has its pros and cons and the file share account is no exception. So beware that file share account could introduce a security threat in your environment. This is because file share account (like any account used in a Windows share delivery subscription) requires write permissions which means if the aforementioned 15 subscriptions were actually writing onto 15 different file shares and you intended on using the same file share account for all your 15 report subscriptions then you would need to grant the same account writer access across all 15 Windows locations which could be a security risk.
Data-Driven Subscription Changes
The next big change after configuration is in the new simplified way of creating data-driven subscriptions in SSRS 2016.
- Reduced Creation Steps
Dataset Fields Preview
- Centralised New Subscription Landing Page
Although, in versions prior to SSRS 2016, you had multiple ways to get to the subscription page (i.e. click Subscribe from dropdown report menu, click My Subscriptions link etc.) only one of the multiple ways would actually get you to the data-driven subscription creation page. The correct method was one that was going to lead you to the New Data-driven Subscription button shown in Figure 6 which you could get to by navigating as follows: {Report Name} > Manage> Subscriptions
Figure 7 SSRS 2016 continues to support multiple methods of getting to the subscription page, but the New Data-driven Subscription button has been done away with and instead, all multiple methods redirect you to the same subscriptions page wherein you can choose whether you want to proceed to create a standard subscription or data driven subscription as shown in Figure 7.
Figure 8
In versions prior to SSRS 2016, data-driven subscription process – for any delivery method – involved a mandatory 6-step process depicted in Figure 3.
The annoying part about this process was that some of the steps were irrelevant for some subscriptions. For instance, if you were configuring a subscription on a report that didn’t have parameters, you still had to go through Step 5 as per the example shown in Figure 4.
In SSRS 2016, unnecessary subscription setting options have been eliminated. Firstly, < Back and Next > buttons have been removed as all the subscription settings take place within one window. Secondly, this single subscription setting window is dynamic in the sense that when the underlying report for the subscription doesn’t contain any parameters then the parameter configuration sub-section is not shown in the subscription setting window. To illustrate this point, Figure 5 shows two screen dumps of subscription setting for a report that doesn’t have parameter and another section with report parameters; you will notice that the next section after the Dataset section for the report without parameters is the Create subscription / Cancel buttons (effectively, the end of the subscription setting) whereas the subscription setting for the report with parameters has a further one more section – the Report parameters section.
Another newly introduced data-driven subscription feature in SSRS 2016 is that you can now get a preview of the fields that will be returned by your subscription dataset. I hope the listing of dataset fields can deter those people that continue to run a SELECT * T-SQL command in their subscription dataset from retrieving unnecessary fields by rewriting the dataset command with specific fields that they want to retrieve.
Enable and disable subscriptions
I actually wrote this article whilst on vacation during which I was bombarded by work email subscriptions for one of my company’s sales reports – despite the fact that the sales department was also on Christmas leave. Well, thanks to this new feature I was able to enjoy my holiday by temporarily disabling the subscription. I did all of that by just clicking the Disable button on report manager as shown in Figure 8.
You have to understand that prior to this new feature, to enjoy my holiday would have meant committing some drastic actions on my part:
- Deleting the subscription entirely (thereby creating more work for me when I return to work/ sales department resumes operations)
- Disabling the schedule used to run the subscription
- Log on to SSMS just to disable the SQL Server Agent job used to run the subscription
Render Format PowerPoint
One of the oldest requested items in Microsoft Connect has finally been actioned and thus as shown in Figure 9, SSRS 2016 enables you to send out subscriptions with PowerPoint formatted attachments.
Just bear in mind that most of SSRS controls (i.e. matrix, tablix) get saved as images in the PowerPoint format of the report which means that some of the programmable actions (i.e. hyperlinks to other resources) that might have been configured in your report could be disabled in the PowerPoint version of the report.
Standard Subscription Description
When you are looking to edit a subscription that exists in an environment that has several other subscriptions, the description field makes it easier to identify that particular subscription you are looking for. Unfortunately, prior to SSRS 2016, the description for a standard subscription type was merely a subscription status as the ability to set subscription description was limited to only data driven subscription (which is only available through the enterprise license of SQL Server). Figure 10 shows an example of an SSRS 2014 standard subscription whose description is just a previous subscription status.
Well in SSRS 2016 you no longer have to upgrade your SQL Server license just to have control of your subscription description as you are now given the ability to edit a description of standard subscriptions too.
Transfer Subscription Ownership
Over time, SSRS administrators are bound to come and go from your team at which point you may need to hand over ownership of a subscription into another administrator to look after. This is where such a feature is most useful. Say for example I needed to transfer ownership of the subscription described as rslogsubs2 in Figure 12.
Well, all that I would need to do is edit the subscription, navigate to the Owner textbox and provide the domain account that I would want to set as the new owner for this subscription.
Once I have applied the changes, the subscription will be removed from the list of my subscriptions as shown in Figure 14.
The only downside to this new subscription feature is that the domain account that you will provide as the new owner of the subscription will only be verified at run time (i.e. when you click Apply to commit the change), so if you have mistyped some character or provided a totally fictitious domain account (as shown in Figure 15), you won’t know about it until you apply your changes in which an error message similar to the one shown in Figure 16 will come up.
Missing Subscription Interactive Sort
Unfortunately, not all changes made in this latest version of SSRS are for the betterment of our lives as administrators. As highlighted in Figure 17, in versions prior to SSRS 2016 you had the ability to sort a subscription list by clicking on field names i.e. Description.
This interactive sort feature has unfortunately been removed in SSRS 2016.
The next article in this series:
References
- Reporting Services Configuration Manager (Native Mode)
- File Share Delivery in Reporting Services
- Disable or Pause Report and Subscription Processing
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu