Retrieving Project Balances by GL Account

Created by Harry Lewis, Modified on Tue, 16 Jul at 9:35 AM by Gabriel Michaud

Applies to

  • Velixo NX
  • Velixo Classic
  • Acumatica, MYOB Acumatica, Cegid XRP Flex, Haufe x360, JAMIS Prime
  • Excel 365 or Excel 2021 (or higher)



TABLE OF CONTENTS



Purpose


Please see Introduction to Report Templates for information about Samples, Demos, and Production Templates and the differences between them.  You many also want to refer to How to Modify Velixo Sample Reports.


This sample template demonstrates how to use Excel 365 and Velixo to retrieve Project Balances dimensioned by GL accounts


This workbook includes the following sample reports:

  • Project Balances by GL Account


Example Screen:






In this video, Gabriel presents how to build a generic inquiry to retrieve project balances by GL account in Excel using Velixo, and then shows how to create the report that uses that inquiry.


He demonstrates how to group the balances by ledger, account, financial period, and project.  In turn, he shows how you have the ability to filter and calculate the balances easily:


https://www.loom.com/share/865fd98c30e14953b064a7080a6882a6




Technical Details

  • The generic inquiry retrieves the total by ledger, account, project and financial period. The grouping allows us to minimize the amount of data to be retrieved when you run & refresh the report.

  • If you need to be able to able to filter by subaccount, or project task, the inquiry will need to be modified accordingly.

  • The inquiry results grid includes two formula fields: one that gives us the net amount (debit minus credit) of the transaction, another one that provides the financial period in YYYYMM format.  The reason the schema field is set to Batch.LineCntr is to ensure the field is formatted as an integer; this will allow Excel to properly filter by period range in the report.




Report Implementation

  • The raw data is loaded into the "Data" sheet of the workbook.  There are no filters, but you could decide to only load the data of a specific project, or for a specific date range.

  • The "Project Balances by GL Account" sheet compares the amount returned by the Velixo project functions with what we calculate straight from the GL.  Note that revenue accounts show up as negative because we show the raw GL balance (which has a credit normal balance).

  • Two different implementations of the GL calculation are provided: one using the =SUMIFS() function, another that leverages Excel's lambda functions to create a "named" version of the same function.



Note: The reason the SUMIFS() function is nested inside a SUM() function is because when we have multiple accounts in the range, SUMIFS() will return one row per GL account.




Named lambda function


You can learn more about named lambda functions from Microsoft's documentation.  


Note that (in the video) Gabriel uses the Excel Labs add-in which includes a feature called "Advanced Formula Environment" which provides an interface for creating these types of functions.


=PROJECTTURNOVERAMOUNTBYACCOUNT(Connection, Project, AccountCode, StartPeriodInclusive)




Downloads

Download the Sample report file

Download the required 
generic inquiry




(need help importing the Generic Inquiry?)


 

💡 Having trouble downloading the files?
Simply right-click the link and select Save link a\

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