Using Parameters

One interesting feature in TatVisualQuery component is the usage of parameters. While building filtering conditions in TatVisualQuery, end-user follows the known procedures: add a condition, choose a field which will be compared to a value, choose operator (equal to, less than, etc.), and choose the value.

However, there is an option where the end-user do not compare a field to a value, but instead compare a field to a parameter. Instead of filling a specific value, end-user types the parameter name. Using parameter feature makes it easy to later change the value to be compared to a field: instead of going there to change the condition expression, user just change parameter value.

Whenever a new parameter name is typed in filter condition, user can then create the editor that will be used to edit the parameter value. This is done in the "Parameter editors" section of TatVisualQuery component. Definitions made in parameter editors are saved in the ParamDefs property of a TatMetaSQLDef class. The ParamDefs property is a TatParamDefs class, which holds a collection of TatParamDef classes. Each TatParamDef class contains specifications of a single parameter editor. Below is the reference of TatParamDef class.

For complete information about componentes related to query parameters, see the Query Studio component reference at online help from IDE.

TatParamDef Class

TatParamDef class holds information about a single parameter defined in the query. A query parameter can be one of the following types:

  • ptFreeEdit: A simple edit component will be used to edit the parameter. User must type the parameter value.

  • ptDistinctList: A combo box will be used to edit the parameter. User can type the parameter value or choose one option from combo items. The items in combo will be automatically filled with content of database. For example, if the parameter is "Customer city", and the field "CustomerCity" will be compared to this parameter, the combo items will be filled with all possible city names. A distinct query is made to the field in database to get all distinct values for the field.

  • ptQueryList: A combo box will be used to edit the parameter. The items of the combo will be the query result of the SQL statement specified in MetaSQLStr property. The different between ptDistinctList and ptQueryList is that the query in distinct list is automatically built by TatVisualQuery, while with ptQueryList it's the user that specified the SQL statement.

  • ptChooseList: A combo box will be displayed for editing the parameter. Each item in combo box is related to an item in ListItems property. Once the user chooses the combo box item, the related ListItem is applied. See ListItems property for more details.

  • ptCheckList: Same as ptChooseList, but a check combo box is displayed instead. User can then choose one or more items, so that more than one ListItem can be applied.

For more information about TatParamDef properties and methods, see the component reference at online help from IDE.

Using choose-list and check-list parameters

TatParamListItem content is "applied" to SQL whenever user choose to apply it, by selecting/checking an item in the parameter combo box.

What TatParamListItem contains is information about which parameters of sql will be changed and their values. This is done by using property ParamValues and its properties Values and Names. The screenshot below illustrates how to use choose list and check list parameters.

param combo list

Filter conditions are set to filter orders given by its ItemsTotal field, which value must be bettwen LowValue parameter and HighValue parameter. In "Parameter editors" section, a single parameter editor is created, of type "dropdown choose-list" (which is ptChooseList type). Three items were created for this parameter editor: "Low-valued orders", "Medium-valued orders" and "High-valued orders". Each of item correspond to a TatParamListItem object. For each list item, two parameter values were defined. For example, for the item name "Mediu-valued orders", the parameter "LowValue" receives 20000, and the parameter "HighValue" receives 60000.

After all is set, end-user will see a combo with a caption "Filter orders by value...", and the combo has three options: "Low-valued orders", "Medium-valued orders" and "High-valued orders". User just choose an item, and the correct orders are displayed in grid. This is the mechanism: let's say end-user chooses "Medium-valued orders" item. When this happen, the parameter values defined in "Medium-valued orders" item is set: LowValue parameter receives 20000, and HighValue parameter receives 60000. Since these two parameters are being used in "Filter conditions" section to filter records by ItemsTotal field, the query will bring all orders which ItemsTotal field value is between 20000 and 60000.

Finally, the TatParamListItem object reflects the parameter editor item above. Below is reference for TatParamListItem class, with examples related to the screenshot above.