xtrareports-17387-feature-guide-to-devexpress-reports-bind-reports-to-data-sql-database-specify-query-parameters.md
This topic describes the common tasks that you can do with query parameters.
A SQL query can include query parameters. A query parameter holds a value that is inserted into a SQL statement before the query is executed. The query parameter value can be static, if specified explicitly, or dynamic, when an expression is evaluated to obtain a value.
In a query string, the parameter name is preceded by the \@ symbol (Microsoft SQL Server). The following SQL string includes the pOrderId parameter:
select [OrderDetailsExtended].*
from [OrderDetailsExtended]
where [OrderDetailsExtended].[OrderID] = @pOrderId
order by [OrderDetailsExtended].[ExtendedPrice]
desc
A report retrieves data from a SQL database with the SqlDataSource object. The SqlDataSource class exposes the SqlDataSource.Queries property that returns a collection of SqlQuery objects. SqlQuery is the base class for different query types, so the Queries collection may contain the following query types:
SelectQuery A query that can be visually constructed in the Query Builder.CustomSqlQueryA query created from a SQL string.StoredProcQueryA query that uses a stored procedure to retrieve data.
The base SqlQuery.Parameters property contains a collection of query parameters.
In this scenario, a report parameter is bound to the query parameter to filter the data supplied to the report. For more information on report parameters, review the following help topic: Use Report Parameters.
Start with a report that is bound to the SQL database and already has a report parameter. Open the report in the Report Designer.
Do the following to add a parameter to the SQL query and bind the query parameter to the report parameter:
Invoke the Field List or the Report Explorer window, and right-click the data source item. Select Manage Queries in the context menu to invoke the Query Builder.
In the Query Builder window, click Edit Parameters to invoke the Query Parameters dialog.
Click Add to add a new query parameter. The default parameter name is Parameter1.
Click Filter to invoke the Filter Editor , where you can specify filter conditions that include query parameters.
Enter the filter expression:
Click OK. The filter expression is added as a SQL statement’s WHERE clause:
In this section, you will find the code that creates a query parameter and binds it to a report parameter.
SelectQuery is a query that can be visually constructed in the Query Builder. Add a new query parameter and include the report parameter name in an expression assigned to the FilterString property.
using DevExpress.DataAccess.Sql;
// ...
QueryParameter parameter = new QueryParameter() {
Name = "Parameter1",
Type = typeof(DevExpress.DataAccess.Expression),
Value = new DevExpress.DataAccess.Expression("?pOrderId", typeof(int))
};
selectQuery.Parameters.Add(parameter);
selectQuery.FilterString = "[OrderDetailsExtended.OrderID] = ?Parameter1";
Imports DevExpress.DataAccess.Sql
' ...
Private parameter As New QueryParameter() With {
.Name = "Parameter1",
.Type = GetType(DevExpress.DataAccess.Expression),
.Value = New DevExpress.DataAccess.Expression("?pOrderId", GetType(Integer))
}
selectQuery.Parameters.Add(parameter)
selectQuery.FilterString = "[OrderDetailsExtended.OrderID] = ?Parameter1"
CustomSqlQuery is a query created manually, from a SQL string. Add a new query parameter and append a filter string to the Sql property. The filter string uses a report parameter to specify the filter criteria.
using DevExpress.DataAccess.Sql;
//...
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "Order Details Extended";
QueryParameter parameter = new QueryParameter() {
Name = "Parameter1",
Type = typeof(DevExpress.DataAccess.Expression),
Value = new DevExpress.DataAccess.Expression("?pOrderId", typeof(string))
};
query.Parameters.Add(queryParameter1);
query.Sql += " WHERE \"Order Details Extended\".\"OrderID\" = ?Parameter1";
sqlDataSource1.Queries.Add(query);
Imports DevExpress.DataAccess.Sql
'...
Private query As New CustomSqlQuery()
query.Name = "Order Details Extended"
queryParameter1 = New DevExpress.DataAccess.Sql.QueryParameter()
queryParameter1.Name = "Parameter1"
queryParameter1.Type = GetType(DevExpress.DataAccess.Expression)
queryParameter1.Value = New DevExpress.DataAccess.Expression("JOIN(?pOrderIds)", GetType(String))
query.Parameters.Add(queryParameter1)
query.Sql &= " WHERE ""Order Details Extended"".""OrderID"" IN (SELECT value from STRING_SPLIT(@Parameter1,','))"
sqlDataSource1.Queries.Add(query)
Review the following help topic for a code example: StoredProcQuery.
A multi-value parameter allows a user to select multiple values and apply them to filter report data.
To use a multi-value report parameter in a query created with the Query Builder UI (the SelectQuery object), bind the query parameter to the report parameter and specify the InOperator in the filter expression. For more information on report parameters and filter operators, review the following help topics:
If you create a query from text (that is, enter the query string manually), the resulting query is a CustomSqlQuery object. It cannot process the multi-value report parameter, and you should convert the report parameter into a string, and bind the resulting string to a query parameter. The query string should use a function that translates the passed query parameter into an array of values suitable for a SQL expression. For Microsoft SQL Server 2016 and later, you can use the STRING_SPLIT function.
Note
The query that supplies a list of values to the report parameter (the dynamic lookup list) should be different from the query that supplies data to the report.
Review the following sections for step-by-step guides to two alternative methods that create a parameterized query bound to a multi-value parameter.
To create a multi-value parameter and bind it to the query, do the following:
Create a new query used to populate the list of selectable values for a report parameter. To do this, open the Field List window, right-click the data source item, and select Manage Queries in the context menu to invoke the Query Builder. Add a new query. You can also click the Clone button to clone the existing query:
Click Filter to invoke the Filter Editor :
Enter the filter expression. A new multi-value parameter is created in the interim:
The resulting filter string is shown in the following image:
The Query Builder displays the text of the parameterized query:
You can view and edit the newly created report parameter (specify the sort order) in the Report Parameters Editor window:
To create a multi-value parameter and bind it to a custom query (a query that you typed manually), do the following:
Create a new query used to populate the list of selectable values for a report parameter. To do this, open the Field List window, right-click the data source item, and select Manage Queries in the context menu to invoke the Query Builder. Add a new query. As an alternative, you can click the Clone button to clone the existing query:
Add a new multi-value parameter to the report. For more information, review the following help topic: Multi-Value Report Parameters. The image below shows the Report Parameters Editor window for a multi-value report parameter that retrieves its values from a query created in the previous step.
Open the Field List window, right-click the data source item, and select Manage Queries in the context menu to invoke the Query Builder window. Click Edit Parameters to invoke the Query Parameters dialog. Click Add to add a new query parameter. The default parameter name is Parameter1. Enable the Expression option and select Expression Editor in the Value drop-down list. Enter the following expression in the Expression Editor window:
Edit the query and use the STRING_SPLIT function (available with Microsoft SQL Server 2016 and later) to convert the query parameter into an array of values:
The Report Wizard and Data Source Wizard include the Create a query or select a stored procedure page. If you select a stored procedure, the wizard creates a query parameter for each procedure parameter and allows you to configure the created parameters on the Configure query parameters and preview the result page.
For more information, review the following help topic: Bind a Report to a Stored Procedure.
Use the query’s Parameters property to access the query parameters collection. The collection includes parameters created in the Query Builder and generated for the data source’s stored procedure. Use the SelectQuery.FilterString property to access the query’s filter string.
You can add query parameters in the Query Parameters dialog and modify the filter in the FilterString Editor.
You cannot pass a multi-value parameter value directly to a stored procedure. You must use Functions for Stored Procedures to combine parameter values into a string or table to pass them to a procedure, and retrieve them as described below.
Use the Join() expression function to convert the array of parameter values to a string if you use Microsoft SQL Server, MySQL, Oracle, or IBM DB2 database systems.
Split the string that 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
You can use the STRING_SPLIT function in Microsoft SQL Server 2016 or later.
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 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
Use the CreateTable() expression function to generate a table populated with values of several multi-value parameters.
The following code demonstrates how to get values from a passed table within a stored procedure with a user-defined table type parameter:
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)