Tuesday, February 27

SSRS (SQL Server Reporting Service) integration on SharePoint 2019.
Part 3

SharePoint 2019 SSRS Integration Part 1
SharePoint 2019 SSRS Integration Part 2

In the previous section, we have installed and configured the SSRS (SQL server reporting service) for SharePoint 2019. In this section, we will deploy the Microsoft report viewer web part as a farm solution. Import the generated reports to the SharePoint site from the SSRS server.

You can download the report viewer web part from here.

Deploy report viewer web part on SharePoint 2019

In the first step, we need to deploy the report viewer web part as a farm solution in SharePoint 2019. Run SharePoint management shell as administrator and with this PowerShell command.

Add-SPSolution LiteralPath “PathOfYourSolution/ReportViewerWebPart.wsp”

After the PowerShell command execution is completed, we can see the status of the report viewer web part. Name, Solution ID, and deployment status. Deployment status is false.

SharePoint 2019 solution management

Login to SharePoint Central Administration. Navigate to the System Setting and Manage farm solutions. In the solution management section, we can see the name of the solution, deployment status, and deployed to. Status is not deployed and deployed to none. Click on the solution.

Solution Properties

These are the solution properties. Click on the Deploy Solution.

Deploy solution in SharePoint.

If you are planning to deploy the solution on a specific date and time. Create a schedule. If you choose now, it will deploy the solution immediately.
The second part of deploying the solution is to choose web applications, where you want to deploy it. If you choose the ALL content web applications, it will deploy for all web applications which you have already created or you will create in the future. If you want to deploy for the specific web application, select the specific one. And click on ok.

deployed solution in SharePoint Central Administration

The solution is deployed as a SharePoint farm solution. Status is deployed and deployed to the whole SharePoint server. All the web applications can use this report viewer web part from the site settings.

We need to create the SharePoint site. In SharePoint 2019, there are two types of site collections. Classic experience and modern. The Morden page does not support the web part. For the Report viewer web part, we need to create the classic page/site.

Site settings

Access your classic site in SharePoint 2019. And go to the Site settings.

Site collection features

Select the Site collection features under the Site Collection Administration section.

Look for the Report viewer web part and activate it.

Edit the page

After activate the report viewer web part. Go back to the home page and click on edit.

add report viewer web aprt to the page

Go to the Insert tab (1). look for the SQL server reporting service (Native mode) (2) in the Categories section. Select Report Viewer (3) and click on Add (4). The Report viewer web part will be added to the site.

Edit report viewer web aprt

Now we need to edit the report viewer web part to provide the report server and reports connection string.

Report Viewer server connection

Provide the Report server URL and the report path/link.
You will get the permission denied error. Like this “The permission granted to user ‘NT AUTHORITY\IUSER‘ are insufficient for performing this operation. (rsAccessDenied)”.

This is due to the permission issue for a specific user. The report viewer web part is trying to connect with the report server and report with the NT AUTHORITY\IUSER user by default. We need to provide the Browser role for this user on the report and dataset in the report server.

Manage report permission

Go back to the report server. Select report and click on the Manage.

Report Security of SSRS report

Select the Security and Customize security.

Reports roles on ssrs report

Add NT AUTHORITY\IUSER in the groups or user section and assign the Browser roles.
Repeat the same steps for the dataset too.

SSRS reports on SharePoint web aprt.

Now you will be able to view the SharePoint list or library reports on SharePoint 2019 site page using the report viewer web part.

1 Comment

  • Soni Samuel P

    I had followed the same steps as you have mentioned in this tutorial but My Farm is 1 WFE (SP2019) + 1SQL DB (2019). I had installed the Reporitng Service on the WFE, configured it, and am able to view the reports in both reportbuilder and the Report server without any problem.

    But when I try to view it from the Classic Site wit the Report Viewer Webpart, I am getting the below error even though I gave the IUSR permission on both the Dataset & Report.

    An error has occurred during report processing. (rsProcessingAborted)
    The execution failed for the shared data set ‘TestDataSet’. (rsDataSetExecutionError)
    Cannot create a connection to data source ‘ Data source for shared dataset’. (rsErrorOpeningConnection)
    Cannot execute as the database principal because the principal “NT AUTHORITY\IUSR” does not exist, this type of principal cannot be impersonated, or you do not have permission.

    Any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *