Pass a Dashboard Parameter to a Custom SQL Query

• Jul 09, 2021
• 4 minutes to read

This topic shows how to pass the dashboard parameter to a custom SQL query using the WinForms Dashboard Designer.

A use of custom SQL queries is disabled by default. To allow users to specify custom SQL queries in the Data Source wizard/Query Editor, set the SqlWizardSettings.EnableCustomSql property exposed by the DashboardDataSourceWizardSettings class to true. Add the following code to allow custom SQL queries:

C#

dashboardDesigner1.DataSourceWizard.SqlWizardSettings.EnableCustomSql = true;

VB.NET

dashboardDesigner1.DataSourceWizard.SqlWizardSettings.EnableCustomSql = True

The dashboard is already connected to the SalesPerson view of the Northwind database. Follow the steps below to dynamically select data from the database (for example, according to the specified order year):

1. Create a new dashboard parameter by clicking the Parameters button in the Home ribbon tab. In the invoked Parameters dialog, specify the following settings.

   

   • Description - Specify the parameter’s description displayed to end-users as ‘Select year:’.
   • Look-Up Settings - Specify the parameter’s look-up editor settings as Static List. Then, click the ellipsis button next to Values and add ‘1996’, ‘1997’ and ‘1998’ parameter values.
   • Type - Specify the parameter’s type as String.
   • Value - Specify the default parameter value as ‘1996’.
   • Name - Specify the parameter name as ‘yearParameter’.

   Click OK to add the created parameter to the dashboard.

2. To pass the created dashboard parameter to the SQL query, click the Edit button in the Query group on the Data Source ribbon tab. This invokes the Query Editor dialog containing the SQL query used to select data.

   

   

   Modify the SQL String in the following way…

   SQL

   select * from SalesPerson where OrderDate between @startDate and @endDate
   

   …and click Next.

3. On the next page, use the Add button to add two new query parameters and specify their settings in the following way.

   

   • Name - Specify parameter names as ‘@startDate’ and ‘@endDate’.
   • Type - Specify the parameter’s types as String.
   • Expression - Enable the checkbox to use an expression to specify a parameter value.

   • Value - Select Expression Editor… to invoke the Expression Editor and specify the following expressions:

     [Parameters.yearParameter] + ‘/01/01’ (for @startDate);

     [Parameters.yearParameter] + ‘/12/31’ (for @endDate).

     

4. Click the Preview… button in the Query Editor to make sure that returned records correspond to the default parameter value (‘1996’).

You can now dynamically change a parameter value to visualize only selected year’s data.

Example

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.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;
        }
    }
}

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