Home / Community & Support / Knowledge Base / Telerik Reporting / Designing Reports / Using data source with parameters in Telerik Reporting

Using data source with parameters in Telerik Reporting

Article Info

Rating: 4

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#:
private void MyReport_NeedDataSource(object sender, System.EventArgs e)
{
    // Take the Telerik.Reporting.Processing.Report instance
    Telerik.Reporting.Processing.Report report = (Telerik.Reporting.Processing.Report)sender;
     
    // Transfer the processing Parameter value to the parameter of the select command         
    this.sqlDataAdapter1.SelectCommand.Parameters["@ManagerID"].Value = report.Parameters["ManagerID"].Value;
 
    // and set the adapter as it's DataSource         
    report.DataSource = this.sqlDataAdapter1;
}

VB:
Private Sub MyReport_NeedDataSource(sender As Object, e As System.EventArgs)
    ' Take the Telerik.Reporting.Processing.Report instance
    Dim report As Telerik.Reporting.Processing.Report = DirectCast(sender, Telerik.Reporting.Processing.Report)
 
    ' Transfer the processing Parameter value to the parameter of the select command         
    Me.sqlDataAdapter1.SelectCommand.Parameters("@ManagerID").Value = report.Parameters("ManagerID").Value
 
    ' and set the adapter as it's DataSource         
    report.DataSource = Me.sqlDataAdapter1
End Sub

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#:
public MyReport()   
{   
  /// <summary>   
  /// Required for telerik Reporting designer support   
  /// </summary>   
  InitializeComponent();   
   
  this.DataSource = null;   
}

VB:
Public Sub New()   
    InitializeComponent()   
     
    Me.DataSource = Nothing   
End Sub

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).

Comments

If you'd like to comment on this KB article, please, send us a Support Ticket.
Thank you!

Please Sign In to rate this article.