Creating Report Datasets from a SQL Server Relational Database
Reporting Services provides a data processing extension that supports report data retrieval from Microsoft SQL Server relational databases. This data processing extension retrieves data from the relational data tables, views, stored procedures, and other data structures defined on the underlying data source. After you have connected to a data source, you can create a report dataset that defines the data you want to use from that data source in your report. A Reporting Services dataset includes a data source, a query that retrieves data when the report is processed, and a collection of fields. The dataset definition is saved in the report definition. The Report Data pane shows the current dataset definitions in a report.
You define a dataset by creating a query using Transact-SQL query text or stored procedure calls, or by choosing a table and retrieving all the data from it. The collection of fields for a dataset is created automatically from the query. You can edit fields or add calculated fields. Calculated fields have expression-based values that are calculated when the query runs. The data types of the fields are mapped to a subset of common language runtime (CLR) data types from the .NET Framework. For more information, see Understanding Report Datasets and How to: Retrieve Data from a SQL Server Data Source.
There are two query designers for relational data sources: a text-based query designer and a graphical query designer. The text-based query designer opens by default. Use the text-based query designer to enter Transact-SQL text directly, to paste query text from another source, to enter complex queries that cannot be built using the graphical query designer, or to enter query-based expressions. Use the graphical query designer to browse the metadata on the data source to interactively design a query or to view a list of stored procedures or tables on the data source. For more information, see Graphical Query Designer User Interface, Text-based Query Designer User Interface, and How to: Retrieve Data from a SQL Server Data Source.
After defining a query, you can also specify properties such as Timeout and CaseSensitivity, and define which report parameters pass values to query parameters. When you run the query, you see a result set from the data source. The columns in the result set map to the fields in the dataset. When the report is processed, report items bound to the dataset fields display the retrieved or calculated data. A report processes only the first result set retrieved by any query.
Specifying a Query
For a SQL Server data source, you can use the text-based query designer or the graphical query designer to help specify a query for your dataset. When you specify the query for a new dataset, the graphical query designer opens by default. If the existing dataset query is too complex to view in the graphical query designer, the text-based query designer opens. Both query designers support the following query types: Text and StoredProcedure. You specify the type of query in the Dataset Properties dialog box before you open the query designer.
For query type Text, if you include query parameters in your query text, Reporting Services creates corresponding report parameters. For query type StoredProcedure, if your stored procedures has input parameters, Reporting Services creates corresponding report parameters. For more information about query parameters and report parameters, see Using Query Parameters later in this topic.
For more information about each query designer, see Text-based Query Designer User Interface and Graphical Query Designer User Interface.
Using Query Type Text
When the query designer opens in Text mode, you can type Transact-SQL commands to define the data in a dataset. For example, the following Transact-SQL query selects all the names of all employees.
SELECT FirstName, LastName
FROM HumanResources.Employee E INNER JOIN
Person.Contact C ON E.ContactID=C.ContactID
Click the Run button (!) on the toolbar to run the query and display the results in the Result pane.
You can also import existing queries from another report or from sql files.
Using Query Type StoredProcedure
When the query designer opens in StoredProcedure mode, you have already selected the stored procedure to use when you specified StoredProcedure in the Dataset Properties dialog box.
If you already know the name of a different stored procedure you want to run, you can replace the existing text and type the new stored procedure name in the query pane.
Reporting Services supports stored procedures that return only one set of data. If a stored procedure returns multiple result sets, only the first one is used.
If a stored procedure has a parameter with a default value, you can access that value in Reporting Services by using the DEFAULT keyword as a value for the parameter. If the query parameter is linked to a report parameter, the user can type or select the word DEFAULT in the input box for the report parameter. For more information, see Stored Procedures (Database Engine).
Using Query Parameters
For query text that contains parameters or for stored procedures with input parameters, Report Designer automatically creates corresponding report parameters in the report definition when you run the query. When the report runs, values for the report parameters are passed to the query parameters. For example, the following SQL query creates a report parameter named EmpID:
SELECT FirstName, LastName FROM HumanResources.Employee E INNER JOIN
Person.Contact C ON E.ContactID=C.ContactID
WHERE EmployeeID = @EmpID
You can manage the relationship between report parameters and query parameters by using Dataset Properties Dialog Box, Parameters. Queries with parameters that are linked to report parameters do not require the DECLARE statement.
Although report parameters are created automatically from query parameters, you manage report parameters separately in the Report Data pane.
Note
If you change the name of a query parameter or delete a query parameter, the report parameter that corresponds to the query parameter is not automatically changed or deleted. You can remove or change the order of report parameters by using buttons in the Report Data pane toolbar . For more information, see Adding Parameters to Your Report.