Retrieving Relational Data from a SQL Server Database
After creating a SQL Server data source, you can build a dataset that uses that data source to query the SQL Server database. When you create a dataset, you can indicate how you want to retrieve the data: by specifying a table, by specifying a stored procedure, or by defining a query. You can also specify properties such as Timeout and CaseSensitivity, and define which report parameters pass values to query parameters.
To view instructions about creating a dataset, click the following topic:
Using Generic Query Designer
When you create a SQL Sever dataset, Report Designer displays a generic query designer. This designer consists of two panes: a query pane and a results pane. You can use the generic designer to write queries that are not supported by the graphical interface. Unlike the graphical query designer, the generic query designer does not check query syntax or restructure the query.
Using Graphical Query Designer
Report Designer also provides a graphical query designer that you can use to design Transact-SQL queries. This view is divided into four areas, or panes.
|Diagram||Displays graphic representations of the tables in the query. Use this pane to select fields and define relationships between tables.|
|Grid||Displays a list of fields returned by the query. Use this pane to define aliases, sorting, filtering, grouping, and parameters.|
|SQL||Displays the Transact-SQL query represented by the diagram and grid panes. Use this pane to write or update a query using Transact-SQL query language.|
|Result||Displays the results of the query. To run the query, right-click in any pane, and then click Run.|
Changing information in any of the first three panes will affect the other two. For example, adding a table in the diagram pane will automatically add the table to the Transact-SQL query in the SQL pane. Adding a field to the query in the SQL pane will automatically add the field to the list in the grid pane and update the table in the diagram pane.
To perform actions within a certain pane, such as adding a table to the diagram pane, right-click within the pane and then click the desired menu item.
Note If you create or update a query using the query designer, but do not switch to Layout view before saving the report, your changes to the query may not be saved. To ensure that your query is saved, switch to Layout view before saving the report.
Using Query Parameters
If your query contains parameters, Report Designer automatically creates corresponding report parameters in the report 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 Employee 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. Also, 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 Using Parameters in a Report.
Using Stored Procedures
You can use stored procedures to return data in a dataset. To do this, set the command type for the dataset to StoredProcedure, and then provide the name of the stored procedure. Reporting Services supports stored procedures that return only one set of data.
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.