How to: Filter an SQL Query at Runtime

Mar 31, 2018
2 minutes to read

The following example shows how to filter an SQL query at runtime using the ASPxDashboard.CustomFilterExpression event. The CustomFilterExpressionEventArgs.TableName event parameter is used to check the name of the query that should be filtered. The CustomFilterExpressionEventArgs.FilterExpression property specifies the required filter criteria.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/aspxdashboard-how-to-filter-an-sql-query-at-runtime-t479085.

<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="WebForm1.aspx.vb" 
         Inherits="ASPxDashboard_CustomFilterExpression.WebForm1" %>

<%@ Register Assembly="DevExpress.Dashboard.v17.1.Web, Version=17.1.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.DashboardWeb" TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="position: absolute; top: 0; left: 0; right: 0; bottom: 0;">
        <dx:ASPxDashboard ID="ASPxDashboard1" runat="server" Width="100%" Height="100%" 
            oncustomfilterexpression="ASPxDashboard1_CustomFilterExpression" 
            WorkingMode="Viewer">
        </dx:ASPxDashboard>
    </div>
    </form>
</body>
</html>

Imports System
Imports DevExpress.DashboardWeb
Imports DevExpress.Data.Filtering
Imports DevExpress.DashboardCommon

Namespace ASPxDashboard_CustomFilterExpression
    Partial Public Class WebForm1
        Inherits System.Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            ASPxDashboard1.SetDashboardStorage(New DashboardFileStorage("~/App_Data/Dashboards"))
        End Sub

        Protected Sub ASPxDashboard1_CustomFilterExpression(ByVal sender As Object, _
                                                            ByVal e As CustomFilterExpressionWebEventArgs)
            If e.DataSourceName = "SQL Data Source 1" AndAlso e.TableName = "Invoices" Then
                e.FilterExpression = New BinaryOperator("CustomerID", "AROUT", BinaryOperatorType.Equal)
            End If
        End Sub
    End Class
End Namespace

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" 
         Inherits="ASPxDashboard_CustomFilterExpression.WebForm1" %>

<%@ Register Assembly="DevExpress.Dashboard.v17.1.Web, Version=17.1.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.DashboardWeb" TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="position: absolute; top: 0; left: 0; right: 0; bottom: 0;">
        <dx:ASPxDashboard ID="ASPxDashboard1" runat="server" Width="100%" Height="100%" 
            oncustomfilterexpression="ASPxDashboard1_CustomFilterExpression" 
            WorkingMode="Viewer">
        </dx:ASPxDashboard>
    </div>
    </form>
</body>
</html>

using System;
using DevExpress.DashboardWeb;
using DevExpress.Data.Filtering;
using DevExpress.DashboardCommon;

namespace ASPxDashboard_CustomFilterExpression {
    public partial class WebForm1 : System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e) {
            ASPxDashboard1.SetDashboardStorage(new DashboardFileStorage("~/App_Data/Dashboards"));
        }

        protected void ASPxDashboard1_CustomFilterExpression(object sender, CustomFilterExpressionWebEventArgs e) {
            if (e.DataSourceName == "SQL Data Source 1" && e.TableName == "Invoices") {
                e.FilterExpression = new BinaryOperator("CustomerID", "AROUT", BinaryOperatorType.Equal);
            }
        }
    }
}

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode