Configuring a Data Request with Data Aggregation
To configure a data request to return aggregate amounts:
On the Orchestrator Studio Home page, click the Data Requests icon.
Create a data request as described in Creating a Component
On the Data Request design page, in the Table/View Name field, enter the name of the table or business view.
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 the fields from the primary business view that is associated with the form.
Click the Load button.
The Orchestrator Studio loads all the fields from the table or the business view into the grid.
Slide the Aggregation toggle to the right.
This step enables the aggregation features in the grid to the left.
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 on which you want to filter on.
The Orchestrator Studio displays each field in the Filter Criteria area on the right.
Set up conditions for filtering data:
For each field in the Filter Criteria area, select the appropriate operand and in the adjacent field, enter a literal 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 braces in the syntax, for example:
${Address Number 1}If the field is a date, you can use the drop-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 Name 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 that are defined in a data request. The queries that you can see in this list are based on UDO security permissions.
-
(Release 9.2.5.4) Enable the Include Empty Query Values option if you want to include the empty values in the query when the data request runs.
For the existing data requests, this option is enabled by default and therefore all the defined fields for the query are always included even if they are empty. If you disable this option, any empty query values at runtime will not be included in the query (potentially resulting in more records returned).
Click the Aggregate icon next to the fields that contain the data for the aggregation.
In the pop-up window, select the type of aggregation that you want to perform.
The aggregation options that are displayed depend on whether the field contains a numeric value or a string.
(Optional) In the Aggregations section, slide the toggle button to right if you want the response to include a count of the records that are 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 grid to the left 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 Orchestrator Studio 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 default 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 section on the right, you can enter a variable name.
Starting with Tools release 9.2.3.4, if you are grouping data by a date field, you can configure the format of the date to be displayed in the output. In the Date Format drop-down field, you can either enter a simple date format or select any of the following formats:
Date - Milliseconds: Displays time in milliseconds.
Date - User's Format: Displays the EnterpriseOne user's preferred date format.
Date - Calendar Quarter: Uses the four-digit year and one-digit quarter format, for example 2020-1 for Q1-2020.
Date - Year: Uses the. four-digit year format.
Date - Year-Month: Uses the four-digit year and two-digit month format.
Date - MM/dd/yyyy
Date - dd/MM/yyyy
Date - yyyy/MM/dd
For more information on SimpleDateFormat, refer to the following Java documentation:
https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.htmlUsing the Group By option may result in multiple rows being returned, one for each unique combination of a group. For example, you might want the system to return the 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: When Data Set Variable Name is not used, the variables are only supported in the first row of a response. If Data Set Variable Name is populated, these variables are used to reference the individual columns within the array If a query contains multiple rows, only the values from the first row will be available as variables.(Release 9.2.6) Enable the Associated Description option if you want to return the description associated with the variable. If you enable this option, the associated description is available as an orchestration variable in subsequent orchestration steps and for inclusion in the orchestration output.
When you add this data request to an orchestration, you can use these variables to map the data in the data set to a subsequent orchestration step.
Order By icon. For any field that is used for aggregation of return data or for group by action on return data, click this icon to arrange return data in ascending or descending order.
Starting with Tools Release 9.2.8.2, you can reorder all the selected columns in each section of the data request, including the Returns and Aggregations, by dragging and dropping the rows. The system preserves the order that you set when you Save the changes and reflects this order in Orchestration Outputs when the Data Request is added to an orchestration. This feature improves readability and enables orchestrations to easily integrate with user interfaces or third-party systems that might be sensitive to the order of outputs.
You can use the Options button to configure the settings that control how the AIS Server processes a data request at runtime. See Configuring Form Request and Data Request Processing.