Filter Data at the Data Source Level (Runtime Sample)

• Jul 20, 2023
• 3 minutes to read

This tutorial illustrates how to filter data at the report data source level report at runtime.

Create a Reporting Application

To get started with this tutorial, open an existing reporting application or create a new one from scratch. To learn how to create a reporting application on the platform of your choice, see Adding a Report to Your .NET Application.

The report created in this tutorial will be platform-agnostic, which means that you can use it later in applications created on any supported platform. See Store and Distribute Reports to learn more about storing and reusing reports.

Filter Report Data

Tip

Online Example: How to programmatically filter report data at the data source level

The following code sample illustrates how to programmatically filter report data at the data source level using a query parameter as a filter criterion. In this example, the query parameter has the Expression type, which allows for the use of an expression to dynamically calculate the parameter’s value (e.g., to map the query parameter to the value of a report parameter).

XtraReport1.vb

Imports DevExpress.DataAccess
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
Imports DevExpress.XtraReports.Configuration
Imports DevExpress.XtraReports.UI
' ...
Public Sub New()
    InitializeComponent()
    BindToData()
End Sub

Private Sub BindToData()
    ' Create a data source with the required connection parameters.   
    Dim connectionParameters As New Access97ConnectionParameters("../../Data/nwind.mdb", "", "")
    Dim ds As New SqlDataSource(connectionParameters)

    ' Create a query to access fields of the Products data table. 
    Dim query As SelectQuery = SelectQueryFluentBuilder.AddTable("Products").SelectColumns("CategoryID", "ProductName").Build("Products")

    ' Add a query parameter to be used as a criterion for data source level data filtering.
    ' In this example the query parameter has the Expression type and contains
    ' a simple expression that references a value of a report parameter named "catID".
    Dim parameter As New QueryParameter() With { _
        .Name = "catID", _
        .Type = GetType(Expression), _
        .Value = New Expression("?catID", GetType(System.Int32)) _
    }
    query.Parameters.Add(parameter)
    query.FilterString = "CategoryID = ?catID"

    ds.Queries.Add(query)

    ' Assign the data source to the report.
    Me.DataSource = ds
    Me.DataMember = "Products"

    ' Bind report controls to appropriate data fields.
    xrLabel1.ExpressionBindings.Add(New ExpressionBinding("BeforePrint", "Text", "[CategoryID]"))
    xrLabel2.ExpressionBindings.Add(New ExpressionBinding("BeforePrint", "Text", "[ProductName]"))
End Sub

XtraReport1.cs

using DevExpress.DataAccess;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
using DevExpress.XtraReports.Configuration;
using DevExpress.XtraReports.UI;
// ...
public XtraReport1() {
    InitializeComponent();
    BindToData();
}

private void BindToData() {
    // Create a data source with the required connection parameters.   
    Access97ConnectionParameters connectionParameters =
        new Access97ConnectionParameters("../../Data/nwind.mdb", "", "");
    SqlDataSource ds = new SqlDataSource(connectionParameters);

    // Create a query to access fields of the Products data table. 
    SelectQuery query = SelectQueryFluentBuilder
        .AddTable("Products")
        .SelectColumns("CategoryID", "ProductName")
        .Build("Products");

    // Add a query parameter to be used as a criterion for data source level data filtering.
    // In this example the query parameter has the Expression type and contains
    // a simple expression that references a value of a report parameter named "catID".
    QueryParameter parameter = new QueryParameter() {
        Name = "catID",
        Type = typeof(Expression),
        Value = new Expression("?catID", typeof(System.Int32))
    };
    query.Parameters.Add(parameter);
    query.FilterString = "CategoryID = ?catID";

    ds.Queries.Add(query);

    // Assign the data source to the report.
    this.DataSource = ds;
    this.DataMember = "Products";

    // Bind report controls to appropriate data fields.
    xrLabel1.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[CategoryID]"));
    xrLabel2.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[ProductName]"));
}

Preview and Publish the Report

Your report is now ready to be generated. Create a Print Preview to view the results.