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

 

Adding User Comments to Reports in SQL Server Reporting Services


By:   |   Last Updated: 2018-09-07   |   Comments (2)   |   Related Tips: > Reporting Services Overview

Problem

SQL Server Reporting Services (SSRS) is used to deliver reports by representing data visually with graphs, charts, and create subscriptions to send reports through different methods. SSRS did not provide functionality to put comments on reports by the end users, but this has changed with SQL Server Reporting Services 2017. In this tip, we will explore this feature.

Solution

Installing SQL Server Reporting Services 2017

SQL Server Reporting Services 2017 is installed separately from the SQL Server database installation.  You can download the latest release of SQL Server Reporting Services from the link.

SQL Server Reporting Services 2017 download

Follow this link, installing SQL Server Reporting Services 2017 to learn more about the installation of SQL Server Reporting Services 2017.

Add Comment to a SSRS Report

Once you installed and configured SQL Server Reporting Services 2017, launch the web portal for the reporting service.  The default path for the web portal is http://localhost/reports, however it might be different based on your configuration settings. You can review it from the Report Server Configuration Manager -Web Portal URL as shown below.

Report Server Configuration Manager Icon
Report Server Configuration Manager – Web Portal URL

Note: In this tip, I am not covering creating how to create a report in SQL Server Reporting Service, you can refer to these other Reporting Services tips.

Open any report in the web portal and you can see the Comments section towards the top right corner as shown below.

SSRS 2017 Report Comments

SQL Server Reporting Services 2017 includes an important and useful commenting facility in user reports. Users can provide comments, feedback, suggestions on the traditional paginated reports, Power BI reports, Mobile Reports, etc.

Click on the Comments section and insert comments in the textbox as shown below

SSRS 2017 Report – Post Comments

Click on Post Comment to publish it. The comment appears in the Comments section with the user details, comment and the time of the comment.

SSRS 2017 Report – View comment

Another user or admin can reply to the comment as well. The reply is linked to the previous comment internally. It also shows the user comments in the report.

SSRS 2017 Report –Reply Comment

Attach File as a comment in SQL Server Reporting Services Report

In order to attach a file as comments, click on Attach File and provide the path of the file.

SSRS 2017 Report – Post comment with image

You still need to write comments in the textbox otherwise the Post Comment will remain disabled.

SSRS 2017 Report – Post comment with image - 1

Write in the comment box and click Post Comment.

SSRS 2017 Report – Post comment with image and comment

In the below screenshot, the comment shows, but the image is just showing an attachment.

SSRS 2017 Report – Post comment with image and comment error

Click on the image icon and we get the below error message (if connected with an administrative privileged user).

{
  "error":{
    "code":"1251","message":"An error occurred when invoking the authorization extension."
  }
}
SSRS 2017 Report – Post comment with image and comment – Error screenshot

If the connected user does not have administrative privileges it shows the below error message.

SSRS 2017 Report – Post comment with image and comment – Error screenshot

The option Attach File for Add Comment only works when the report inherits permissions from the folder. If we customize the report permissions, then we are unable to see/attach files on the comments.

To resolve this, we need to change the security settings. Click on eclipse icon (...) and click Manage.

SSRS 2017 Report – Manage permissions

This shows a list of items on the left side. Click on Security.

 SSRS 2017 Report – Manage security

Now in order to show the attachment image, we need to use the same security as the parent folder. Click on Use same security as parent folder as shown below.

SSRS 2017 Report – Manage security as parent folder

Then click OK to confirm the security settings that are defined for this item will be replaced by the security setting of its parent.

SSRS 2017 Report – Confirm security change

Note: be careful in making the security change as it might impact the permissions for the report.

Once we apply the parent security permissions, we can see the Customize security option comes up. It shows we now have the security permissions the same as the parent folder.

SSRS 2017 Report – Manage security

Now go back to the SSRS report and refresh it.  We can see the image in the attachment section.

SSRS 2017 Report – View comment with image

SQL Server Reporting Services Comments Stored in a Database

The ReportServer database contains table [ReportServer].[dbo].[Comments] which shows all the comments if we want to view it from the database.  Connect to the database and select data from the table.

SSRS 2017 Report – look at the database table
  • This table includes the usual GUID references to the ItemID in the Catalog table.
  • The UserID maps to the Users table for the commenter.
  • The ThreadID is then populated with the CommentID of the comment it replied to.
  • An AttachmentID column contains a NULL value if there is no attachment in the comment else AttachmentID is populated.

Manage Permissions for SSRS Comments

In this section, we will see how to manage the comments for the SSRS reports. We might want users only to give access to manage their comments or we do not want to have any comment permissions.

Connect to SSRS instance from Management Studio.

SSRS 2017 Report – Connect with Reporting Services

Expand Roles under security.

SRS 2017 Report – SSRS Roles

Double click on the Browser role.

SSRS 2017 Report – Comment permissions

It shows two new permissions:

  • Comment on reports: With this permission, the user can create, view, edit and delete comments on reports.
    • Comment on Reports permissions are assigned to the below roles:
      • Browser
      • Content Manager
      • My Reports
      • Report Builder
  • Manage comments: With this permission, the user can delete other user comments as well.
    • Manage comments permissions are assigned to the below roles:
      • Content Manager
      • My Reports
      • Publisher

If we do not want users to provide or manage comments, remove the permissions checkbox for the role.

A user with the manage comments permissions can delete the comments posted by other users as well. This gives administrators the ability to remove unnecessary comments.

SSRS 2017 Report – Comment permissions – Edit, Delete

However, a user with only Comment on reports permission can edit, delete the comments posted by themselves. To see this, let's create a user with the Comment on reports permission in SQL Server Reporting Service.

Go to Manage > Security > New Role. Add the user or group and give them browser permission. The browser role already has the Comments on reports permission.

SSRS 2017 Report – Comment permissions – Give Browser permission

Now connect to the SSRS report with the newly created user. You have to open a browser with that user and connect to the SSRS report.

In the below screenshot, we can see we are connected with testuser. Testuser can delete and edit the comment posted by testuser.

SSRS 2017 Report – Comment permissions – Edit, Delete

As stated above, since testuser has permission Comment on report, it cannot delete or edit a comment posted by other users.

SSRS 2017 Report – Comment permissions – No Edit, Delete permission

Commenting on reports seems to be an interesting feature. However, it might require some enhancements which we might expect in future releases.

Next Steps


Last Updated: 2018-09-07


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips




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.



    



Wednesday, April 03, 2019 - 10:55:02 AM - Aftab Back To Top

Very helpful post


Friday, September 21, 2018 - 12:02:29 PM - Anne Back To Top

Thanks for the nice article!

For attachment where it is stored? I can only see the attachmentID in the comments table. where is real file stored to?

Also I can see comment n web portal of SSRS? if the report is embedded in a web page, can the comment also be shown?

Thanks


Learn more about SQL Server tools