Report Subscription Changes in SQL Server Reporting Services 2016
Report Subscription Changes in SQL Server Reporting Services 2016
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.
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.
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.
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.
Figure 5 Dataset Fields Preview 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.
Figure 6 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
Figure 9 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
Figure 10 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.
Figure 11 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.
Figure 12
Figure 13 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.
Figure 14 Once I have applied the changes, the subscription will be removed from the list of my subscriptions as shown in Figure 14.
Figure 15 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.
Figure 16
Figure 17
Figure 18 This interactive sort feature has unfortunately been removed in SSRS 2016.
Figure 19 The next article in this series: How to administer SQL Server Reporting Services (SSRS) subscriptions using PowerShell
Author Recent Posts Sifiso NdlovuSifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu Latest posts by Sifiso Ndlovu (see all) Dynamic column mapping in SSIS: SqlBulkCopy class vs Data Flow - February 14, 2020 Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events - July 1, 2019 Bulk-Model Migration in SQL Server Master Data Services - May 30, 2019
SQLShack
SQL Server training EspañolReport Subscription Changes in SQL Server Reporting Services 2016
December 30, 2016 by Sifiso Ndlovu 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.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.
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 In versions prior to SSRS 2016, data-driven subscription process – for any delivery method – involved a mandatory 6-step process depicted in Figure 3.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.
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.
Figure 5 Dataset Fields Preview 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.
Figure 6 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
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.Figure 9 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.Figure 10 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.Figure 11 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.
Figure 12
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.Figure 13 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.
Figure 14 Once I have applied the changes, the subscription will be removed from the list of my subscriptions as shown in Figure 14.
Figure 15 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.
Figure 16
Figure 17
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.Figure 18 This interactive sort feature has unfortunately been removed in SSRS 2016.
Figure 19 The next article in this series: How to administer SQL Server Reporting Services (SSRS) subscriptions using PowerShell
References
Reporting Services Configuration Manager (Native Mode) File Share Delivery in Reporting Services Disable or Pause Report and Subscription ProcessingAuthor Recent Posts Sifiso NdlovuSifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu Latest posts by Sifiso Ndlovu (see all) Dynamic column mapping in SSIS: SqlBulkCopy class vs Data Flow - February 14, 2020 Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events - July 1, 2019 Bulk-Model Migration in SQL Server Master Data Services - May 30, 2019