SELECT GL7Accounts.AccountNumber "Account Number",
dbo.GL7GetAccountBalance(1, GL7ACCOUNTS.ACCOUNTNUMBER, '2016/1/1', '',
1, -1, 0, 0, 0, 0, 0, 0, 0, -1, 1, 0) "Beginning Balance"

FROM dbo.GL7Accounts

WHERE gl7accounts.CATEGORY = 4 --Revenue Category only
--1-asset, 2-liability, 3-net asset, 4-revenue, 5-expense, 6-gift, 7-transfer, 8-gain, 9-loss


Specific information about the GL7GetAccountBalance function parameters:

@UserID integer > FE User Id (ex: 1 = Supervisor)
@AccountNumber varchar(30) > Account Number i.e. '01-1000-00'
@BalanceDate DateTime > Date to get the balance
@ProjectIDString varchar(8000) > String containing GL7Projects IDs. '1, 2, 4' use empty '' string for all
@GL7GeneralInfoID integer > Usually 1
@OutputNatural smallint >  -1= returns Debits/Credits as positive/Negative. otherwise returns all positive (-1 recommended)
@TranType smallint > Transaction Type 1 = Regular, 2 = Encumbrance, 0 = both.
@Class integer > TableEntriesID for the Account class tableentries ( 0 for all )
@TrancodeValue1 integer >TableEntriesID for the 1st Transaction codes tableentries ( 0 for all )
@TrancodeValue2 integer > TableEntriesID for the 2nd Transaction codes tableentries ( 0 for all )
@TrancodeValue3 integer > TableEntriesID for the 3rd Transaction codes tableentries ( 0 for all )
@TrancodeValue4 integer > TableEntriesID for the 4th Transaction codes tableentries ( 0 for all )
@TrancodeValue5 integer > TableEntriesID for the 5th Transaction codes tableentries ( 0 for all )
@BeginningBalance smallint > Beginning balance -1 if you want the beginning balance, 0 for ending balance
@CompanyID integer > CompanyID ( 1 recommended)
@NotYetPostedSystemMask integer > NotYetPostedSystemMask System mask for the source system. ( 0 recommended)

Note: If the balance seems inaccurate, check the @BalanceDate parameter as fiscal periods may be affecting the number. Try using a date period after or before the given date.
Disclaimer: We provide programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Our Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.