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

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:

 



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 at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article