Multiple connections per formula

Overview

This feature allows users to retrieve and combine data from multiple connections directly within supported Velixo functions. You can simply use a Velixo filtering expression (for instance, * for all available connections) or specify a 2D array for the ConnectionName argument to aggregate results using one of the following modes:

sum - for retrieving totals for multiple connections
average - for retrieving averages for multiple connections
min / max - e.g., for performance comparison
concat
stack-horizontally - e.g., for data reconciliation/comparison
stack-vertically - e.g., for retrieving exhaustive lists of objects (can be combined with SORT(UNIQUE(...)))
first - returns the first result for any of the connections that does not trigger an error
ensure-identical - returns a value only if it’s identical across all queried connections; otherwise, returns an error
set - combines the results from queried connections, removes duplicates, and sorts the results.

This functionality supports a range of scenarios, including consolidating financial data across companies, merging lists, or combining data from multiple environments. For instance, you can get the turnover by specific account for all connections in the workbook, or get all projects for connections whose names start with 'Construction'.

Writeback-based functions, and functions that leverage Table Mirroring do not support this feature.

You can leverage the VX.SETTINGS function to construct a 2D array with the parameters listed below.

Parameters

Parameter	Required / Optional	Description
`Connection`	Required	Velixo range expression containing connection names.
`AggregationMode`	Optional	Aggregation mode selection. Valid values: `sum`, `average`, `min`, `max`, `concat`, `stack-horizontally`, `stack-vertically`, `first`, `ensure-identical` ,`set` Default value: `stack-horizontally`
`GlobalAggregation`	Optional	If `TRUE`, then `sum`, `average`, `min`, `max`, and `concat` collapse all values, including dynamic-aware results of individual functions. Example: You run three connection-scoped functions, each returning 100 values. If you specify `sum` with `GlobalAggregation = TRUE`, you will get the total sum of all 300 values instead of the default spill range. Default value: See the Default aggregation mode section
`CaseInsensitiveAggregation`	Optional	Specifies whether to compare normalized (lowercase) or original strings in `min` and `max` modes. Default value: `TRUE`
`TrimmedAggregation`	Optional	Specifies whether whitespace surrounding the strings should be ignored in comparison in `min` and `max` modes. The function returns original values regardless of the setting. Default value: `TRUE`
`ConcatSeparator`	Optional	Defines a separator character for the `concat` aggregation mode. By default, the separator defined in the Options menu is used.
`IncludeConnectionName`	Optional	If `TRUE`, in the `stack-horizontally` mode, adds a row with connection names above the returned datasets. In the `stack-vertically` mode, it adds a column containing connection names. Ignored in other modes. The connection name appears only once per dataset, then empty rows/columns are output until the connection changes. If the data set for the connection is empty, a row/column with the connection name is still displayed. When working with a single connection, the connection name will not be displayed. Default value: `TRUE` for `stack-horizontally` and`stack-vertically` modes `FALSE` for `set` ignored in other modes
`StrictErrorMode`	Optional	If set to `FALSE`, and the function returns an error for a specific connection and input value, handle it depending on the aggregation mode and the output value type: for `sum`, `average`, `min`, `max`: replace the invalid value with one of the following, “neutral” values: `0` for a number, `FALSE` for a boolean, `'1900.01.01'` for a date, `''` for a string for `concat`: replace the invalid value with an empty string for `stack-horizontally`, `stack-vertically`: a replace the invalid value with a 1x1 empty array. If set to `TRUE`, and the function returns an error for a specific connection and input value, error is returned in the aggregated function output for this particular value. Default value: `TRUE`

Default aggregation mode

The default value of the AggregationMode parameter depends on the type of output of the function used. Below, see a table listing supported functions and their corresponding default aggregation modes.

Velixo function	Default aggregation mode
ACCOUNTBEGINNINGBALANCE	`sum`
ACCOUNTCLASS	`ensure-identical`
ACCOUNTCLASSLIST	`set`
ACCOUNTCLASSNAME	`first`
ACCOUNTCONSOLIST	`set`
ACCOUNTENDINGBALANCE	`sum`
ACCOUNTGROUPDESCRIPTION	`first`
ACCOUNTNAME	`first`
ACCOUNTSANDSUBACCOUNTSWITHHISTORY	`set`
ACCOUNTSWITHHISTORY	`set`
ACCOUNTTOTALCREDITS	`sum`
ACCOUNTTOTALDEBITS	`sum`
ACCOUNTTURNOVER	`sum`
ACCOUNTTYPE	`ensure-identical`
ACU.EXPANDLEDGERRANGE	`set`
ACU.EXPANDOBJECTRANGE	`set`
ACU.EXPANDPROJECTTASKRANGE	`set`
ACU.OBJECTDEFINITION	`stack-horizontally`
ACU.QUERY	`stack-horizontally`
ACU.QUERYFILTER	`ensure-identical`
BRANCHLIST	`set`
BRANCHNAME	`first`
COMPANYNAME	`first`
COSTCODEDESCRIPTION	`first`
EXPANDACCOUNTCLASSRANGE	`set`
EXPANDACCOUNTGROUPRANGE	`set`
EXPANDACCOUNTRANGE	`set`
EXPANDBRANCHRANGE	`set`
EXPANDCOMPANYRANGE	`set`
EXPANDCOSTCODERANGE	`set`
EXPANDINVENTORYITEMRANGE	`set`
EXPANDPROJECTHISTORY	`set`
EXPANDPROJECTRANGE	`set`
EXPANDSEGMENTVALUERANGE	`set`
EXPANDSUBACCOUNTRANGE	`set`
FINANCIALPERIOD	`ensure-identical`
FINANCIALPERIODBYDATE	`ensure-identical`
FINANCIALPERIODENDDATE	`ensure-identical`
FINANCIALPERIODLIST	`set`
FINANCIALPERIODOFFSET	`ensure-identical`
FINANCIALPERIODSTARTDATE	`ensure-identical`
GI	`stack-horizontally`
GIFILTER	`ensure-identical`
GILOOKUP	`first`
GILOOKUPF	`first`
INVENTORYITEMDESCRIPTION	`first`
PROJECTBEGINNINGAMOUNT	`first`
PROJECTBEGINNINGQUANTITY	`first`
PROJECTBUDGETAMOUNT	`first`
PROJECTBUDGETQUANTITY	`first`
PROJECTCHANGEORDERAMOUNT	`first`
PROJECTCHANGEORDERQUANTITY	`first`
PROJECTCOMMITTEDAMOUNT	`first`
PROJECTCOMMITTEDCHANGEORDERAMOUNT	`first`
PROJECTCOMMITTEDCHANGEORDERQUANTITY	`first`
PROJECTCOMMITTEDINVOICEDAMOUNT	`first`
PROJECTCOMMITTEDINVOICEDQUANTITY	`first`
PROJECTCOMMITTEDOPENAMOUNT	`first`
PROJECTCOMMITTEDOPENQUANTITY	`first`
PROJECTCOMMITTEDQUANTITY	`first`
PROJECTCOMMITTEDRECEIVEDQUANTITY	`first`
PROJECTCOSTATCOMPLETION	`first`
PROJECTCOSTTOCOMPLETE	`first`
PROJECTDESCRIPTION	`first`
PROJECTENDDATE	`first`
PROJECTENDINGAMOUNT	`first`
PROJECTENDINGQUANTITY	`first`
PROJECTFORECASTAMOUNT	`first`
PROJECTFORECASTQUANTITY	`first`
PROJECTFORECASTREVISEDAMOUNT	`first`
PROJECTFORECASTREVISEDQUANTITY	`first`
PROJECTPENDINGINVOICEAMOUNT	`first`
PROJECTREVISEDBUDGETAMOUNT	`first`
PROJECTREVISEDBUDGETQUANTITY	`first`
PROJECTREVISEDCOMMITTEDAMOUNT	`first`
PROJECTREVISEDCOMMITTEDQUANTITY	`first`
PROJECTSTARTDATE	`first`
PROJECTTASKDESCRIPTION	`first`
PROJECTTASKENDDATE	`first`
PROJECTTASKPLANNEDENDDATE	`first`
PROJECTTASKPLANNEDSTARTDATE	`first`
PROJECTTASKSTARTDATE	`first`
PROJECTTURNOVERAMOUNT	`first`
PROJECTTURNOVERQUANTITY	`first`
SEGMENTDESCRIPTION	`first`
SUBACCOUNTNAME	`first`

Unsupported scenarios

Unsupported functions

The following functions do not support the multiple connection functionality:

Query function limitations

The GI and ACU.QUERY functions do not support multiple connections in the following scenarios:

The function uses Table Mirroring; the OutputColumn argument is provided in the function’s formula
sum or average is selected as the AggregationMode parameter value in the ConnectionName argument of the function’s formula.

Drilldown limitations

The following Drilldown-related scenarios are currently not supported or limited:

Drilling into the Excel sum of results stacked horizontally or vertically
Drilling into results stacked horizontally or vertically in case of multi-column and/or multi-row results
Drilling into the sum of results with the GlobalAggregation parameter set to TRUE in case of multi-column and/or multi-row results.

Examples

All connections in the workbook, `sum` aggregation mode

The function in this example uses a range named “Connection" as the function’s ConnectionName argument.

The Connection parameter in the connection name is set to * - a Velixo filtering technique that returns all possible values, in this case, connection names.

The AggregationMode is set to sum.

The resulting ConnectionName argument is equivalent to the following 2D array:

CODE

{“Connection“, “*“; “AggregationMode“, “sum“}

As a result, the function returns a summary of values for all connections active in the workbook.