SSRS - Different ways of Deploying Reports

1.   Background

This article describes how to deploy the reports in different ways using SQL Server Reporting Services: Following are the different ways in which we can deploy the report:

• Report Deployment from BIDS/Report Builder/SQL Server Data Tools.
• Report Deployment from Report Manager.
• RS command prompt utility
• Backup and Restore of Report Server DB.

2.   Prerequisite

• Reporting services should be configured in the instance.
• SQL Server Reporting services should run.
• Reports that need to deploy should run in the BIDS/ Report Builder/ SQL Data Tools without any issue.
• Report Server and Report Manager URL should work.

3.   Steps to deploy the report:

a)    Report Deployment from BIDS/Report Builder/SQL Server Data Tools.

Below is the sample report named “EmployeeReport” with shared dataset and data source in BIDS. Report is working fine in BIDS. Below is the screenshot.

  

                I.        Right click on the project top node named “MyProjectSolution” and click on Build:

Build succeeded :

              II.        Right click on project top node named “MyProjectSolution” and select Property:

             III.        It will open the property window. Here we can set the deployment property for the report. Below are the property need to set for report deployment:

• TargetDataSourceFolder         :           MyDataSource
• TargetDatasetFolder                :           MyDataSets
• TargetReportFolder                  :           MyReports
• TargetReportPartFolder           :           MyReportPartFolder
• TargetServerURL                      :           http://vishal-pc/reportserver
• TargetServerVersion                :           SQL Server 2008 R2 or later
• OverwriteDatasources             :           Based on your requirement
• OverwriteDatasets                    :           Based on your requirement

Below is the screenshot showing deployment configuration settings:

                       

                       

Click ok to save the settings.

            IV.        Go to the report server URL to check whether Report server URL is working fine or not: http://vishal-pc/reportserver :

Report server url is working fine and there are no reports available.

             V.        Now go to Reporting solution, right click on project top node and click on deploy:

So Report has been deployed successfully. See the below screenshot from BIDS:

            VI.        To verify whether report deployed successfully or not. Go to Report server URL and run the report.

Here we are able to see the deployed objects:

           VII.        Go to My Reports folder and run the report:

Report is working fine. See the below screenshot:

Note : Deployment steps for Report builder/ SQL Data Tools is same as BIDS.

b) Report Deployment from Report Manager

Report deployment from Report Manager means uploading the Reports, Data Sources and Datasets from file system to Report Server DB by using Report Manager.

                I.        Below are the report items that we will deploy  by using Report Manager:

 

              II.        Go to Report Manager URL e.g  (http://servername/reports) and check whether these report items are there or not:

There are no report items in the Report Manager.

             III.        Create the folder for Datasource, Dataset and Report in report manager. For creating folder click on new folder:

           

Provide the folder name and description and click ok.

It will create the folder for Datasource. Create the folder for Dataset and Report also.

            IV.        Below is the screenshot of all the folders created in Report Manager:

             V.        Go to DataSource folder and click on New Data Source:

It will open the page for creating data souce. Provide the Data source name, connection string and credential type and click Ok.:

Now we can see data source has been created.

            VI.        Now go to DataSet folder and click on Upload File:

 Provide the path of Dataset and clcik Ok:

It will create the dataset. Below is the screenshot:

           VII.        Repeat the VI for Report folder and upload the rdl file. Below screehsot showing datasource, dataset and report both uploaded successfully.

                    

         VIII.        Now go to the report folder, click on drop button on report and click on Manage to see whether report is mapped to correct dataset or not:

It will open the property for the report. Click on “Shared Dataset” and select the correct dataset for report.

Click Ok. It will map the “Reseller” dataset to the report.

            IX.        Go to DataSets folder and select the Dataset “Reseller” and click on Drop button and select Manage:

             X.        It will open the Dataset property. Click on DataSource and select the dataset for the dataset:

Click Ok. It will map the dataset to datasource.

            XI.        Go to the report folder and run the report.

Report is working successfully.

c)  Backup and Restore of Report Server DB:  It is one of the simplest way of deploying all the reports from one environment to another environment. This way is useful when we are deploying report first time in any environment in which there are no existing reports.

                I.        Below is source environment where all the reports are available(http://vishal-pc/reports)::

                 

Below is the report that is available in this environment :

 

              II.        Below is the target environment where no report items are available(http://sqlcircuit/reports):

             III.        Take the Backup of ReportServer DB from the source system:

 

            IV.        So we have taken the backup of source Report server DB in G:\ReportDeployment folder. Now restore it to the target SQL server instance.

Note: SQL Server Reporting Services should configured properly in the target instance

             V.        Now go to Report Manager URL and check whether all the report items came or not. (http://sqlcircuit/reports)

            VI.        Run the report and see whether you are able to run the report or not:

Report is working fine.

d) Report Deployment using RS utility

RS.exe is a command prompt utility that Processes script you provide in an input file (.rss). We use this utility to automate report server deployment and administration tasks.

RSS file: The .rss file contain the code to read the report item  file from the local system, upload the file to report server and set data source. We can write .rss file using Visual Basi.net.

Below is the syntax for RS.exe

rs –i c:\ReportDepolyment\RSDeploy.rss -s http://localhost/reportserver

4.   Conclusion

By using the above steps, we can deploy the SSRS reports

----------------------------------------------------End of Document---------------------------------------------------