Configuring a Data Request with Data Aggregation

Create a data request as described in Creating Service Requests.

In the Available Actions area, enter the name of the table or business view in the Table/View Name field.

If you do not know the table or business view name, click the Get View From Form button and enter the application and form ID to load all fields from the primary business view associated with the form.

Click the Load button.

The Orchestrator Studio loads all fields from the table or business view into the grid.

Slide the Aggregation toggle to the right.

This enables the aggregation features in the Fields grid.

Note: You can use the Data Set Variable Name field to configure the data request to return a data set. See Retrieving and Passing Data Sets in an Orchestration for more information.

Click the "Filter" icon next to the fields that contain the data that you want to filter on.

The Orchestrator Studio displays each field in the Conditions area on the right.

Set up conditions for filtering data:

For each field in the Conditions box, select the appropriate operand and in the adjacent field, enter a hard coded value or a variable.
A variable appears in the field by default. You can modify the variable name, but you must use the $ sign and brackets in the syntax, for example:
${Address Number 1}
Starting with Orchestrator Studio 7.1.0.0, if the field is a date, you can use the down arrow to set a special value, such as today plus or minus the number of days, months, or years that you specify.
Select the Match All or Match Any option to determine how the conditions are applied.
You can also select the Options button, and from the Query drop-down list, select a predefined query to use for the filtering criteria. You can use a query instead of or in combination with the filtering criteria defined in a data request. The queries that you can see in this list are based on UDO security permissions.

Click the "Aggregate" icon next to the fields that contain the data for the aggregation.

In the pop-up window, select the type of aggregate that you want to perform.

The aggregate options displayed depend on whether the field contains a numeric value or a string.

(Optional) In the Aggregations and Variable Names section, click the Include Count check box if you want the response to include a count of the records returned. To use the returned count in a subsequent orchestration step, enter a variable name in the adjacent field.

(Optional) Use the following features in the Fields grid to further define the data aggregation:

"Having" icon. Click this icon next to a field for which you want to provide additional filtering criteria on an aggregation.
The Data Request design page displays the field in the Having section on the right.
a. Click the drop-down list next to the field and select the operand.
b. In the adjacent field, enter a hard coded value or variable.
"Group By" icon. Click this icon next to any field that you want the aggregate results grouped by. In the "Group By and Variable Name" section on the right, you can enter a variable name.
Using "Group By" may result in multiple rows being returned, one for each unique combination of a group. For example, you might return total orders for an entire year for customers, and group the results by customer. In this case, the data request response will return a unique row for each customer with the customer's total orders for the year.
Note: Variables are only supported in the first row of a response. If a query contains multiple rows, only the values from the first row will be available as variables.
"Order By" icon. For any fields used in an aggregate or "group by," click this icon to arrange return data in ascending or descending order.

(Optional) Click the Options button to configure settings that control how the AIS Server processes a data request at runtime. See Configuring Form Request and Data Request Processing.