Jaa

Defining Report Datasets for a SQL Server Relational Database

  • Artikkeli

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 Datasets window 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 evaluated when the query runs. You can also create filters to limit the data at the source before it is sent to a report. For instructions about defining a dataset, see Report Data How-to Topics.

There are two query designers for relational data sources: a generic query designer and a graphical query designer. The generic query designer opens by default. Use the generic 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.

To open the query designers in Report Designer, select the Data tab. To switch from one query designer to the other, click the Generic Query Designer (Icon of the Generic Query Designer button) toggle button on the Data view toolbar. For more information and examples, see SQL Server Query Designer User Interface.

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.

Query designers for relational data have the following three command types:

  • Text type accepts Transact-SQL statements.
  • StoredProcedure type accepts calls to stored procedures on the data source. This mode does not support output parameters.
  • TableDirect type accepts the name of a table on the data source and returns all columns for the table. Not all data providers support TableDirect.

Using Query Text

You can use Transact-SQL to define the data in a dataset. To do this, set the command type for the dataset to Text and enter the Transact-SQL text by typing directly into the SQL pane.

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.

Because this command mode accepts Transact-SQL statements, you can also run a stored procedure from this mode. For example, the following SQL statement calls the AdventureWorks stored procedure uspGetEmployeeManagers.

EXEC uspGetEmployeeManagers '1'

Using Stored Procedures

You can use stored procedures to define the data in a dataset. To do this, you can either execute the stored procedure from the Query pane or set the command type for the dataset to StoredProcedure and select it from a drop-down list. If you are in the graphical query designer, click the Command type drop-down list and see the available stored procedures from the data source. If you are in the generic query designer and already know the name of the stored procedure you want to run, enter its 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 tied 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 TableDirect

You can easily return all the columns for a single table to define the data in a dataset. To do this, set the command type to TableDirect and select a table from the drop-down list. If the table has a schema on the data source, verify the schema is included in the table name. If not, you can type it in.

For example, enter the following command in the Query pane in Command type TableDirect. This command retrieves all the data from the Person.Contact table for the AdventureWorks database.

Person.Contact

Click the Run button on the toolbar to run the command and display the results in the Result pane.

Note

The Microsoft SQL Server data source type does not support TableDirect. To use this mode, select data source type OLE DB, and then the Microsoft OLE DB Provider for SQL Server.

Using Query Parameters

If your query contains parameters, Report Designer automatically creates corresponding report parameters in the report definition when you type 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 on the Parameters tab of the Dataset dialog box. Queries with parameters that are tied 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 layout view.

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 the report parameter by using the Report Parameters dialog box. For more information, see Working with Parameters in Reporting Services.

To Create a Dataset

  1. On the Data tab, from the Dataset drop-down list, select <New Dataset>.

  2. On the Dataset dialog box, from the Data source drop-down list, select New Data Source.

  3. On the Data Source dialog box, from the Type drop-down list, select Microsoft SQL Server.

  4. Specify a connection string that works with your SQL Server data source.
    Contact your database administrator for connection information. The following connection string example specifies the sample AdventureWorks database on the local client.

    Data Source=localhost;Initial Catalog=AdventureWorks

  5. Set the database credentials in the Data Source dialog box on the Credentials tab.

  6. Click OK.

For more information, see Connecting to a Data Source.

See Also

Tasks

How to: Create a Dataset (Report Designer)

Concepts

Defining Report Datasets
Query Design Tools in Reporting Services
Connecting to a Data Source

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • TableDirect.
  • To Create a Dataset subtopic.
Changed content: