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

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.

Important

To complete the procedures in this chapter, you must be assigned to the Content Manager role on a native-mode report server or to the Owners role on an integrated-mode report server. If you are a local administrator on the server, you have the correct role assignment by default.

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.

   Important

   

   Although the reports in this project are similar to the reports that you created if you completed the previous chapters, you must use the report project in the Chap10 folder to complete all procedures in this chapter successfully.

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

   NOTE

   

   Many of the remaining steps to follow in this procedure depend on whether your target report server is native mode or SharePoint integrated mode. Each step includes instructions for both architectures.

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.

   NOTE

   

   This URL for the integrated mode server assumes that you followed the instructions in Chapter 2, “Installing Reporting Services,” to create a Report Center site called SSRS. Replace the example URL if you have an alternate site available.

   Important

   

   Even if you are deploying to your local computer, you must specify the server name in the URL rather than localhost. Otherwise, the deployment fails with the following error: Report Server has encountered a SharePoint error.

   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.

     NOTE

     

     This procedure assumes that you followed the instructions in Chapter 2 to install Reporting Services on your local computer and to create a virtual directory called ReportServer for the Web service. Replace the server name or the virtual directory name in this URL as appropriate if your configuration differs from the environment described in Chapter 2.

     

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

     

   NOTE

   

   In this example, and throughout this chapter, the server name in the URL is fictitious and is included for illustration purposes only.

   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.

   TIP

   

   In this procedure, you deploy all reports in the project. In some cases, you might prefer to deploy a specific report or set of reports. To limit deployment to a set of one or more reports, select each report to include in the set, right-click one of the reports in the set, and then select the Deploy command from the shortcut menu.

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.

   

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.

    

    • 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.

    

    NOTE

    

    The SampleWorkbook appearing in the Reports Library is a Microsoft Office Excel 2007 workbook added to the Reports Library automatically when you created the site collection. The Reseller Sales item is the report model that is deployed at the end of Chapter 9, “Developing Report Models.”

    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.

   

   • 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.

   

   NOTE

   

   By default, the uploaded document in SharePoint adds the report definition as new content to support the versioning feature in SharePoint. In-depth coverage of generic features of SharePoint such as this is beyond the scope of this book. However, you can learn more from the Help article “Introduction to Versioning,” at http://Office.microsoft.com/en-us/help/HA100215761033.aspx?pid=CL100605171033.

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

   

   Important

   

   The upload process to a report server in native mode or integrated mode does not associate the report definition file with a data source, which prevents you from viewing the report at this time. Later in this chapter, you learn how to correctly associate a report with its data source.

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).

Important

The Rs utility is not supported for use with an integrated-mode report server.

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.