[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]Office File API
[Collapse]Reporting
 [Expand]Product Information
 [Expand]Get Started with DevExpress Reporting
 [Expand]Create Popular Reports
 [Expand]Detailed Guide to DevExpress Reporting
 [Expand]Visual Studio Report Designer
 [Collapse]Create End-User Reporting Applications
   Cross-Platform Reporting
  [Expand]WinForms Reporting
  [Collapse]WPF Reporting
   [Expand]Print
   [Expand]Document Preview
   [Collapse]End-User Report Designer
    [Expand]Quick Start
    [Collapse]GUI
     [Collapse]Interface Elements
       Toolbar
       Control Toolbox
       Field List
       Report Explorer
       Report Gallery
       Group and Sort Panel
       Properties Window
       Design Surface
       Script Editor
       Query Builder
      Document Preview
     [Expand]Report Wizard
     [Expand]Data Source Wizard
    [Expand]API and Customization
   [Expand]Application Security
   [Expand]Application Appearance
    Application Deployment
   [Expand]Localization
    End-User Documentation
  [Expand]Web Reporting
  [Expand]WCF Report Service
  [Expand]Discontinued Platforms
   Localization
   Redistribution and Deployment
 [Expand]API Reference
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Query Builder

The Query Builder provides a visual interface for constructing SQL queries used to access an SqlDataSource's tables and views.

Note

The Query Builder is not available for object, Entity Framework, and Excel data sources.

Expanded Run the Query Builder

You can invoke the Query Builder from the Report Wizard's query customization page when creating a new data-bound report or when binding an existing one to an SQL data source. On this page, click the Queries category's button to create a new query using the Query Builder.

If you use an older Report Wizard version, the query customization page allows you to create a single query. In this case, select the Query option to specify that you want to construct an SQL query and then click the Run Query Builder button.

You can also use the Query Builder to add queries to an existing SQL data source, as well as edit existing queries. Right-click the data source in the Report Explorer and select Manage Queries in the context menu.

In the invoked Manage Queries dialog, click Add to add a new query. Click an existing query's ellipsis button to edit it.

Then, click the Run Query Builder button in the invoked Data Source Wizard.

Expanded Select Tables

You can add a specific data table or view to a query by dragging the corresponding item from the list of available tables at the left onto the central area.

Enable checkboxes for the table fields that you want to include in the query result set.

Each table provides a context menu which allows you to rename the table or remove it from the query.

Click the table list at the left and start typing to search for a specific table or view.

Expanded Join Tables

You can join multiple tables within the same query. The Query Builder automatically highlights tables related to previously added tables. Drag-and-drop a subordinate table in the same way as a main table to include it in a query and automatically create an inner join relation based on a key column.

Another way to join tables is to click the button in a row corresponding to a key column.

You can customize the relationship by right-clicking it on the diagram and selecting Edit Relation in the invoked context menu. Use the Join Editor to select the join type (Left Outer or Inner), apply a logical operator (Equals to, Is less than, etc.) and column key fields.

A left outer join returns all the values from an inner join along with all values in the "left" table that do not match to the "right" table, including rows with NULL (empty) values in the key field.

You can manually join tables if they do not have a relationship at the database level. In this case, when you drag-and-drop a table onto the list of tables, the Join Editor is automatically invoked allowing you to construct a custom join relationship.

After executing the query, it returns a "flat" table composed of data records selected based on the specified join options.

Note

Although joining different tables within a single query may be required in some scenarios, creating hierarchical data sources results in better performance (master-detail reports are generated faster than similar-looking reports created by grouping "flat" data sources).

Expanded Edit Parameters

Click the Edit Parameters button to invoke the Query Parameters dialog which allows you to add and remove Query Parameters as well as specify parameter settings.

The following properties are available for each query parameter:

  • Name - specifies the name used to refer to a parameter.
  • Type - specifies the data type of the parameter's value.
  • Expression - determines whether the actual parameter value is static or generated dynamically.
  • Value - specifies a query parameter's actual value. If the Expression option is enabled, the parameter value is produced dynamically by calculating an associated expression, which is useful when you need to map a query parameter value to a report parameter value.

The created parameters are then available on the Configure Query Parameters wizard page.

See Query Parameters for general information on query parameters.

Expanded Filter Data

Click the Filter... button in the Query Builder to specify filter criteria. This invokes the Filter Editor which provides the following capabilities:

  • Filter Tab

    The editor contains the Filter tab allowing you to specify filter conditions for resulting data. Filter criteria can be assigned Query Parameters or bound to report parameters.

  • Group Filter Tab

    The Group Filter tab allows you to specify filter conditions for grouped and aggregated data. The second tab is disabled if data is not grouped.

  • Other Options

    You can limit the number of resulting data rows. If data is sorted, you can also specify how many rows to skip before retrieving the specified number of rows.

    Note

    Depending on the selected data provider, it may not be possible to take into account the skip setting in the provider-specific SQL string.

    Another option enables you to include only distinct values into the resulting set.

Expanded Shape Data

The Query Builder displays the column list under the data source editor which provides various shaping options.

  • Column

    Specifies the selected column.

    You can choose a column from the drop-down list or create a column expression by clicking the corresponding column's ellipsis button.

  • Table

    Specifies the table containing the selected column.

    This option indicates (All Tables) if an expression is specified for the corresponding column.

  • Alias

    Specifies a custom column name (alias).

    This option is available only for columns that are included in a query.

  • Output

    Specifies whether the column is included into the query's resulting set.

  • Sorting Type

    Specifies whether to preserve the original data records order within the column, or sort them (in ascending or descending order).

    Note

    When binding to XML files, the Query Builder does not support sorting by aggregate functions, DISTINCT and SELECT ALL statements, and custom SQL.

  • Sort Order

    This option becomes available after applying sorting to the data column records.

    It defines the priority in which sorting is applied to multiple columns (the less this number is, the higher the priority).

    For example, if column A has the sort order set to 1 and column B has it set to 2, the query is first sorted by column A and then by the column B.

    Changing this setting for one column automatically updates other columns' sort order to avoid a priority conflict.

  • Group By

    Specifies whether the query's result set should be grouped by this column.

  • Aggregate

    Specifies whether the column's data records should be aggregated.

    The following aggregate functions are supported:

    • Count
    • Max
    • Min
    • Avg
    • Sum
    • Count Distinct
    • Avg Distinct
    • Sum Distinct

    Applying any of these functions to a column discards individual data records from the query result set, which only includes the aggregate function result.

Note

You should apply aggregation/grouping to either all columns or to none of them.

Expanded Enable Custom SQL Editing

If custom SQL editing is enabled, the Query Builder contains the Allow Edit SQL checkbox. Selecting this option disables the Query Builder's visual features and allows users to specify the custom SQL string manually.

Important

Enabling custom SQL in the Report Designer exposes your databases to the risk of possible SQL injections. Custom SQL queries are not validated before their execution and may contain potentially harmful instructions. See Data Access Security for more information.

Expanded Preview Results

You can preview the query execution result in the form of a tabular data sample by clicking the Preview Results button.

This opens the Data Preview window displaying the query result set limited to the first 1000 data records.

Expanded Query Builder Light Mode

The Query Builder Light mode allows you to specify custom names for tables and columns. The Query Builder does not display the resulting SQL query and disables column expressions in this mode because these options use actual data member names.

See Use the Query Builder Light Mode for information on how to enable the Query Builder Light mode.

Is this topic helpful?​​​​​​​