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

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.

´╗┐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

See Also