Velixo NX 2025.2 Release Notes – Sage Intacct

SI.EXPANDGLHISTORY

Created by Harry Lewis, Modified on Thu, 13 Mar at 1:23 PM by Grigorii Kosianenko

Applies to

Velixo NX 2025.02 or higher
Sage Intacct

TABLE OF CONTENTS

Introduction
Syntax
Parameters
Output
Example

Introduction

If you want to get accounts with non-zero balances, you can use the SI.EXPANDGLHISTORY function.

You can make an even more precise query to select accounts with a non-zero balance of a specific type for particular dimensions from the specified account range. You can also specify books and balance types that should be used for the turnover calculation.

Syntax

=SI.EXPANDGLHISTORY(ConnectionName,AccountGroup,AccountCode,StartDate,EndDate,LocationId,Books,Dimensions,UserDefinedDimensions,IncludeChildDimensionBalances,ExpansionOrder,IncludeInactive,BalanceTypesToLookAt,BudgetId)

Parameters

Parameter	Required/Optional	Description
ConnectionName	Required	The name of the connection, as specified in the Connection Manager
AccountGroup	Optional	The name of the account group that gathers the required accounts. Either AccountCode or AccountGroup should be provided.
AccountCode	Optional	The account code (10100), account range (10100:11300;10600), or a wildcard mask (??999) for required accounts. See the article about composing account ranges, using wildcards, and other techniques for details. Either AccountCode or AccountGroup should be provided.
StartDate	Required	The inclusive start date or dates in YYYY-MM-DD format or an Excel range reference.
EndDate	Required	The inclusive end date or dates in YYYY-MM-DD format or an Excel range reference. The number of end dates should match the number of start dates. The start and end dates are taken pairwise so that you can define more than one period.
LocationId	Optional	One or many IDs of the Sage Intacct entities or locations. If you specify LocationId, you can use it to define the ExpansionOrder.
Books	Optional	You can specify a reporting or global consolidation (GC) book ID to search for transactions. You can add one or more user-defined adjustment book IDs separated by commas to the book ID (“ACCRUAL,ALT“). If you omit the parameter, ACCRUAL or CASH book will be used, depending on the company settings.
Dimensions	Optional	You can specify the dimensions and their values if you want to consider transactions with these dimensions only. There are two ways to do it: A two-column Excel range where the first column contains the name of a predefined dimension (e.g., department, customer, project, etc.) and the second column contains a semicolon-separated list of values for that dimension OR A multi-column array where the first column of the array contains the name of a predefined dimension, and the remaining columns contain the individual values for that dimension. You can specify dimension values using ranges, wildcards, and other techniques. If you want to specify user-defined dimensions, please use the UserDefinedDimensions parameter. Both the Dimensions and UserDefinedDimensions are used to filter transactions.
UserDefinedDimensions	Optional	You can specify the dimensions and their values if you want to consider transactions with these dimensions only. There are two ways to do it: A two-column Excel range where the first column contains the name of a user-defined dimension and the second column contains a semicolon-separated list of values for that dimension OR A multi-column array where the first column of the array contains the name of a user-defined dimension, and the remaining columns contain the individual values for that dimension. You can specify dimension values using ranges, wildcards, and other techniques. If you want to specify predefined dimensions, please use the Dimensions parameter. Both the Dimensions and UserDefinedDimensions are used to filter transactions.
IncludeChildDimensionBalances	Optional	If you want to exclude combinations with inactive dimensions from your result, set this parameter to FALSE. By default, all combinations of dimensions specified in Dimensions, UserDefinedDimensions , and ExpansionOrder, including those with Inactive status, will be returned.
ExpansionOrder	Optional	If you want to see in your result another set of dimensions than specified in Dimensions and/or UserDefinedDimensions, you can pass them as a comma-separated string. The Dimensions and UserDefinedDimensions not mentioned in the ExpansionOrder are still used for filtering transactions. The result is grouped by dimensions specified in ExpansionOrder. You also can use Location in ExpansionOrder if you specify the LocationId parameter. To get account codes in the result set, specify AccountCode in ExpansionOrder.
IncludeInactive	Optional	If you want to get all possible combinations of dimensions specified in Dimensions and UserDefinedDimensions without regard to their turnovers, set this parameter TRUE.
BalanceTypesToLookAt	Optional	You can specify balance types to take into account as a comma-separated string (“debit,credit“). Options: debit, credit, adjdebit, adjcredit, turnover, opening, closing, budgeted. By default, all the values above but budgeted are used for balance calculation. The budgeted option can be specified only with BudgetId.
BudgetId	Optional	The budget to look at for balances. This parameter is required if BalanceTypesToLookAt contains budgeted and should be specified only in this case.

Output

The function returns a spill range with account codes and dimensions specified in the parameters LocationId, Dimensions, UserDefinedDimensions, or ExpansionOrder.

It does not return balances or turnovers for these account codes and dimensions.

The output is always sorted by account code first, then by LocationId if specified, and afterward, each of the dimensions specified in Dimensions and UserDefinedDimensions or by ExpansionOrder.

For example, if you specify ExpansionOrder as Location,Project, the output will be sorted by Location, then by Project.

Example

You want to get closing balances for the accounts with transactions related to the Tango project.

Primarily, you need to get all accounts with non-zero turnover for the Project dimension.

=SI.EXPANDGLHISTORY("Sage",,"*","2024-01-01","2024-12-31",,{"Project", "Tango"})

Or this way, if you have them on the sheet:

=SI.EXPANDGLHISTORY(B2,,"*",B5,B6,,B3:B4)

Then, you can get the closing balance for each account.

=SI.CLOSINGBALANCE($B$2,,A8,$B$5,$B$6,,,$B$3:$B$4)

See the CLOSINGBALANCE function description for details.