Aggregation attribute properties

The following interface is displayed when configuring an aggregation attribute:

An aggregation attribute has the following specific properties:

Target Table: you must browse for a Target table using the Choose Database Item dialog. When you do so, a list of database tables is displayed in the Choose Item section. Select the required database table and click OK.

If one or more auxiliary databases have been configured at the current RPI tenant, you can use the Choose Database selector to select the database from which the list of tables is retrieved. If auxiliary databases are available, the table is displayed at the property as ‘[Database name].[Schema].[Table name]’.

For more information on using the Choose Database Item dialog, please see the RPI Framework documentation.

Aggregation Table: this property represents the table within which the data to be aggregated is to be found...for example, if adding all of a customer’s sales to create a Total Customer Sales attribute, choose the Sales table. Selection of the same Target and Aggregation table is supported.

You must browse for an Aggregation table using the Choose Database Item dialog. When you do so, only the Target table and those tables that join to it are listed. The Choose Database section is not shown.

If auxiliary databases are available, the table is displayed at the property as ‘[Database name].[Schema].[Table name]’.

Note that you cannot select an Aggregation table until the Target table has been specified.

• Aggregation Key: populated with the list of database keys compatible with the Target table.

• Function: selected from a drop-down list containing the following values:

  • Count

  • Minimum

  • Maximum

  • Sum

  • Average

  • Custom

In the Total sales example, you would choose Sum from this list.

When value Custom is selected, the following additional properties are shown:

• Custom Function: you must supply a valid custom function with which to configure the attribute. Use of the string ‘{alias}’ to represent the aggregation table within the function is supported and is recommended to avoid any possible issues with column ambiguity at SQL execution.

• Function Data Type: this dropdown field allows you to define the custom aggregation attribute’s data type. The following values are available:

  • BigInt

  • Date

  • DateTime

  • Decimal

  • Integer

  • String

• Function Data Length: this mandatory integer property is only displayed when Function is set to Custom, and Function data type is set to String. It defaults to 50. It is used to default the attribute’s Length property when it is used in an export template.

• Validate: the custom function provided must be validated successfully before the attribute can be saved. A button is provided for this purpose. Its initial text reads:

The button is enabled when a Custom function has been supplied. When the button is clicked, its text changes to:

When validation is complete, the Results field is populated, and the button appears as follows if the function supplied is valid:

The button is re-enabled when the Custom function is changed.

The button appears as follows if the function supplied is invalid:

Results: this read-only property is displayed having validated the supplied Custom function. If the function is valid, Results displays:

The Finish button is also enabled.

If the function is invalid, error details are also displayed:

A warning is displayed if you subsequently attempt to Finish attribute creation.

Example: a read-only example of data to which the supplied Custom function has been applied is displayed after the function's successful validation.

Function Column: this property is shown when Function is set to any value other than Custom. It is required when any function other than Count is selected. It represents the column upon which the selected Function will be executed.  For example, having chosen function Sum, you could select Sales Amount from the Sales table.  When aggregated at the customer level, this attribute would expose the Total Sales Value on a customer-by-customer basis.

You can browse for a Function column using the Choose Database Item dialog. When you do so, columns within the selected Aggregation table are listed. The Choose Database section is not shown.

Note that you cannot select a string or date Function column when using the Average function.

Filter: optional.  You may choose an existing standard or basic selection rule to act as a Filter.  A Filter allows the records upon which the Function is performed to be limited.  In our example, if the Sales table contains both internet and store sales, and you want to total only internet sales, create a selection rule that counts only internet sales, with a resolution level of Sales.  Use the rule as a filter when building the aggregation and the resultant attribute will expose total internet sales on a customer-by-customer basis.

You can navigate to the filter selection rule by clicking Open latest version. If a Rule Designer is already open, the rule is shown there. If the Designer is not open, it is displayed in a new instance. You can also Clear the property.

Use of an auxiliary database-resolving selection rule in this context is not supported.