By: Scott Murray | Comments (1) | Related: > SQL Server 2016
Problem
What SQL Server Reporting Services (SSRS) enhancements are available in SQL 2016?
Solution
Microsoft has finally offered some much awaited attention to Reporting Services. Several visualization enhancements have provided some new graph and charting options which Koen Verbeeck has outlined in several MSSQLTips (see Next Steps at end of this tip). Fortunately, Microsoft also added a few basic features to subscriptions in SQL Server 2016. Subscriptions now include a feature for stopping and starting a subscription. Furthermore, a subscription now has a description field allowing for details about the subscription to be added to a subscription. Another issue that has been alleviated in SQL Server 2016 is the ability to change the owner of a subscription. Finally, probably for me the most helpful feature addition, is the ability to use shared credentials to save a report exports to a file share.
We will use the Adventure Works databases as the basis for our SSRS example. The 2014 versions (2016 sample databases are not available as of yet) of the regular and data warehouse databases, along with the SSRS report examples are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server, we will subsequently use SQL Server Data Tools for Business Intelligence to complete our work.
SSRS 2016 Subscription Enhancements
Disable SQL Server 2016 Reporting Services Subscriptions
Often you may want to keep all the details of a subscription, but need to temporarily stop it. Fortunately, SSRS 2016 gives us the ability to do just that. As shown below, SSRS now has the ability to select one to many subscriptions and then disable (and then subsequently re-enable them).
To enable or disable, simply select the subscriptions you want to enable / disable and click the enable or disable buttons in the tool bar. Note how you can perform this task in bulk meaning that multiple subscriptions can be selected at once. As shown in the next illustration, once you disable a subscription, the status shows as Disabled and the Warning icon appears to the left of the delivery method (green circle below).
Enable Subscriptions in SSRS for SQL Server 2016
To re-enable, just pick the subscription(s) and then click the Enable button; the warning goes away, as illustrated below, and the status reverts to Ready. There are two other methods of stopping a subscription without deleting it: 1) you can pause a shared schedule if the subscription is part of a shared schedule or 2) you can disable a shared data source. Both of these options have been features for several editions of SSRS; please see the Next Steps section at the end of this tip for links to these methods.
Change Owner of a SSRS Subscription in SQL Server 2016
In a similar way, SSRS 2016 now allows you change the owner of a subscription. However, to change the subscription ownership you need to actually select and then edit each individual subscription. Subsequently, as displayed below, on the edit subscription screen you can insert the new owner for that job. This process is of course helpful when an employee leaves or gets assigned to different responsibilities. Unfortunately this process is a one subscription at a time adjustment; using a script may or may not be the appropriate solution depending on the number of subscriptions you have to update. A previously created PowerShell script has been developed and can be found at this link: https://msdn.microsoft.com/en-us/library/dn747196.aspx. It should be noted that the change ownership function requires either System Administrator role membership or membership in a role that has the Maintain Subscriptions task.
If you attempt to use an invalid user in the Owner field, the following error will result.
Add a Description to an SSRS Subscription in SQL Server 2016
Another new addition to the SSRS subscription stack is the ability to now add a description to each subscription. As shown next, the description now shows on the edit subscription screen.
Furthermore this description also now displays on the summary list of subscriptions. Of course having the description provides ample opportunity to detail the particular important points of a subscription. Again, another welcome addition.
Configure a Single Credential for Report Exports to a Shared File Store
The last new option is likely one of my personal favorites as we now have the ability to create and set a single credentialed account to be used when exporting reports to file server location. In many environments which use Active Directory based service accounts, those accounts often have passwords which expire frequently. Having to change many subscriptions is a pain, but using a single credentialed approach allows you to just change the password (and maybe user ID) in only one place. First we need to add the user ID and password in the SSRS Configuration Manager as shown below. Next we select the Subscription Settings tab and then we must check "Specify a file share account" to activate this option. Finally, you fill in the Account and Password details and then click Apply and then Exit.
Once you complete the configuration then when you create a subscription you can select the Use file share account options under the Credentials used to access the file share area (note this option only activates when you select the Windows File Share Delivered By option).
Now when the account or password changes, we only need to complete the password or user account update in one place.
Conclusion
It is exciting to see some SSRS subscription enhancements added in the 2016 CTP version of SQL Server. These enhancements range from the very basic, a new subscription description field, all the way to a way to use a single file share account in your subscriptions. In between, we now can enable or disable one or many subscriptions and, also, without the use of a script, change the owner of a subscriptions. These features all add up to much needed improvements to the subscription functionality.
Next Steps
- Keep the SQL Server 2016 SSRS Subscription features in mind when you upgrade and think about how to incorporate these features into your future projects.
- Check out these additional resources:
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
