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.
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.
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.
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
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.
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.
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.
You still need to write comments in the textbox otherwise the Post Comment will remain disabled.
Write in the comment box and click Post Comment.
In the below screenshot, the comment shows, but the image is just showing an attachment.
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."
}
}
If the connected user does not have administrative privileges it shows the below error message.
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.
This shows a list of items on the left side. Click on 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.
Then click OK to confirm the security settings that are defined for this item will be replaced by the security setting of its parent.
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.
Now go back to the SSRS report and refresh it. We can see the image in the attachment section.
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.
- 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.
Expand Roles under security.
Double click on the Browser role.
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
- Comment on Reports permissions are assigned to the below roles:
- 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
- Manage comments permissions are assigned to the below roles:
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.
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.
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.
As stated above, since testuser has permission Comment on report, it cannot delete or edit a comment posted by other users.
Commenting on reports seems to be an interesting feature. However, it might require some enhancements which we might expect in future releases.
Next Steps
- Check out the SQL Server Reporting Services Resources.
