How to: Pass a Dashboard Parameter to a Custom SQL Query in Code

• Mar 31, 2018
• 2 minutes to read

The following example demonstrates how to create a new dashboard parameter and pass it to a custom SQL query.

In this example, the dashboard parameter is passed to the custom SQL query parameter’s expression. This allows you to dynamically change the query passed to the server.

View Example

Form1.vb

Imports DevExpress.DashboardCommon
Imports DevExpress.XtraEditors
Imports DevExpress.DataAccess
Imports DevExpress.DataAccess.Sql

Namespace Dashboard_ParametersAndCustomSQL
    Partial Public Class Form1
        Inherits XtraForm

        Public Sub New()
            InitializeComponent()

            Dim dashboard As New Dashboard()
            dashboard.LoadFromXml("..\..\Data\Dashboard.xml")

            ' Creates a new dashboard parameter.
            Dim staticSettings As New StaticListLookUpSettings()
            staticSettings.Values = New String() { "1994", "1995", "1996" }
            Dim yearParameter As New DashboardParameter("yearParameter", GetType(String), "1995", _
                                                        "Select year:", True, staticSettings)
            dashboard.Parameters.Add(yearParameter)

            Dim dataSource As DashboardSqlDataSource =
                CType(dashboard.DataSources(0), DashboardSqlDataSource)
            Dim salesPersonQuery As CustomSqlQuery =
                CType(dataSource.Queries(0), CustomSqlQuery)
            salesPersonQuery.Parameters.Add(New QueryParameter("startDate", GetType(Expression), _
                                            New Expression("[Parameters.yearParameter] + '/01/01'")))
            salesPersonQuery.Parameters.Add(New QueryParameter("endDate", GetType(Expression), _
                                            New Expression("[Parameters.yearParameter] + '/12/31'")))
            salesPersonQuery.Sql =
                "select * from SalesPerson where OrderDate between @startDate and @endDate"

            dashboardViewer1.Dashboard = dashboard
        End Sub
    End Class
End Namespace

Form1.cs

using DevExpress.DashboardCommon;
using DevExpress.XtraEditors;
using DevExpress.DataAccess;
using DevExpress.DataAccess.Sql;

namespace Dashboard_ParametersAndCustomSQL {
    public partial class Form1 : XtraForm {
        public Form1() {
            InitializeComponent();

            Dashboard dashboard = new Dashboard();
            dashboard.LoadFromXml(@"..\..\Data\Dashboard.xml");

            // Creates a new dashboard parameter.
            StaticListLookUpSettings staticSettings = new StaticListLookUpSettings();
            staticSettings.Values = new string[] { "1994", "1995", "1996" };
            DashboardParameter yearParameter = new DashboardParameter("yearParameter", 
                typeof(string), "1995", "Select year:", true, staticSettings);
            dashboard.Parameters.Add(yearParameter);

            DashboardSqlDataSource dataSource = (DashboardSqlDataSource)dashboard.DataSources[0];
            CustomSqlQuery salesPersonQuery = (CustomSqlQuery)dataSource.Queries[0];
            salesPersonQuery.Parameters.Add(new QueryParameter("startDate", typeof(Expression), 
                new Expression("[Parameters.yearParameter] + '/01/01'")));
            salesPersonQuery.Parameters.Add(new QueryParameter("endDate", typeof(Expression), 
                new Expression("[Parameters.yearParameter] + '/12/31'")));
            salesPersonQuery.Sql = 
                "select * from SalesPerson where OrderDate between @startDate and @endDate";

            dashboardViewer1.Dashboard = dashboard;
        }
    }
}