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.

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.

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.

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

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.

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.

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

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

Look for the Report viewer web part and activate it.

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

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.

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

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.

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

Select the Security and Customize security.

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

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