Creating Report Datasets from a SQL Server Relational Database (Report Builder 2.0)
Report Builder 2.0 provides a data processing extension that supports report data retrieval from Microsoft SQL Server relational databases. You can retrieve report data for a report dataset from database tables, views, stored procedures, and table-valued functions.
Before you create a report dataset, you must create a data source that has the data source type Microsoft SQL Server. After you create a Microsoft SQL Server data source, you can create a dataset in the following ways:
Use the New Table or Matrix Wizard or the New Chart Wizard. You can select an existing dataset in the report or you can use the wizards to help create a different dataset and a query.
Create a dataset and a dataset query from the Report Data pane.
Specifying a Report Dataset Query
A query specifies which data to retrieve from a Microsoft SQL Server database for a report dataset. The columns in the result set for a query populate the field collection for a dataset. A report processes only the first result set retrieved by any query.
You can choose from the following options to help specify the data that you want:
Build a query interactively. Use the relational query designer that displays a hierarchical view of tables, views, stored procedures, and other database items, organized by database schema. Select columns from tables or views or specify stored procedures or table-valued functions. Limit the number of rows of data to retrieve by specifying filter criteria. Customize the filter when the report runs by setting the parameter option.
Type or paste a query. 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 relational query designer, or to enter query-based expressions.
Import an existing query from a file or report. Use the Import query button from either query designer to browse to a .sql file or .rdl file and import a query.
For more information, see Relational Query Designer User Interface (Report Builder 2.0), Text-based Query Designer User Interface (Report Builder 2.0), and How to: Retrieve Data from a SQL Server Data Source (Report Builder 2.0).
Using Query Type Text
In the text-based query designer, you can type Transact-SQL commands to define the data in a dataset. For example, the following Transact-SQL query selects the names of all employees who are marketing assistants.
SELECT
HumanResources.Employee.BusinessEntityID
,HumanResources.Employee.JobTitle
,Person.Person.FirstName
,Person.Person.LastName
FROM
Person.Person
INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID = HumanResources.Employee.BusinessEntityID
WHERE HumanResources.Employee.JobTitle = 'Marketing Assistant'
Click the Run button (!) on the toolbar to run the query and display a result set.
To parameterize this query, add a query parameter. For example, change the WHERE clause to the following:
WHERE HumanResources.Employee.JobTitle = (@JobTitle)
When you run the query, report parameters that correspond to query parameters are automatically created. For more information, see Query Parameters later in this topic.
Using Query Type StoredProcedure
You can specify a stored procedure for a dataset query in one of the following ways:
In the Dataset Properties dialog box, set the Stored Procedure option. Choose from the drop-down list of stored procedures and table-valued functions.
In the relational query designer, in the Database view pane, select a stored procedure or table-valued function.
In the text-based query designer, select StoredProcedure from the toolbar.
After you select a stored procedure or table-valued function, you can run the query. You will be prompted for input parameter values. When you run the query, report parameters that correspond to input parameters are automatically created. For more information, see Query Parameters later in this topic.
Only the first result set that is retrieved for a stored procedure is supported. 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 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 about stored procedures, see "Stored Procedures (Database Engine)" in SQL Server Books Online.
Using Query Parameters
For query text that contains parameters or for stored procedures with input parameters, corresponding report parameters are automatically created. 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 (Report Builder 2.0). 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 (Report Builder 2.0).
Modifying a Dataset
After you create a dataset, you can view the dataset and its field collection in the Report Data pane. You might also want to use the Dataset Properties dialog box to do some of the following tasks:
Edit the field collection by adding calculated fields. Calculated fields have expression-based values that are evaluated after 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.
Specify dataset properties such as Timeout, CaseSensitivity, and Collation.
Modify the query to add parameters that enable a user or a report author to customize the data for a report at run-time.
In addition, to change the way data is sorted or filtered in a dataset, see the following topics:
See Also