SQL Server Reporting Services SSRS 2017 Deployment and Implementation
By: Scott Murray
Once a report is completely designed, the next step is to post or deploy the report for all to see. In this particular section of our tutorial, we will focus on the deployment process; however, we will discuss the security ramifications of deploying report in a later part of this tutorial series. There are multiple methods to complete the deployment process.
To start off the process, we will assume that Visual Studio—SSDT-BI is being used and has been opened to a new report, and that the data source(s) have been created along with any necessary parameters. Furthermore, we will assume the report design is complete and ready for report consumers to review.
Two main deployment techniques are available for a report designer to deploy a report to the report server website. The first method is what I call the manual method of uploading a report. To utilize this method, you first must navigate to the report server website. Then select the upload option.
Next, we need to upload the appropriate files to the report server. In the below example, I first upload my data source file and then my report file. If only the report is uploaded and a shared data source is used by that report, then the report will error out if the data source does not already exist.
If the file already exists on the site, an overwrite warning will pop up.
Now both the data source and the report file have been deployed on the report server, as shown in the below figure.
Alternatively, the report and related files can be deployed using the deployment option in SSDT-BI. I generally find this method much easier and convenient than the manual upload method. However, it does require some pre-deployment setup at the project level. We must set the Deployment properties by selecting Project and then the Project Properties.
On this window, each of the Deployment options should be selected or filled in:
- OverwriteDatasets and OverwriteDataSources will overwrite existing data sets and sources automatically. Note that if these options are set to yes, no warning is given that the data set or data source is being overwritten. Generally, I would not recommend overwriting data sources.
- TargetDataSetFolder notes the location where shared data sets are uploaded.
- TargetDataSourceFolder notes the location where shared data sources are uploaded.
- TargetReportFolder notes the location where reports are uploaded.
- TargetReportPartFolder notes the location where shared reports are uploaded.
- TargetServerURL indicates the URL of your Report Manager. If an instance name is used by the server, this name will need to be part of the URL listed.
- TargetServerVersion notes the appropriate version of the report server site, SQL 2016 or later in the below example.
When you are ready to deploy, the individual report that will be uploaded is selected, then right mouse click and pick deploy.
After completing my deployment, we now have each of the respective folders on our report server.
Within the MSSQLTips folder our Sales Summary report is available and ready to run.
Implementation Options for SSRS
For this series we have focused on deploying reports to a normal SSRS server. However, with the 2017 release several changes should be reviewed going forward. Reporting Services in its current form is still available, but a Power BI Report Server option is also available, for a cost (https://powerbi.microsoft.com/en-us/report-server/). This version allows for PowerBI reports, KPIs, and Mobile reports to be deployed to the Report Server along with the paginated reports that are in use today. Additionally, SharePoint integration mode is going away; in future releases there will only be a Native Mode installation. To compensate for this change, the ReportViewer web part has been brought back to allow for SSRS reports to be viewed within SharePoint (https://docs.microsoft.com/en-us/sql/reporting-services/report-server-sharepoint/deploy-report-viewer-web-part). As PowerBI continues to be rolled out and adopted, it will be advantageous to pay attention to new enhancements that may come to SSRS too.