How to: Retrieve Data from a SQL Server Data Source
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 Shared Data Sources and Embedded Data Sources and Creating, Modifying, and Deleting Shared Data Sources.
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. For more information, see Working with Report Designer in Business Intelligence Development Studio.
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 AdventureWorks database on the local client.
Data Source=localhost;Initial Catalog=AdventureWorksClick Credentials.
Set the credentials to use to connect to the data source. For more information, see Specifying Credential and Connection Information for Report Data Sources.
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
**Table **Choose from a list of tables on the data source. When you select a table, a query is automatically generated that selects all columns from the table. For example, for a table that is named Customers, the following query command equivalent is used: SELECT * from Customers.
**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