dashboard-113947-winforms-dashboard-winforms-designer-create-dashboards-in-the-winforms-designer-providing-data-sql-data-source-use-query-parameters.md
A query parameter holds an external value that is inserted into an SQL statement before query execution. This value can be static or generated dynamically by an associated expression.
The query parameter value is inserted into the resulting SQL query string in the @QueryParameterName placeholder’s position.
Note
Various SQL extensions can require a special character as the variable’s first character. For instance, this can be the “at” sign (@) for Transact-SQL or the colon (:) for PL/SQL. Insert the required character before the parameter name within the custom SQL query.
In dashboards, query parameters are used in the following scenarios:
To filter data on the data source level.
To bind a dashboard to a stored procedure an SQL data source provides.
Use the Parameters property of the dashboard’s SqlDataSource query to access query parameters created within the Query Builder or generated for the data source’s stored procedure.
The Query Editor‘s second page contains the added query parameters and its settings.
The following properties are available for each query parameter:
NameSpecifies the query parameter’s name.TypeSpecifies the parameter value’s data type.ExpressionSpecifies whether an expression is used to specify a parameter value.Value
Specifies the parameter value.
You can specify a static value according to the selected value’s data type. Alternatively, construct an expression to generate parameter values dynamically.
If the Expression check box is enabled, you can select a dashboard parameter and map it to the query parameter or specify an expression in the Expression Editor dialog.
Use the Add / Remove buttons to add or remove query parameters.
Click the Preview… button to preview the query result. Click Finish to complete query modification.
You can also access query parameters and their settings through the Query Builder by clicking the Edit Parameters… button.
Below, you can see how to specify a value for a query parameter.
Choose a query parameter’s value type and set a static value in the Value column according to the selected type.
To change the query parameter value dynamically, bind it to the corresponding dashboard parameter or create an expression. To do this, in the query parameter’s settings, select the Expression check box. Then, expand the Value property’s drop-down list and select the option.
Use the following options to calculate the query parameter value dynamically:
Existing Dashboard ParameterIn the drop-down list, select an existing dashboard parameter (created earlier) to map the dashboard parameter to the query parameter.New Dashboard Parameter
Select the Add Dashboard Parameter option from the drop-down list.
In the invoked Dashboard Parameter dialog, specify the dashboard parameter’s settings. Specify the dashboard parameter type according to the type of the corresponding query parameter. Click OK to save the dashboard parameter.
ExpressionSelect the Expression Editor option from the drop-down list. In the invoked Expression Editor, specify the expression.
You can create a custom SQL query with a query parameter and bind it to a dashboard parameter to change the value dynamically.
To do this, follow the steps below:
In the Query Editor , specify a custom SQL query in the SQL String editor.
On the next page of the Query Editor , click Add to create a query parameter.
Specify the query parameter’s settings.
Click Finish to add the query.
For information on how to bind a query parameter to a multi-value dashboard parameter in a custom SQL query, refer to the following section: Pass a Multi-Value Dashboard Parameter Value to a Custom SQL Query.
The following example shows how to create a new dashboard parameter and bind it to a custom SQL query parameter in code.
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[] { "2014", "2015", "2016" };
DashboardParameter yearParameter = new DashboardParameter("yearParameter",
typeof(string), "2015", "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;
}
}
}
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() { "2014", "2015", "2016" }
Dim yearParameter As New DashboardParameter("yearParameter", GetType(String), "2015", "Select year:", True, staticSettings)
dashboard.Parameters.Add(yearParameter)
Dim dataSource As DashboardSqlDataSource = DirectCast(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
To bind a stored procedure parameter to a dashboard parameter, follow the steps below:
Select a stored procedure in the Query Editor :
On the next page of the Query Editor , select a query parameter and specify its settings.
Click Finish to apply the specified settings.
For information on how to bind a query parameter to a multi-value dashboard parameter in a stored procedure, refer to the following section: Pass a Multi-Value Dashboard Parameter Value to a Stored Procedure.
The following sections show how to pass a multi-value dashboard parameter to a SELECT SQL query, custom SQL query, and stored procedure.
To map a multi-value dashboard parameter to a query parameter, follow the steps below:
Create a dashboard parameter. In the settings, enable the Allow Multiselect option and specify the Look-Up settings.
In the Query Editor , run the Query Builder and specify the query. To specify a filter criteria, click the Filter… button.
In the invoked Filter Editor, specify the condition with the Is any of or Is none of operator. To compare a field value to a parameter value, click the button of the second operand, and then click the button. After that, click Select a parameter.
Click OK to apply the specified settings.
The following query selects the orders for which IDs can be found within the @OrderIdDashParam query parameter values:
For more information on how to filter SQL queries, refer to the following article: Filter Queries.
You cannot pass a multi-value parameter to a custom SQL query directly. To pass a multi-value parameter to a custom SQL query, convert the array of parameter values to a string with the JOIN() function and then split the resulting string in the query.
To map a multi-value dashboard parameter to a custom SQL query parameter, follow the steps below:
Create a dashboard parameter. In the settings, enable the Allow Multiselect option and specify the Look-Up settings.
In the Query Editor , specify a custom SQL query in the SQL String editor. The image below displays a custom query for a Microsoft SQL Server database.
On the next page of the Query Editor , click Add to create a query parameter.
Specify the query parameter’s settings.
In the Expression Editor , pass the name of the dashboard parameter to the JOIN() function.
Click Finish to add the query.
For more information about custom SQL queries, refer to the following article: Custom SQL Queries.
You cannot pass a multi-value parameter value to a stored procedure directly. To pass a multi-value parameter to a stored procedure, convert the array of parameter values to a string with the JOIN() function if you use the Microsoft SQL Server, MySQL, or Oracle database. The stored procedure code should parse the resulting string to get the dashboard parameter values.
Note
Do not pass dynamic look-up dashboard parameters to a custom query or a stored procedure with the Select All option enabled.
Create a dashboard parameter. In the settings, enable the Allow Multiselect option and specify the Look-Up settings.
In the Query Editor , select a stored procedure.
On the first page of the invoked Data Source Wizard dialog, specify whether you want to use an existing data connection or create a new data connection.
On the next page, select the Expression check box for the query parameter of the selected stored procedure. From the drop-down list, select the Expression Editor option.
In the invoked Expression Editor , pass the name of the dashboard parameter to the JOIN() function.
Click Finish to add the query.
Below are examples of how to get parameter values from the passed string within a stored procedure.
Split the string the Join() function returns into an array within the stored procedure.
create procedure [dbo].[GetOrdersByID]
@data varchar(100)
as
select * from Orders
where OrderID in (select value from STRING_SPLIT(@data,','));
Note that you can use the STRING_SPLIT function in Microsoft SQL Server 2016 and later.
You can also implement a stored procedure with a User Defined Table Type parameter. In this instance, use the CreateTable() expression function to prepare a table from values of several multi-value parameters.
The following code demonstrates how to implement a stored procedure that gets values from the table the CreateTable() function produces:
create type OrderParameters as table (value int, dt datetime);
create procedure [dbo].[get_orders]
@args OrderParameters readonly
as
select * from Orders
where OrderId in (select value from @args)
or OrderDate in (select dt from @args)
The following code demonstrates how to implement an Oracle-specific stored procedure that takes a string of parameters:
create or replace procedure get_movies(titles in varchar, rc out sys_refcursor) as
begin
open rc for
select * from movies
where title in (
select regexp_substr(titles, '[^,]+', 1, level) from dual
connect by regexp_substr(titles, '[^,]+', 1, level) is not null
);
end;
The following code demonstrates how to implement a MySQL-specific stored procedure that takes a string of parameters:
create procedure `get_movies`(in titles varchar(100))
begin
select *
from movies
where find_in_set(title, titles);
end;
The following code is the stored procedure for an IBM DB2 database that takes a string of parameters:
CREATE OR REPLACE PROCEDURE "GetOrdersByID" (data varchar(100)) READS SQL DATA DYNAMIC RESULT SETS 1
BEGIN
DECLARE cursor_1 CURSOR WITH RETURN FOR
SELECT *
FROM "Orders"
WHERE "OrderID" in (SELECT * from XMLTABLE('$d/r/i' PASSING XMLPARSE(DOCUMENT CAST('<r><i>' || replace(data , ',' , '</i><i>') || '</i></r>' as CLOB) ) as "d" COLUMNS ITEM VARCHAR(255) PATH '.'));
OPEN cursor_1;
END