Export (0) Print
Expand All

Lesson 8: Adding a Linked Report with Customized Parameter Values

SQL Server 2005

New: 17 July 2006

Linked reports can be created after a report is published to the server. You create a linked report from an existing .rdl file on the report server and rename it. You can override parameter defaults and set parameters to specific values. If you change the original report definition and republish it to the server, those changes affect the linked report. The linked report points to the original report definition. Parameters for the linked report can only be set on the report server using Report Manager or SQL Server Management Studio.

In this lesson, you create a linked report called "Resellers Pacific" from the published report "Resellers Worldwide" and override the default parameter value for TerritoryGroup. You will also pre-select values for many of the other parameters and then hide the parameters to provide a less cluttered report toolbar.

  1. In SQL Server Business Intelligence Development Studio, open the Advanced Parameters Tutorial report server project if it is not already open.

  2. In Solution Explorer, double-click on the Resellers Worldwide report. The report opens in Layout view.

  1. In Solution Explorer, right-click the Advanced Parameters Tutorial project, and then click Properties.

  2. Click Configuration Manager.

  3. In the Configuration Manager dialog box, in Active Solution Configuration, select Production.

  4. Click Close.

  5. In the Property Pages dialog box, in TargetServerURL, type the report server virtual directory; for example, http://servername/reportserver. (This is the virtual directory of the report server, not Report Manager.)

    Aa337452.note(en-US,SQL.90).gifNote:
    If the report server is on the same computer as Report Designer, you can use localhost as the server name, for example, http://localhost/reportserver or http://localhost/reportserver$SQLEXPRESS.

  6. Expand the Debug properties node if it is not already open to show the StartItem property. Click in the text box next to StartItem and from the drop-down list, select the report Resellers Worldwide.rdl.

  7. Click OK.

  8. Save the report project. On the File menu, click Save All.

  9. Publish the report. On the Debug menu, click Start Without Debugging.

  10. When publishing is complete, Report Designer opens Internet Explorer. Click Resellers Worldwide to view the report.

In Business Intelligence Development Studio, the Output window displays actions and messages as the report deploys to the report server. If the Output window is not visible, from the View menu, select Output.

  1. Open a browser and browse to the report server reports directory. By default, the URL is http://localhost/reports.

  2. Open the folder Advanced Parameter Tutorial, then select Resellers Worldwide. The report opens in the Report Manager.

  1. In Report Manager, on the report page, click the Properties tab.

  2. At the bottom of the page, click the Create Linked Report button. The new linked report page opens.

  3. In the Properties section, in the Name text box, type Resellers Pacific.

  4. In the Description text box, type Resellers for sales territory Pacific.

  5. Click OK.

  6. The Resellers Pacific report runs.

In the next procedure, you will change the default parameter values to use for the linked report Resellers Pacific. You will change the defaults for many of the parameters to hard-coded values, and then set the Hide property so the parameters do not appear on the report toolbar. This produces a less-cluttered look and provides a way to create reports targeted to different readers but using the same report definition.

Aa337452.note(en-US,SQL.90).gifNote:
Setting the Hide property simply prevents the parameter from appearing on the toolbar. This is not used for securing data.

  1. Click the Properties tab. You do not need to wait for the report to finish rendering.

  2. On the left pane, click Parameters. The Select the parameters page opens.

    On this page, you can manage the parameter settings for the Resellers Pacific report independently from Resellers Worldwide report.

    Change the parameter settings to match those in the following table.

    Parameter Name Default Value Hide Prompt User

    TerritoryGroup

    Pacific

    True

    disabled

    CountryRegion

    Query Based

    True

    disabled

    StateProvince

    Query Based

    False

    True

    City

    Query Based

    False

    True

    UserSortBy

    Number of sales per store

    True

    disabled

    InitialSort

    Total Due

    True

    disabled

    ShowSalesDetails

    Click Override Default and select True.

    True

    disabled

    ContactDisplayFields

    Click Override Default, from the drop-down list, select Phone.

    True

    disabled

    Colors

    SteelBlue

    LightSteelBlue

    True

    disabled

    For the parameter CountryRegion, select the Hide option. Because the Pacific territory group in the AdventureWorks sample database has only one value, you do not need to select a value and can hide this parameter on the toolbar.

  3. Click Apply.

  4. Click the View tab.

    Because the Hide property is set for the other parameters, only the StateProvince and City parameters appear on the report toolbar.

    The default values for StateProvince (New South Wales) and City (Alexandria) have no reseller stores, so the NoRows message appears.

    Because ResellersPacific is a linked report, published changes to the parent report ResellersWorldwide will also appear in this report.

  5. (Optional) From the StateProvince drop-down list, choose Victoria. The City parameter defaults to Melbourne. Click View Report.

    Four cycle shops and their number of sales appear: Cycle Parts and Accessories (4), Eastside Cycle Shop (4), Fitness Discount Store (4), and List Price Catalog Company (4).

You have successfully created a linked report and set its parameters. This concludes the advanced parameters tutorial. For more tutorials, see Reporting Services Tutorials.

Community Additions

ADD
Show:
© 2015 Microsoft