Bind a Report to a Stored Procedure
- 4 minutes to read
This tutorial demonstrates how to bind a report to a stored procedure provided by an SQL data source. Additionally, it shows how to map report parameters to stored procedure parameters, producing a convenient interface for user-level data shaping.
The document consists of the following sections.
Bind to a Stored Procedure
To bind to a stored procedure, do the following.
Click the report’s smart tag. In the invoked actions list, expand the drop-down menu for the DataSource property and click Add Report Data Source…
On the first page of the invoked Data Source Wizard, select Database and click Next.
The next page allows you to specify whether you want to use an existing data connection or create a new data connection with custom parameters. Select the first option to create a new connection and click Next.
On the next page, you can define a custom connection string or select from the list of supported data providers. Depending on the data provider selected, it may be necessary to specify additional connection options (such as authentication type and database name) on this page.
To proceed to the next wizard page, click Next.
Click Next on the following page to save the created connection string to the configuration file.
On the next page, you can choose which tables, views and/or stored procedures to add to the report. Expand the Stored Procedures category, select the required stored procedure from the list of available stored procedures and click Next.
For each stored procedure parameter, a corresponding query parameter is created, which you can associate with a static value, an expression or a report parameter. In this document section, static values are used.
On the next page, specify values for all created query parameters. To preview the result of the stored procedure execution with the specified parameters, click the Preview button and select a query.
The following image demonstrates the Data Preview displaying the resulting data sample. Click Close to exit the preview.
Click Finish to exit the wizard.
Pass Parameters to the Stored Procedure
To map report parameters to parameters of a stored procedure, do the following.
To configure the parameters of the stored procedure query, select the data source in the Report Explorer, expand its SqlDataSource.Queries collection property in the Properties window and click the ellipsis for the SqlQuery.Parameters property of the required query.
The Query Parameters dialog will be invoked. For each stored procedure parameter that you want to map to a report parameter, activate the Expression check box, which allows the use of expressions to calculate the parameter value. Next, expand the drop-down list for the Value property and select New Report Parameter to create a new report parameter. A reference to this parameter will be used as a simple expression to generate the query parameter value, which will be passed to the stored procedure.
Note
At present, mapping query parameters to multi-value report parameters is not supported. For a possible workaround, see the following example: Passing Parameter Values to a Data Source Query.
In the invoked Report Parameter dialog, specify the required report parameter settings. Be sure to specify the report parameter type according to the type of the respective query parameter. Click OK to exit the dialog.
Alternatively, you can map a query parameter to a report parameter that already exists in a report. To do this, expand the drop-down list for the Value property in the Query Parameters dialog and select the parameter that you want to use.
You can also create a complex expression to generate a query parameter value. To do this, expand the drop-down list for the Value property in the Query Parameters dialog and select Expression Editor. Then, construct the required expression in the invoked dialog.
View the Result
A parameterized report bound to a stored procedure is now ready. To view the result, drop the data fields that you want to display in your report onto appropriate report bands and switch to the Preview Tab of the Visual Studio Report Designer. Enter values for the required report parameters in the Parameters panel, and click Submit.
