Using Dimensions

Overview

The Velixo functions for retrieving account balances for Sage Intacct (such as SI.TURNOVER , SI.OPENINGBALANCE, SI.CLOSINGBALANCE, and SI.BDUGETTURNOVER) support showing only that part of the balance that corresponds to a particular analytical dimension value (for example, a certain department, vendor, or customer).

To filter by dimension, you need to use the Dimensions argument of the balance function.

For user-defined dimensions, there is a separate argument: UserDefinedDimensions, which works in the same way.

There are various methods for referencing the Dimensions and UserDefinedDimensions.

Method #1 - The SI.DIMENSIONS function (RECOMMENDED)

The most versatile method for accessing and defining the dimensions you want to use for your Velixo functions is use the SI.DIMENSIONS function. SI.DIMENSIONS converts a sequence of pairs (as many combinations of DimensionName and DimensionValue as needed) into the matrix format that is usable by the other Velixo functions.

Example:

The following function returns the account turnover in the month of December for GL account #10010 for just Department number 200 and both Customer 10003 and 10004

=SI.TURNOVER("Sage",,"10010","2019-12-01","2019-12-31","*", SI.DIMENSIONS("Sage","Department","200","Customer","10003;10004")

See the SI.DIMENSIONSfunction for more information.

Method #2 - two-column (or two-row) range

The value of the dimension argument can be a two-column or two-row Excel range, where the first column or row contains the name of the dimension, and the second column or row contains a list of semicolon-separated dimension values.

Examples:

By specifying such an Excel range in the SI.TURNOVER function, you will be able to see only the turnover amounts:

• where the Department associated with the transaction is "200",

• and the Customer record associated with the transaction is either 10003 or 10004

Two-column example	Two-row example

Sage Intacct expects the ID of a record, not its Name, in the filter. For example, if there is a customer record with ID "10003" and the name "Uplift Services", you will need to specify "10003" in the dimension filter.

Method #3 - Excel array

The dimension argument can be a multi-column array where the first row (or column) of the array contains the name of a user-defined analytical dimension, and the remaining rows (or columns) contain the individual values for that dimension.

Example A - Vertical

Dimension name in first row

Example B - Horizontal

Dimension name in the first column

Special Cases

1 - Including Empty dimension values

To retrieve balances with any (including empty) dimension value (*?)

The following function returns the account turnover for GL account #10010. In the function, the Dimensions argument (configured to {"customer";"*?"}) means that transactions with any value of the Customer dimension will be included in the balance.

=SI.TURNOVER("sage",,"10010","2019-12-01","2019-12-31","100","Accrual",{"customer";"*?"})

2 - Excluding Empty dimension values

To retrieve balances with non-empty dimension values only (*)

The following function returns the account turnover for GL account #10010. In the function, the Dimensions argument (configured to {"customer";"*!"}) means that only transactions with non-empty values in the Customer dimension will be included in the balance.

=SI.TURNOVER("sage",,"10010","2019-12-01","2019-12-31","100","Accrual",{"customer";"*"})

3 - Including ONLY Empty dimension values

To retrieve balances with empty dimension values only (null)

The following function returns the account turnover for GL account #10010. In the function, the Dimensions argument (configured {"customer";"null"}) means that only those transactions with an empty value in the Customer dimension will be included in the balance.

=SI.TURNOVER("sage",,"10010","2019-12-01","2019-12-31","100","Accrual",{"customer";"null"})

The null syntax is not supported with the following dimensions:

• Customer Type

• Vendor Type

• Project Type

• Cost Type

• Employee type

• Product Line

4 - Using Sage Intacct Dimension GROUPS

To use a Sage Intacct Dimension Group, you can use the dimension within the SI.DIMENSIONS function and signify that the corresponding value is a group by prefixing its name with a #

Location Group Example

To use a Sage Intacct Location Group, you can use the Location Group dimension within the SI.DIMENSIONS function:

Or use either the Location dimension inside the Velixo SI.Dimensions function:

or use the Location argument inside the specific Velixo function:

(a cell reference has been used in this example)

You simply need to signify the intent to use a Group by prefixing the group name with #

Customer Group Example

To use a Sage Intacct Customer Group, you can use the SI.DIMENSIONS function with either the Customer Group dimension:

Or use the Customer dimension and preface the group name with the "#":

(a cell reference has been used in this example)

Wondering where to find your pre-defined Dimension Groups within Sage Intacct?
Applications → Reports → Setup → Dimension groups

Supported Dimensions

The supported dimension names for the Dimensions argument are as follows:

For your convenience, the dimension names are not case-sensitive.

How to use User-Defined Dimensions

When using a User-Defined Dimension in your Velixo functions, it must be the Integration Name that is specified. Thus, given the following configuration within Sage Intacct:

We must use the Integration Name of test_dimension_2 in our Velixo functions.

e.g.,

as seen in this example:

You can specify dimension values using ranges, wildcards, and other techniques.

Use the function to return values from a UDD

In case you want to use UDDs in or SI.WRITEBACK… functions, you can easily retrieve the UDD value IDs they require using the function.