How to: Retrieve Data from a SQL Server Data Source (Report Builder 2.0)
To use data from a SQL Server database in your report, you must define a SQL Server data source and one or more report datasets. When you define the data source, you must specify a connection string and credentials so that you can access the data source from the client computer.
You can create an embedded data source definition for use by a single report or a shared data source definition that can be used by multiple reports. The procedures in this topic describe how to create an embedded data source. For more information about shared data sources, see Embedded and Shared Data Sources (Report Builder 2.0) and Creating, Modifying, and Deleting Embedded Data Sources (Report Builder 2.0).
After you create a SQL Server data source definition, you can create one or more datasets. After you create a dataset, the name of the dataset appears in the Report Data pane as a node under its data source.
After you publish your report, you may want to change the credentials for the data source so that when the report runs on the report server, the permissions to retrieve the data are valid.
To create an embedded Microsoft SQL Server data source
On the toolbar in the Report Data pane, click New, and then click Data Source. The Data Source Properties dialog box opens.
In Name, type a name or accept the default name.
Verify that Embedded connection is selected.
From the Type drop-down list, select Microsoft. SQL Server.
Specify a connection string that works with your SQL Server data source.
Contact your database administrator for connection information and for the credentials to use to connect to the data source. The following connection string example specifies the sample AdventureWorks2008 database on the local client.
Data Source=(local);Initial Catalog=AdventureWorks2008Click Credentials.
Set the credentials to use to connect to the data source. For more information, see Specifying Credentials for a Report Data Source (Report Builder 2.0).
Note
To test the data source connection, click the Edit button next to the connection string text box. The Connection Properties dialog box opens. Click Test Connection. If the test is successful, you will see the information message "Test connection succeeded". If the test is not successful, you will see a warning message that has more information about why the test was not successful.
Click OK.
The data source appears in the Report Data pane.
To create a dataset for a Microsoft SQL Server data source
In the Report Data pane, right-click the name of the data source that connects to a SQL Server data source, and then click Add Dataset. The Query page of the Dataset Properties dialog box opens.
In Name, type a name or accept the default name.
In the Data source text box, verify that the name of the data source you right-clicked appears.
In Query type, select from the possible ways you can create a query. Query types that are not supported by the data source type are disabled.
**Text **Transact-SQL commands
**Stored Procedure **Choose from a list of stored procedures on the data source.
Click Query Designer to open the graphical query designer to build a query interactively. Click Run Query (!) to verify the result set, and then click OK.
Alternatively, you can paste a query from the clipboard directly into the Query pane, or import an existing Transact-SQL query from a file or from another report. To import an existing query, click Import, and then use a .sql or .rdl file.
Click OK.
The dataset and its field collection appear in the Report Data pane under the data source node.
See Also