To further the discussion related to Mohamed’s post on Reporting Services 2016 What’s new in SQL Server 2016 Reporting Services (SSRS), I wanted to cover the new features and functionality we see in Reporting Services 2017 as RC1 was released last week SQL Server 2017 Reporting Services Release Candidate now available, separately from SQL Server 2017 which reached RC1 a few days prior.
The most widely anticipated new feature of SSRS is the integration of Power BI to give us the Power BI Report Server. This was generally released back in June as a standalone application and can actually be built upon SQL Server 2016.
(You can try out the current Power BI Report Server release using an Azure image which gets everything up & running for you, including some sample reports. This is built on SQL Server 2016 though)
Installation
Starting from SQL 2016, Reporting Services and Management Studio are now packaged & installed separately with only a web link to each download page within the SQL Server Installation Center so be sure to grab them first if you intend on deploying to a server with no external network access.
Setup & installation is very much the same as in SSRS 2016. You’ll only get the option to Configure manually once setup completes so you can close or configure which presents the Report Server Configuration Manager which is also very familiar.
Bugs
So, I did hit a few issues at this point, using the July 2017 RC1 of Reporting Services. The configuration manager was VERY slow to load & when moving between tabs. I decided to test this with 3 different VMs set up in different environments (Hyper-V and VMWare) with the same results.
I would then get an “RPC server is not listening” error when I tried to create a new (or connect to an existing) ReportServer DB. This only happened with a SQL Server 2017 RC1 engine though. I had no such connection issue with SQL Server 2016 but the slow Configuration manager was still there. Its worth noting that both these issues aren’t present in the March CTP of Reporting Services.
I reached out to the Reporting Services Program Manager, Riccardo Muti on Twitter & also logged an MSDN question and within 48 hours, Riccardo messaged me on Twitter to say they had a fix thanks to the details I provided.
I applied the new installation as an upgrade and could navigate the configuration Manager with no lag or delay & I created the ReportServer Database with no issues.
This kind of quick turnaround & direct interaction with key players at Microsoft just cements their new image and involvement with the community!
New Features
There’s only a few new features here, mainly fine tuning the big changes brought in with SQL Server 2016.
Comments
The most significant addition is the inclusion of a commenting facility, adding a built-in method for users to discuss and provide feedback on reports whether they are traditional paginated reports, mobile reports or the new Power BI reports (if you’re running Power BI Report Server).
Users can also reply to comments & add attachments to their comments, possibly to share screenshots or snippets of data.
Under the hood
Digging further into the comment feature we find a new table in the ReportServer database that contains the comment data.
This table includes the usual GUID references to the ItemID in the Catalog table and the UserID from the Users table for the commenter. The ThreadID is then populated with the CommentID of the comment it replied to, as seen here:
Moving onto security, who is allowed to comment on reports? Is this something we want all users to have access to? Maybe we want to get more specific and grant read only access out-with the developers?
You’re in luck…somewhat. You can customize security for the comment feature but its only got 3 flavors: Comment, Manage or nothing.
Connecting to the Reporting Services instance from Management studio you can see the new tasks under each role:
This is a great start but it may not be enough granularity for everyone. Id be looking for a few other options along these lines:
- View comments. No contribution rights.
- Comment on reports. Denied access to add attachments.
Let’s hope Microsoft expand on this in the future.
Here’s a summary of the default commenting rights for each role out the box:
-
Comment on Reports
- Browser
- Content Manager
- My Reports
- Report Builder
-
Manage comments
- Content Manager
- My Reports
- Publisher
I hope you noticed these next to “Management Studio” because if you try to connect to a SQL 2017 Reporting Services instance or Power BI Report Server instance with anything less than Management Studio 17 (grab it, its free!) you’ll receive this nasty error:
Don’t worry, your instance is there & (likely) online. Once you have SSMS 17 or higher you’ll be able to connect to any version 14.XX SSRS instance as demonstrated below:
With 2017 we also see further separation of the Reporting Services components from the core SQL Server Engine.
This started with SSRS 2016 being packaged as a separate installer but behind the scenes, we still had the same file structure, the same service management and the same instance, the default being MSSQLSERVER.
With 2017 the gap is widening. Looking first at the SQL Server Configuration Manager we no longer see an option to manage the Reporting Services service (You’ll have to trust me that its installed)
Notice that these are all your usual default instance of MSSQLSERVER.
Now the default instance for the Reporting Services 2017 install on the same server is named SSRS as you can see from this SSRS Configuration Manager screenshot
At a file level SSRS 2017 is now completely separate from SQL Server file structure. Traditionally grouped under the Microsoft SQL Server folder & then split by component (RS, AS, SQL) and instance, each component now holds its own folder within Program Files with instances of each (where relevant) managed in sub folders.
Visual tweaks
There’s been a few minor tweaks to the Report Manager interface, mainly streamlining some button arrangements etc.:
View drop down
Bugs Fixed
One thing that has impacted me the most in SSRS 2016 is the absence of the report path when viewing search results in list view. I often have duplicate reports in different folder structures. These are totally indistinguishable from each other in SSRS 2016 as the folder path isn’t shown.
While testing this in SSRS 2017 (RC1 remember) I came across an issue with the Search box in IE (v11) where you can click it but you cannot type in it. This isn’t present in SSRS 2016 in any browser & is not an issue in Chrome with 2017 so I’m confident this will be fixed for or shortly after release.
Coming back to my bug (or a dropped feature?) it seems this is just the same in SSRS 2017. List view in any folder or search results does not show the folder path, which is a shame.
Other well documented “changes” since the new Report Manager interface in 2016 also remain:
- Security option missing from report drop down menu
I blogged this time last year about a possible future concern with the change of URL format in SSRS 2016.
The concern is that although the old URL format cleanly redirects or maps to the new format, how long would Microsoft support this. If we have embedded report links in places like SharePoint, user’s desktops, other applications would they break if we upgraded SSRS?
I’m happy to report that old URL formatting still works & successfully renders the target report while changing the address bar to the new report URL.
References
- What’s new in SQL Server 2016 Reporting Services (SSRS)
- Reporting Services 2017 RC1 Announcement
- Management Studio (SSMS) 17.1
- SSRS 2016 URL Format Changes
View all posts by Craig Porteous
- How to secure Reporting Services with Group Managed Service Accounts (GMSA) - November 7, 2018
- Contribute, contribute, contribute! - June 20, 2018
- Top 10 things you must document in SQL Server Reporting Services (SSRS) - February 26, 2018