Home > Sample chapters > Programming > SQL Server

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

Managing Content

One way to manage content on a report server is to store every report in a single location, such as the Home page, but before long, you’ll find that the number of reports becomes so large that it is difficult to manage security and to find content. You can use folders to simplify report management and usage. You can move content to a new folder later and, on a native-mode server, use a linked report to set up variations of a single report for applying different properties such as security or parameters.

Using Folders to Organize Content

Folders are useful for organizing content into logical groups by subject matter, target audience, security, or any combination of these. Folders can be nested into a hierarchical structure, and you can provide a description for a folder to provide users with more information about the folder contents. On a native-mode report server, you can also hide a folder name from the list view in Report Manager.

In this procedure, you add folders to store reports.

Add a folder to a report server

  1. In Internet Explorer, open the home folder for Reporting Services.

    • Native mode: In the address bar, type http://localhost/Reports to open the Home folder in Report Manager or click the Home link at the top of the window.

    • Integrated mode: Type http:// <servername> /sites/ssrs/ReportsLibrary, replacing <servername> with the name of your server, to open the Reports Library folder in SharePoint.

  2. Add a new folder.

    • Native mode: On the Report Manager toolbar, click the New Folder button.

    • Integrated mode: On the Reports Library toolbar, click the arrow on the New button, and select Folder.

  3. Provide a name for the folder.

    • Native mode: Replace the default name with For Review, and then, in the Description box, type Reports in this folder are pending user review and acceptance, as shown here. Click OK.

      httpatomoreillycomsourcemspimages379228.png
    • Integrated mode: In the Name text box, type For Review, and click OK.

Moving Content

Getting the right folder structure established for reports might take some time. After reports are deployed to a report server, you can move them from one folder to another folder.

In this procedure, you move selected reports from the Sales folder to the For Review folder.

Move content

  1. In Internet Explorer, open the folder containing the Reporting Services reports that you deployed earlier in this chapter.

    • Native mode: On the Home page of Report Manager, click the Sales link.

    • Integrated mode: If the Reports Library folder is not already open, in the address bar, type http://<servername>/sites/ssrs/ReportsLibrary, replacing <servername> with the name of your server.

  2. Open the view that allows you to select the reports to move.

    • Native mode: On the Report Manager toolbar, click the Show Details button.

    When you click the Show Details button, the contents of the folder display as a single list with a check box to the left of each item, as shown here. You can move or delete one or more items in the list by selecting each item’s check box and then clicking the Move or Delete button on the toolbar, respectively.

    httpatomoreillycomsourcemspimages379230.jpg
    • Integrated mode: In the upper-right corner of the window, click Site Actions, select Manage Content And Structure, and then, in the tree view on the left, select Reports Library to view the library contents, as shown here.

    httpatomoreillycomsourcemspimages379232.jpg
  3. Select all reports except the Reseller Sales Margin Analysis by Sales Territory report to move to the For Review folder.

    • Native mode: Select the uppermost check box (to the left of Edit) to select all reports, clear the Reseller Sales Margin Analysis By Sales Territory check box, and then click Move.

    • Integrated mode: Select the uppermost check box (to the left of Type) to select all reports, clear the For Review, SampleWorkbook.xlsx, Sample Dashboard.aspx, Reseller Sales.smdl, and Reseller Sales Margin Analysis By Sales Territory check boxes, click the arrow on the Actions button, and select Move.

  4. Select the For Review folder as the target location for the reports.

    • Native mode: Select the For Review folder, as shown here, and then click OK.

      httpatomoreillycomsourcemspimages379234.png
    • Integrated mode: In the Move... Webpage Dialog box, expand Reports Library, select the For Review folder, as shown here, and then click OK.

      httpatomoreillycomsourcemspimages379236.jpg
  5. Open the For Review folder to confirm that the seven reports have been moved successfully.

    • Native mode: In the upper-left corner of the browser window, click the Home link, and then, in the list view, click the For Review link.

    • Integrated mode: In the address bar, type http://<servername>/sites/ssrs/ReportsLibrary/For Review, replacing <servername> with the name of your server.

Creating a Linked Report

On a native-mode report server, you can create a variation of a report without the need to create and maintain multiple report definition files by creating a linked report. A linked report is a separate report item on the report server that uses a report definition file as a base but has separate execution, parameter, subscription, and security properties. A common reason to use a linked report is if you want to build a report that contains data for multiple departments and includes a parameter to filter the report content by department. You can then deploy the report to a folder that none of the users can access, create one linked report per department, set the report parameter default to a specific department, and configure security such that users can access only the linked report for their department.

In this procedure, you create a linked report for the Reseller Sales Margin Analysis by Sales Territory report.

Create a linked report

  1. In Internet Explorer, click the Home link at the top of the window, and then click the Sales link.

  2. If necessary, click the Show Details button to switch the view to list view.

  3. In the Edit column for the Reseller Sales Margin Analysis by Sales Territory report, click the Properties icon, as shown here.

    httpatomoreillycomsourcemspimages379238.png
  4. Click Create Linked Report.

  5. In the Name text box, type Pacific Reseller Sales Margin Analysis, as shown here.

    httpatomoreillycomsourcemspimages379240.jpg

    Note that, as with the base report, you can add a description to the linked report. By default, the linked report is created in the same folder as the base report. You can click Change Location and select a target folder for the linked report.

  6. Click OK.

    The linked report displays in the browser window. You can now change the properties of the linked report. For example, to display only data for the Pacific sales territory in the report, you must change the parameter properties, which you do in the next procedure.

Copying a Report

Although you cannot create linked reports on an integrated-mode report server, you can create a copy of a report to achieve a similar result. That is, you can reproduce the same report definition as a separate file on the report server and set different execution, parameter, subscription, and security properties on the new file. However, any changes to the base report definition do not update the copy.

In this procedure, you create a copy of the Reseller Sales Margin Analysis by Sales Territory report on an integrated-mode server.

Create a report copy

  1. In Internet Explorer, in the navigation bar on the left, click Reports.

  2. Point to the Reseller Sales Margin Analysis by Sales Territory report, click the Edit button (which appears as an arrow to the right of the report title), point to Send To, and select Other Location.

  3. On the Copy: Reseller Sales Margin Analysis By Sales Territory.rdl page, in the Destination Document Library Or Folder box, append /ReportsLibrary to the existing URL.

  4. In the File Name For The Copy box, replace the default text by typing Pacific Reseller Sales Margin Analysis, as shown here.

    httpatomoreillycomsourcemspimages379242.jpg
  5. Click OK.

  6. In the Copy Progress message box, click OK, and then, when the copy operation completes, click Done.

  7. In the Copy Progress message box, click OK.

    The report copy displays in the list of reports. You can now change properties for the report copy, which you do in the next procedure.

Configuring Report Parameters

Besides managing the location of reports on the report server, you can also manage the default values that are configured for the report parameters. In addition, you can remove the parameter prompt for a report so that a user cannot choose a different parameter value.

In this procedure, you configure the default value of a parameter in the Pacific Reseller Sales Margin Analysis report and remove the user prompt for the parameter.

Configure parameter properties

  1. In Internet Explorer, open the parameter properties page of the Pacific Reseller Sales Margin Analysis report that you created earlier in this chapter.

    • Native mode: Open the report if necessary, click the Properties tab above the report, and then click the Parameters link.

    • Integrated mode: Open the reports library folder for the report, if necessary, point to the report in the list, click the Edit button (which appears to the right of the report title), and select Manage Parameters.

  2. Change the default value for the SalesTerritoryGroup parameter to Pacific.

    • Native mode: On the SalesTerritoryGroup row, in the Default Value drop-down list, clear the rows containing Europe and North America, as shown here.

      httpatomoreillycomsourcemspimages379244.jpg
    • Integrated mode: Click the SalesTerritoryGroup link, and then, in the Use This Value drop-down list, clear the check boxes for the rows containing Europe and North America.

  3. Remove the user prompt for SalesTerritoryGroup.

    • Native mode: On the SalesTerritoryGroup row, clear the Prompt User check box, and then click Apply.

    • Integrated mode: Select the Hidden option, click OK, and then, on the Manage Parameters: Pacific Reseller Sales Margin Analysis page, click Close.

  4. View the report.

    • Native mode: Above the parameter properties, click the View tab to render the report, as shown here.

    httpatomoreillycomsourcemspimages379246.jpg

    Notice that the prompt for the sales territory selection does not appear in the parameter toolbar above the report and that the report displays data only for the Pacific sales territory.

    • Integrated mode: In the Reports Library list, point to the Pacific Reseller Sales Margin Analysis report, click the Edit button (which appears to the right of the report title), and select Manage Data Sources. Click the AdventureWorksDW2008 link, and then click the ellipsis button to the right of the Data Source Link box. In the Select An Item dialog box, click Up, click the Data Connections link, select AdventureWorksDW2008, click OK twice, and click Close. Now that the data source information has been linked to the report, click the Pacific Reseller Sales Margin Analysis link to view the report, shown here.

    httpatomoreillycomsourcemspimages379248.jpg