Lesson 6: Adding a Parameter to Pass to a Drillthrough Report

  • Article

New: 17 July 2006

Drillthrough reports are a type of report that you access by clicking a link in the current report. When you click on a text box with a drillthrough action, you open the drillthrough report. If the drillthrough report has parameters, you need to pass parameter values to each report parameter.

In this lesson, you will import the AdventureWorks sample report Sales Order Detail to your report server project and create a drillthrough action on the text box in the Sales Order report that contains the sales order number. You will set the drillthrough report parameter to be the sales order number, as expected by the drillthrough report. You will also create an internal parameter that defines the link color to use for the hyperlink text in the text box.

To import an existing report to your report server project

  1. In Solution Explorer, right-click on the Report folder. Choose Add, and then Existing Item. The Add Existing Item dialog box opens.

  2. Navigate to the folder where the AdventureWorks sample reports are installed. The default directory is <installdir>:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports. Select Sales Order Details.rdl. The report is added to your report project.

    You now need to bind your dataset data sources to your AdventureWorks database.

  3. In Solution Explorer, double-click Sales Order Details.rdl.

  4. Click the Data tab. The error "A connection cannot be made to the database" appears.

    You need to modify the data source for the two datasets in this report to point to the AdventureWorks data source on your system.

  5. From the Dataset drop-down list, select SalesOrder.

  6. Click the Edit Dataset (…) button. The Dataset dialog box opens.

  7. Next to the data source drop-down list, click the Edit Data source (…) button. The Data Source dialog box opens.

  8. Verify that Type equals Microsoft SQL Server.

  9. Click Edit next to the Connection String pane. The Connection Properties dialog box opens.

  10. Enter the server name and authentication for your system. Stand-alone systems frequently use server name localhost and Windows authentication.

  11. In the Connect to a database section, type AdventureWorks.

  12. Click Test Connection. If you do not get a successful connection, contact your database administrator.

  13. Click OK.

  14. Repeat steps 5-13 for the SalesOrderDetail dataset.

Add a report drill through action to your report

  1. In Solution Explorer, double-click Sales Order.rdl.

  2. Click the Layout tab to change to Layout view.

  3. In the Sales Order column, click in the detail row text box. The value in the text box should be =Fields!SalesOrderNumber.Value.

  4. Right-click in the text box, and select Properties. The Textbox Properties dialog box opens.

  5. Click the Navigation tab.

  6. In the Hyperlink section, click Jump to report.

  7. From the drop-down list in the text box, select Sales Order Detail.

  8. Click the Parameters button. The Parameters dialog box opens.

  9. From the Parameter Name drop-down list that shows the list of parameters defined for the drillthrough report, choose SalesOrderNumber.

  10. Click in the Parameter Value text box. From the drop-down list, select =Fields!SalesOrderNumber.Value.

  11. Click OK.

    Next, change the text style and color for the drillthrough link.

  12. Click the Font tab.

  13. From the Decoration drop-down list, choose Underline.

  14. Click OK.

  15. In the Properties window for this text box, click in the Color property text box. From the drop-down list, choose Expression. The Expression Editor opens.

  16. Replace the default text Black with the following expression:

    =Parameters!MyFontColor.Value

    The text box you added now has underlined text and will appear as the color set in the parameter MyFontColor. These visual indicators help report readers understand there is an active link to another report.

    Before you can preview the report, you need to define the MyFontColor parameter in the next step.

To create an internal report parameter to use for Font Color

  1. In Layout view, on the Report menu, click Report Parameters. The Report Parameters dialog box opens.

  2. Click Add. A new parameter is created with property defaults set for you.

  3. In the Properties section, in the Name property, type MyFontColor.

  4. Verify the Data type is String.

  5. Select the Hidden check box. The Prompt property is disabled.

  6. Verify Available values is Non-queried.

  7. In Default values, select Non-queried and type the value MediumBlue or any other valid font color.

    Note

    To see valid values for font color, click the Foreground Color button on the Report Format toolbar.

  8. Preview the report. Verify the sales order numbers appear underlined and with a different font color.

  9. Click a sales order number. The Sales Order Detail report for that sales order number opens.

Note

If you have made changes to the detail report but continue to get an error when you click the drillthrough link in the main report, the data cached for the report might need to be refreshed. Report Designer caches query results on the client in an Rdl.data file to improve performance. To refresh the local cache, change the query in Report Designer by adding a space, and click Preview.

Next Steps

In this lesson, you have successfully imported an existing report and rebound its data sources. You have added a parameterized drillthrough link in the main report to the imported report and created an internal parameter to define a drillthrough link color. This completes the Adding Parameters to a Basic Tabular Report tutorial. For more tutorials that use parameters, see Tutorial: Advanced Features Using Parameters.

See Also

Other Resources

Working with Parameters in Reporting Services
Using Parameters to Connect to Other Reports

Help and Information

Getting SQL Server 2005 Assistance