Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Collapse]Reporting
 [Expand]Product Information
 [Collapse]Creating Reports in Visual Studio
  [Expand]Adding a Report to Your .NET Application
  [Expand]Getting Started with DevExpress Reporting
  [Collapse]Detailed Guide to DevExpress Reporting
   [Expand]Storing and Distributing Reports
   [Expand]Providing Data to Reports
   [Collapse]Shaping Report Data
    [Expand]Data Filtering
    [Expand]Grouping and Sorting
    [Expand]Shaping Data using Binding Expressions
    [Expand]Shaping Data using Legacy Data Bindings
    [Expand]Using Calculated Fields
    [Collapse]Using Report Parameters
      Parameters Overview
      Creating Multi-Value and Cascading Report Parameters
      Requesting and Passing Report Parameter Values
      Creating Custom Report Parameters
      Passing Parameter Values to a Data Source Query
   [Expand]Report Navigation, Interactivity and Appearance
   [Expand]Reporting API
  [Expand]Detailed Guide to Visual Studio Report Designer
 [Expand]Creating End-User Reporting Applications
 [Expand]API Reference
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Passing Parameter Values to a Data Source Query

This document provides general information on query parameters and describes common ways of utilizing parametrized SQL queries to perform simple and straightforward data source level data shaping.

The document consists of the following sections.

Expanded Query Parameters Overview

A query parameter holds an external value that is inserted into a SQL statement before query execution. This value can be either static or dynamically generated by an associated expression.

The query parameter value is inserted into the resulting SQL query string in the position of the corresponding placeholder, which has the "@QueryParameterName" form.

In XtraReports, query parameters are typically used in the following scenarios.

The image below demonstrates a SQL string with parameter placeholders produced by the Query Builder.

Expanded Create and Configure Query Parameters

To create and configure query parameters, do the following.

  1. When creating a new data-bound report using the Report Wizard or binding an existing report to a database, go to the query customization page. On this page, click the button for the Queries category to create a new query using the Query Builder.

    You can also open the query customization page to modify an existing data source as described in the Query Builder document.

  2. In the Query Builder, construct the required query and click the Edit Parameters button.

  3. The invoked Query Parameters dialog allows you to add and remove query parameters as well as specify parameter settings.

    For each query parameter, the following properties are available.

    • Name - specifies the name used to refer to a parameter.

    • Type - specifies the data type of the parameter value.

    • Expression - determines whether the actual parameter value is static or generated dynamically.

    • Value - determines the actual value of the query parameter. If the Expression option is enabled, the actual parameter value is produced dynamically by calculating an associated expression. This is particularly useful when you need to map the query parameter value to the value of a report parameter. For more information, refer to the Provide the Query Parameter Value document section.

  4. To filter data at the data source level based on query parameter values, click the Filter button, and in the invoked Filter Editor, construct a filtering expression. To access parameters, continue clicking the icon on the right until it turns into a question mark. Then, click the parameter placeholder and select one of the available parameters.

    You can also create a new parameter by selecting Add Query Parameter. Then, in the invoked Create Query Parameter dialog, specify the parameter name and appropriate value type, and click OK.

    As a result, the query parameter will be passed to the WHERE part of the SQL string and will have the "@QueryParameterName" form.

The created query parameters will then be available on the Configure Query Parameters wizard page.

After creating a data source, you can access and manage its parameters. To do this, select the data source in the Report Explorer and go to the Properties window. Then, expand the SqlDataSource.Queries collection property and click the ellipsis for the SqlQuery.Parameters property of the required query.

This invokes the Query Parameters dialog described above.

Expanded Provide the Query Parameter Value

You can provide actual values (either static or dynamic) for query parameters right away when creating the parameters in the dedicated dialogs or do it afterwards on the Configure Query Parameters wizard page.

  • Specifying a static value

    To specify a static value for a query parameter, choose its value type and specify the Value property according to the selected type.

  • Providing a dynamic value

    To use a dynamically generated value, activate the Expression check box for the required parameter.

    Then, the following three options will be available to you, which help to dynamically calculate the parameter's actual value.

    • To create a complex expression, expand the drop-down list for the Value property and select Expression Editor.

      Construct a required expression in the invoked Expression Editor.

    • To map the query parameter to a new report parameter, expand the drop-down list for the Value property and select New Report Parameter.

      In the invoked Report Parameter dialog, specify required report parameter settings. Be sure to specify the report parameter type according to the type of the corresponding query parameter. Click OK to exit the dialog.

    • You can map a query parameter to a report parameter that already exists in a report by referring to its value from a simple expression. To do this, expand the drop-down list for the Value property in the Query Parameters and select the parameter that you want to use. An appropriate expression string will be generated automatically.

      Note

      At present, mapping query parameters to multi-value report parameters is not supported. For a possible workaround, see the next section of this document.

Expanded Pass a Multi-Value Parameter to a Query

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T352379.

This example illustrates a workaround enabling you to use multi-value parameters in a query string.

At present, query parameters cannot be directly mapped to multi-value report parameters, and this task is solved by adding a WHERE clause to the SQL string in the following way.

To dynamically update the query string using the values assigned to a report parameter (whose Parameter.MultiValue property is set to true), use the following code in the XtraReportBase.DataSourceDemanded event handler.

How would you rate this topic?​​​​​​​