Using Dimensions

Created by Damien Zwillinger, Modified on Wed, 07 Feb 2024 at 06:59 PM by Harry Lewis

Applies to:

  • Velixo NX
  • Sage Intacct

TABLE OF CONTENTS


Overview


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.

 

Method #1 - two-column range

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

 

Example dimension filter:

Department

150

Customer

10003;10004

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

  • where the company department associated with the transaction is "150", 

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

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 #2 - 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 support 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)




Supported Dimensions

The supported dimension names for the Dimensions argument are as follows. Each row in the below table corresponds to a dimension:

  • Department

  • Employee

    • Both Employee IDs and Employee Group IDs are supported as values.

  • Employee type

    • ⚠️ Do not use if Employee is set

  • Customer

    • Both Customer IDs and Customer Group IDs are supported as values.

  • Customer type

    • ⚠️ Do not use if Customer is set.

  • Vendor

    • Both Vendor IDs and Vendor Group IDs are supported as values.

  • Vendor type

    • ⚠️ Do not use if Vendor is set.

  • 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 Project is set.

  • Task

    • ↖️ Only available if parent Project is set.

  • Cost type

    • ↖️ Only available if parent Task is set. 

  • Item

    • Both Item IDs and Item Group IDs are supported as values.

  • Product line

    • ⚠️ Do not use if Item is set.

  • Class

    • Both Class IDs and Class Group IDs are supported as values.

  • Contract

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

 


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article