SI.EXPANDGLHISTORY

Overview

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

CODE

=SI.EXPANDGLHISTORY(ConnectionName, AccountGroup, Account, FromDate, ToDate, LocationId, Books, Dimensions, UserDefinedDimensions, IncludeChildDimensionBalances, ExpansionOrder, IncludeInactive, BalanceTypesToLookAt, BudgetId)

Arguments

Argument	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.
`Account`	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.
`FromDate`	Required	The inclusive start date or dates in YYYY-MM-DD format or an Excel range reference.
`ToDate`	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 argument, `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` argument. 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 argument. 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 argument 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 can also use `Location` in `ExpansionOrder` if you specify the `LocationId` argument. To get account codes in the result set, specify `Account` 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 argument `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 argument 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 arguments 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 afterwards, 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.

CODE

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

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

CODE

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

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

CODE

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

See the CLOSINGBALANCE function description for details.