Using data source with parameters in Telerik Reporting

Article information
Article relates to	Telerik Reporting Q1 2008+
Created by	Milen, Telerik
Last modified	09 March, 2009
Last modified by	Steve, Telerik

HOW-TO
Use data source with parameters in Telerik Reporting

SOLUTION
Often the data which has to be retrieved for the report depends on some particular input by the user. This supposes that there is a way of getting some specific values and a way of passing them to a parameterized data source such as stored procedure or a Select command with parameters.

Generally there are two ways of getting the parameters values:

• Through your own user interface or any other means (e.g. query string).
• Through the report parameters defined in the ReportParameters collection of the report.

To pass the values using the first approach you can use the solution provided in the following video:
Design Time Support for Parameterized Queries.

With the second approach you take the advantage of using the automatic user interface which Telerik Reporting provides for entering values for its parameters. To use it the Visible property of the respective report parameters should be set to true.

To transfer the values of the ReportParameters to the parameterized query you have to use the NeedDataSource event of the report. This is the event which is fired just before the ReportProcessor starts processing the report and the best place whereyoucan initialize the parameterized data source and set it for the DataSource property of the report. For example:

C#:

VB:

In order to have full design time support (e.g. populated Data Explorer, populated Fields columns in all editors) you need to initialize the Report.DataSource with your data source object. Make sure that the data source parameters have default values, so the report designer will be able to retrieve its schema.

At runtime Report.NeedDataSource event is raised only when the report has no data source set, so you should set the Report.DataSource to null (Nothing in VisualBasic.NET):

C#:

VB:

Below is a step-by-step example how to use a stored procedure with parameters as data source (source code in C# and VB.NET is attached):

1. Add a new Class Library project to the Solution (See our Best Practices - Solution Structure).

2. Add a new Telerik Report to the project and Cancel the Report Wizard, because you cannot use a stored procedure in it.

3. From the toolbox Add SQLDataAdapter to the report (If the SQLDataAdapter component is not added to your toolbox, follow this article to add it).

4. Configure the adapter to use the desired stored procedure with parameter(s) as select command. Set default values for the parameters of the Select command, so that these values are used at design time.

5. In order to use the data source fields in design time, the report's DataSource property should be set to the dataAdapter from step #3. However on the last line of the constructor of the report you should assign null(Nothing) to the DataSource of the report, so that the NeedDataSource event is fired at runtime.

6. Using the ReportParameters collection editor, add parameters that correspond to the parameters of the procedure, and configure their UI to allow the end user to input values (see ReportParameters).

7. Add a handler to the NeedDataSource event of the report. In it get the current values of the report parameters and pass them as parameters of the select command of the data adapter. Then cast the sender parameter of the handler as Telerik.Reporting.Processing.Report and set the adapter as it's DataSource (code snippets for point 7 are written above).