When creating a query it is possible to use the Parameter object. This object is designed to send additional conditions for selecting data into a query. For example, if you need a query to use a value entered by the user each time the query is executed, you can create a query using parameters. The Parameter object can only be used with SQL data sources. These data sources are typically have the Text Query field. To insert a parameter in the query, you must click the New Parameter button. The picture below shows the toolbar, on which the New Parameter button can be found:
After clicking this button a new parameter will be created. This parameter will be displayed in the Parameters tab in the Columns panel. The picture below shows an example of the Columns panel with the Parameters tab:
Each parameter has a property with which you can change its settings. The picture below shows the panel of parameters properties:
The
Name property.
Used to change the parameter name. This feature works only for
named parameters.
For
each parameter you can specify a value that is used to populate the
parameter. The value can be an expression, const, variable, etc.
For example, {x + y}
or
{variable}.
The
Size property
provides an opportunity to change the size of the type used in the
parameter. Keep in mind that each type in the database has its own
size. Therefore, when using a query, you must specify the correct
type size. For some adapters, database size may be omitted, but
generally if the size is not specified or is incorrect, then the
queries using these parameters will be performed
incorrectly.
Use
the Type property
in order to change the parameter type. The values of the
properties are in the drop-down list, and are a list of types used
in the parameters for a particular database. It should be noted
that a list of types differs depending on the database.
Also, you must specify the parameter in the query. Here is an example of schematic position of parameters in the query:
As a rule, the @ symbol is used to specify a parameter in the query. The @ symbol is used with named parameters, i.e. after the @ symbol goes the name of the parameter. But in some databases (for example in OleDB), the @ symbol cannot be perceived by the adapter and database queries with parameters will not work. In this case, you can use unnamed parameters. For specifying unnamed parameters in the query the ? character is used. After the ? character the parameter name is not specified. In this case, the order of parameters in the Parameters tab is important. As indications of the ? characters in the query, parameters will be taken sequentially from the Parameters tab in the top-down direction. Consider the following example. Suppose there are three parameters that are specified in the query:
Since, in this case, unnamed parameters (marked with ?) are used, then, when running, the query parameters will be taken from the Parameters tab in the top-down order. The picture below schematically presents a comparison of parameters of the Parameters tab to the parameters in the query:
In this case, the parameters used in this example, can have names, but when using the ? character they play no role. Once a query to parameters is created and executed, the parameters will also be displayed in the Dictionary, in the created data source in the Parameters tab. The picture below shows an example of the Dictionary panel and placing parameters in it:
To edit a parameter separately from the data source, select the Parameter in the data dictionary and click Edit on the toolbar in the dictionary or select Edit item in the context menu of the selected parameter. After pressing the button or selecting Edit, the user will be shown the Edit Parameter dialog, in which you can edit the selected parameter. The picture below shows an example of the Edit Parameter dialog:
This field
displays the parameter Name,
which can be edited;
This field displays
the Type
of the
parameter, which can be edited;
The Dictionary
button
contains a drop-down menu that displays the structure of the data
dictionary. In this menu you can select data columns, business
objects, or system variables that will be added to the expression
of calculation of calculated data columns;
The Expression
field displays
used expressions in a query parameter, which, if necessary, can be
edited;
The Save a
Copy button saves a copy of the
edited parameter by assigning the Copy
postfix in the
parameter name.