Lesson 2: Modifying the Report Data Source Properties

  • Article

In this lesson, you start by choosing a tool and selecting a report that will be delivered to recipients. Although you can use either Report Manager or Management Studio to define a data-driven subscription, this tutorial assumes you are using Management Studio.

The data-driven subscription that you will define will distribute the Adventure Works sample report, Employee Sales Summary. In the steps that follow, you will modify the data source connection information used by the report to get data. Only reports that use stored credentials to access a report data source can be distributed through a data-driven subscription. Stored credentials are necessary for unattended report processing.

To modify the data source properties

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and click SQL Server Management Studio.

  2. Connect to a report server. From the File menu, choose Connect Object Explorer. In the Connect to Server dialog box, specifying the following:

    1. In Server type, select Reporting Services.
    2. In Server name, select the report server instance you want to connect to. If you have multiple instances installed, click Browse for more to view a complete list of all of the report server instances that are currently installed on your computer.
    3. Click Connect.

  3. In Object Explorer, expand Home, and then navigate to the folder containing the Adventure Works sample reports. By default, the folder name is AdventureWorks Sample Reports. For more information about the sample reports, see AdventureWorks Report Samples.

  4. Expand the Employee Sales Summary sample report, and then expand Data Sources.

  5. Right-click AdventureWorks, and then select Properties.

  6. Select A custom data source.

  7. For Connection Type, select Microsoft SQL Server.

  8. Type the following connection string:

    data source=localhost; initial catalog=AdventureWorks

  9. Click Credentials stored securely in the report server.

  10. Type your user name (use the format domain\user) and password. If you do not have permission to access the AdventureWorks database, specify a login that does.

  11. Click Use as windows credentials when connecting to the data source, and then click OK. If you are not using a domain account (for example, if you are using a SQL Server login), do not click this checkbox.

  12. View the report to verify that the report runs with the credentials you specified. To view the report, right-click the report and select View Report. Note that once the report is open, you must select an Employee name and then click the View Report button to view the report.

Next Steps

You successfully configured the report to get data using stored credentials. Next, you specify the subscription using the Data-Driven Subscription wizard. See Lesson 3: Defining a Data-Driven Subscription.

See Also

Tasks

Tutorial: Creating a Data-Driven Subscription

Other Resources

Setting Data Source Properties in Reporting Services
Specifying Credential and Connection Information

Help and Information

Getting SQL Server 2005 Assistance