Using Dimensions

Created by Damien Zwillinger, Modified on Mon, 26 Aug at 6:15 PM by Harry Lewis

Applies to:

Velixo NX
Sage Intacct

TABLE OF CONTENTS

Overview
Method #1 - The SI.DIMENSIONS function
Method #2 - two-column (or two-row) range
Method #3 - Excel array
- Example A - Vertical
- Example B - Horizontal
Special Cases
Supported Dimensions
How to use User Defined 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.DIMENSIONS function 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

Note: 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 only those transactions with and 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"})

⚠️Important Note:

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 parameter 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:

Department	The Department ID values are supported
Employee	Both Employee IDs and Employee Group IDs are supported as values
Employee Type	⚠️ Do not use if the Employee dimension is used
Customer	Both Customer IDs and Customer Group IDs are supported as values
Customer Type	⚠️ Do not use if the Customer dimension is used
Vendor	Both Vendor IDs and Vendor Group IDs are supported as values.
Vendor Type	⚠️ Do not use if the Vendor dimension is used
Warehouse	Both Warehouse IDs and Warehouse Group IDs are supported as values
Project	Both Project IDs and Project Group IDs are supported as values
Project Type	⚠️ Do not use if the Project dimension is used
Task	↖️ Only available if parent Project is set
Cost Type	↖️ Only available if parent Project is set
Item	Both Item IDs and Item Group IDs are supported as values
Product Line	⚠️ Do not use if the Item dimension is used
Class	Both Class IDs and Class Group IDs are supported as values
Contact	Both Contract IDs and Contract Group IDs are supported as values

Note: 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.,

such as is done here: