Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services 2016 Subscription Enhancements


By:   |   Last Updated: 2015-10-27   |   Comments   |   Related Tips: More > 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).

Enable Disable

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

Disable

 

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.

Re-Enable

 

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.


change owner

If you attempt to use an invalid user in the Owner field, the following error will result.


Incorrect Owner

 

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.

Description

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.

Desc Summary

 

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.

SSRS Configuration

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

file share details

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


Last Updated: 2015-10-27


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools