Home > Sample chapters > Programming > SQL Server

Microsoft SQL Server 2008 Reporting Services: Deploying Reports to a Server

This chapter from Microsoft SQL Server 2008 Reporting Services Step by Step shows you how to deploy reports to the report server, organize content on the report server by using folders, create a linked report on a native-mode report server, override report parameter settings, manage authentication in data sources, control report execution, and create and store report snapshots in history.

After completing this chapter, you will be able to:

  • Deploy reports to the report server.

  • Organize content on the report server by using folders.

  • Create a linked report on a native-mode report server.

  • Override report parameter settings.

  • Manage authentication in data sources.

  • Control report execution.

  • Create and store report snapshots in history.

The process of deploying reports includes a variety of tasks that are covered in this chapter. At minimum, you place report definition files on the report server to make reports available to users. Typically, you also configure data sources to associate reports with a different test or production database. You might create alternate versions of a base report by setting up linked reports or generating report copies. You can also configure report properties, such as report parameter default values, execution options, or history management properties, to control what data displays in a report, the amount of time before the user request that the data is retrieved for the report, and the amount of time a snapshot of a report is saved on the report server.

You learn more about assigning security permissions to control user activity on the report server in Chapter 11, “Securing Report Server Content.”

Reviewing Deployment Options

The deployment process copies the report definition into the ReportServer database described in Chapter 1, “Introducing Reporting Services.” A shared data source or a file that you want to store centrally for user access can also be deployed to the report server and is similarly stored in the ReportServer database. Report developers typically deploy a report directly from Business Intelligence Development Studio (BI Dev Studio), while report server administrators often deploy a report manually by using Report Manager or programmatically by using the Rs utility (described more fully in Chapter 16, “Programming Reporting Services”).

Deploying a Report Project

A solution in BI Dev Studio can contain one or more report projects, each of which can contain one or more reports. Each project has a set of properties to define the target location for the reports and data sources that it contains. After you define these properties, you can deploy all reports in all report projects in the solution, all reports in a single report project, a selected set of reports, or a specific report.

In this procedure, you use BI Dev Studio to deploy a report server project.

Deploy a report project to a report server

  1. Click Start, select Microsoft SQL Server 2008, and then select SQL Server Business Intelligence Development Studio.

  2. On the File menu, select Open, and then select Project/Solution.

  3. In the Open Project dialog box, navigate to the Microsoft Press\Rs2008sbs\Chap10 folder in your documents folder, select Sales.sln, and click Open.

  4. In Solution Explorer, right-click Sales, and then select Properties.

  5. In the Sales Property Pages dialog box, in the TargetDataSourceFolder text box, provide the target location for the shared data source in the project. You can create a hierarchy of folders also by including each folder name in the TargetDataSourceFolder box and separating each folder name with a forward slash (/).

    • Native mode: Keep the default value, Data Sources. This folder will be created on the Home page of Report Manager if it doesn’t already exist.

    • Integrated mode: Type http://<servername>/sites/ssrs/data connections, replacing <servername> with the name of your server.

    In a typical SharePoint integrated-mode configuration, you store data sources in a data connections library, but you can use any document library to which the report data source content type has been added.

  6. In the TargetReportFolder text box, provide the target location for the reports in the project.

    • Native mode: Keep the default value, Sales.

    • Integrated mode: Type http://<servername>/sites/ssrs/ReportsLibrary, replacing <servername> with the name of your server.

    The target report folder can be any document library to which the report content type has been added as described in Chapter 2.

  7. In the TargetServerURL text box, provide the URL for the target report server.

    • Native mode: Type http://localhost/ReportServer, as shown here, and then click OK.

      httpatomoreillycomsourcemspimages379212.jpg
    • Integrated mode: Type http://<servername>/sites/ssrs, replacing <servername> with the name of your server, as shown here.

      httpatomoreillycomsourcemspimages379214.jpg

    Notice that the target server URL does not include a reference to the report server virtual directory as it does when deploying to a native-mode report server. In this case, you deploy to a SharePoint site that is configured for Reporting Services as described in Chapter 2.

  8. In Solution Explorer, right-click Sales, and select Deploy.

  9. Review the output of the deployment process in the Output window, which displays at the bottom of your screen in the default layout. The Output window shown here is for deployment to a native-mode report server. An integrated-mode report server deployment looks very similar and displays the report’s URL reference as each report deploys.

    httpatomoreillycomsourcemspimages379216.jpg
  10. When deployment completes, open Windows Internet Explorer and navigate to the Web application hosting Reporting Services.

    • Native mode: In the address bar, type http://localhost/Reports to open the Home page in Report Manager, and then click the Sales link to view the deployed reports, as shown here.

    httpatomoreillycomsourcemspimages379218.jpg
    • Integrated mode: In the address bar, type http://<servername>/sites/ssrs/ReportsLibrary to open the Reports Library, replacing <servername> with the name of your server, as shown here.

    httpatomoreillycomsourcemspimages379220.jpg

    All files are physically stored as binary files in the Catalog table in the ReportServer database. For a native-mode report server, Report Manager queries this table to retrieve a list of the items for the current folder, Sales, and displays the results in two columns in alphabetical order. An integrated-mode report server also stores the report definition files in the SharePoint content database and displays the list of report definition files as a single column of report titles.

Uploading a Report

In some organizations, certain users might have permission to add content to the report server but might not be report developers and therefore won’t have BI Dev Studio installed on their computers. These users can upload reports directly to the report server using the Web application interface.

In this procedure, you upload a report to the report server.

Upload a report to a report server

  1. In Internet Explorer, open the target report folder.

    • Native mode: If the Sales folder is not already open, in the address bar, type http://localhost/Reports/Sales.

    • Integrated mode: If the reports library is not already open, type http://<servername>/sites/ssrs/ReportsLibrary, replacing <servername> with the name of your server.

  2. Upload the Sales Summary report definition file.

    • Native mode: On the Report Manager toolbar, click Upload File, click Browse, navigate to the Microsoft Press\Rs2008sbs\Chap10 folder in your documents folder, select Sales Summary.rdl, and click Open. In the Name text box, keep the default report name, as shown here, and click OK.

    httpatomoreillycomsourcemspimages379222.jpg
    • Integrated mode: On the SharePoint toolbar, click the Upload button, click Browse, navigate to the Microsoft Press\Rs2008sbs\Chap10 folder in your documents folder, select Sales Summary.rdl, and click Open. On the Upload Document: Reports Library page, shown here, click OK.

    httpatomoreillycomsourcemspimages379224.jpg
  3. On a SharePoint integrated-mode report server, keep the default report name, as shown here, and then click OK.

    httpatomoreillycomsourcemspimages379226.jpg

Using the Rs Utility

A report administrator can use script files to manage many activities on a native-mode report server, including report deployment. When you want an administrator to deploy reports in bulk, you develop a script file using Microsoft Visual Basic .NET and save the file as a Unicode or UTF-8 text with an .rss file name extension. Then you provide the administrator with the script file and report definition files to be uploaded. The report administrator can then use the script file as an input file for the Rs utility (described in more detail in Chapter 16).

In this procedure, you execute the Rs utility with the DeploySalesReports.rss script to deploy reports.

Deploy reports to a native-mode report server by using the Rs utility

  1. On a native-mode report server, open a command prompt window.

  2. Type the command to navigate to the folder containing the script file. For example, if you’re running Windows XP or Windows Server 2003, type cd C:\Documents and Settings\ <username> \My Documents\Microsoft Press\Rs2008sbs\Chap10. If you’re running Windows Vista or Windows Server 2008, type cd C:\Users\ <username> \Documents\Rs2008sbs\Chap10.

  3. To run the Rs utility, type rs –i DeploySalesReports.rss –s http://localhost/ReportServer, and press Enter. The results of executing the utility display in the command prompt window.

  4. To Confirm the successful deployment of the reports, switch to Internet Explorer, navigate to http://localhost/Reports, and click the Sales And Order Quantity Analysis link in Report Manager and view the list of reports.