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 connectionsaverage- for retrieving averages for multiple connectionsmin/max- e.g., for performance comparisonconcatstack-horizontally- e.g., for data reconciliation/comparisonstack-vertically- e.g., for retrieving exhaustive lists of objects (can be combined withSORT(UNIQUE(...)))first- returns the first result for any of the connections that does not trigger an errorensure-identical- returns a value only if it’s identical across all queried connections; otherwise, returns an errorset- 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'.
Query Builder formulas, 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 |
|---|---|---|
| Required | Velixo range expression containing connection names. |
| Optional | Aggregation mode selection. Valid values: Default value: |
| Optional | If Example: You run three connection-scoped functions, each returning 100 values. If you specify Default value: See the Default aggregation mode section |
| Optional | Specifies whether to compare normalized (lowercase) or original strings in Default value: |
| Optional | Specifies whether whitespace surrounding the strings should be ignored in comparison in Default value: |
| Optional | Defines a separator character for the By default, the separator defined in the Options menu is used. |
| Optional | If 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:
|
| Optional | If set to
If set to Default value: |
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 |
|---|---|
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|
Unsupported scenarios
Unsupported functions
The following functions do not support the multiple connection functionality:
Query function limitations
The SI.QUERY and SI.XQUERY functions do not support multiple connections in the following scenarios:
The function uses Table Mirroring; the
OutputColumnargument is provided in the function’s formulasumoraverageis selected as theAggregationModeparameter value in theConnectionNameargument 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
GlobalAggregationparameter set toTRUEin 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:
{“Connection“, “*“; “AggregationMode“, “sum“}
As a result, the function returns a summary of values for all connections active in the workbook.