SQL Server Reporting Services SSRS 2017 Deployment and Implementation


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.

Deployment Techniques

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.

manual upload - Description: manual upload

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.

upload data source - Description: upload data source
upload report - Description: upload report

If the file already exists on the site, an overwrite warning will pop up.

overwrite warning - Description: overwrite warning

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.

project properties - Description: 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.
deployment properties - Description: deployment properties

When you are ready to deploy, the individual report that will be uploaded is selected, then right mouse click and pick deploy.

deploy replort - Description: deploy report

After completing my deployment, we now have each of the respective folders on our report server.

SSRS Home - Description: SSRS Home

Within the MSSQLTips folder our Sales Summary report is available and ready to run.

new report - Description: new report
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.

Additional Information

Comments For This Article

get free sql tips
agree to terms