Use Report Parameters_usereport-优快云博客

本文链接：https://blog.youkuaiyun.com/happymagic/article/details/103289528

https://docs.devexpress.com/XtraReports/4812/detailed-guide-to-devexpress-reporting/shape-report-data/use-report-parameters

The topics in this section describe how to use parameters in a report:

Parameters Overview

Describes how to create and use report parameters.
Request and Pass Report Parameter Values

Illustrates how to assign the default and custom values to a report's parameters and describes the editors that are used to request these values in a Print Preview.
Multi-Value and Cascading Report Parameters

Explains how to configure report parameters so that they accept multiple values, and how to filter a parameter's values based on another parameter's value.
Date Range Parameters

Describes how to create date range parameters to filter report data by a specific time period.
Custom Report Parameters

Demonstrates how to create custom type parameters for a report.
Query Parameters

Details how to link report parameters with query parameters defined in your report's data source.

https://docs.devexpress.com/XtraReports/5158/create-end-user-reporting-applications/winforms-reporting/print-preview/api-and-customization

API and Customization

Jul 31, 2018

The topics in this section describe the Print Preview's customization options in WinForms applications:

Query Parameters

Aug 13, 2019
5 min to read

This document provides general information on query parameters and describes common ways of utilizing parametrized SQL queries to filter data on the data source level.

#Query Parameters Overview

A query parameter holds an external value that is inserted into an SQL statement before query execution. This value can be either static or dynamically generated by an associated expression.

The query parameter value is inserted into the resulting SQL query string in the "@QueryParameterName" placeholder's position.

In XtraReports, query parameters are used in the following scenarios:

When filtering report data on the data source level using the Query Builder.

The Query Builder helps you construct SQL queries when creating a new data-bound report or binding an existing report to an SQL data source,

... or when adding queries to an existing SQL data source or editing existing queries.

You can filter the constructed queries using query parameters. Press the Edit Parameters... button to invoke the Query Parameters dialog.

Press the Filter... button to invoke the Filter Editor and filter data using the created query parameters.

The criteria based on the specified query parameters are added as an SQL statement's WHERE part.

Refer to the Filter Data at the Data Source Level topic for details on filtering using query parameters.
When binding a report to a stored procedure provided by an SQL data source.

The Report Wizard, as well as the 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 query parameters in the next Configure query parameters and preview the result page.

You can access query parameters using the SqlQuery.Parameters property of the query the report's SqlDataSource component exposes. These parameters include the ones you created within the Query Builder or that were generated for the data source's stored procedure. You can also access the query’s filter string using the SelectQuery.FilterString property. This filter string includes the filter that you specified in the Query Builder.

QueryParameters_FilteringScenario

You can add new query parameters in the Query Parameters dialog and modify the filter within the Filter Editor.

#Configure Query Parameters

The following properties are available for each query parameter:

Name - specifies the parameter's name.
Type - specifies the parameter value's data type.
Expression - determines whether the actual parameter value is static or generated dynamically.
Value - determines the query parameter's actual value. If the Expression option is enabled, the actual parameter value is produced dynamically by calculating an associated expression. This is useful when you map the query parameter value to the report parameter value. Refer to the Provide the Query Parameter Value document section for more information.

#Provide the Query Parameter Value

Below, you can see how a value is specified for a query parameter within the Data Source Wizard's page. You can also specify query parameter values in the Report Wizard or the Query Parameters dialog in the same way.

Specify a static value

Choose a query parameter's value type and set a static value to the Value property according to the selected type.

See Expression Constants, Operators, and Functions for the list of constants that you can use as parameter values.
Provide a dynamic value

Activate the Expression checkbox for a parameter.

The following three options are used to dynamically calculate the parameter's actual value:
- Create a complex expression by expanding the Value property's drop-down list and selecting Expression Editor.
  
  Construct an expression in the invoked Expression Editor.
- Map a new report parameter to a query parameter by expanding the Value property's drop-down list and selecting New Report Parameter.
  
  Specify report parameter settings in the invoked Report Parameter dialog. Remember to specify the report parameter type according to the type of the corresponding query parameter. Click OK to exit the dialog.
- Map a report parameter that already exists in a report to a query parameter by expanding the Value property's drop-down list and selecting the parameter you want to use. An appropriate expression string is generated automatically.

#Pass a Multi-Value Parameter Value to a Query

You can map multi-value parameters to query parameters. For instance, the following query selects the orders whose IDs can be found within the values the @OrderID query parameter provides.

MultiValueParameterInQuery

#Pass a Multi-Value Report Parameter Value to a Stored Procedure

You cannot pass a multi-value parameter value to a stored procedure directly. Use one of the following expression functions:

Use the Join() expression function to convert the array of parameter values to a string if you use MS SQL Server, MySQL or Oracle database systems.

Below are examples of how to get parameter values from the passed string within a stored procedure.
- MS SQL Server
  
  Split the string the Join() function returns into an array within the stored procedure.
  - SQL
```
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 MSSQL server 2016 and later.
- Oracle
  
  The following code demonstrates how to implement an Oracle-specific stored procedure that takes a string of parameters:
  - SQL
```
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;
```
- MySQL
  
  The following code demonstrates how to implement a MySQL-specific stored procedure that takes a string of parameters:
  - SQL
```
create procedure `get_movies`(in titles varchar(100))
begin
select *
from movies
where find_in_set(title, titles);
end;
```
Use the CreateTable() expression function to prepare a table using 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:
- SQL
```
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)
```