Function that will mirror the Dimension Balances Report

Hi Patrick. Thanks. I'm with you now!

Using a SI.QUERY function on GLACCOUNTBALANCE would have been best to return the dimensions, but it looks there is a Sage Intacct API limitation. We could nonetheless work around it with a new function but I have been able to build this in a different way. See attached example.

You'll see that it uses a CrossJoin Lambda function (which I found online) to return all the permutations of Accounts, Departments and Locations (all of them, not just the one with data since we can't get to that dimension balance table), then auto-hide rows is used to remove empty values. 

If we limit the Account set to just Expenses, the performance should be acceptable - it returns in a few seconds for me on the Sage demo instance. 

Please let me know if this works for you.

Damien,

Thanks for the reply and the links to the templates.  I am basically looking for an expanded trial balance function that would pull all accounts/departments/locations that have balances (because Sage Intacct essentially has unlimited combinations).  I am not looking for the GL Details although that is a great report to have.  

I use the dimension balances report because it has the ability to select the date ranges, GL account numbers, and the Dimension filters to expand out as far as we need.  In order for me to get this information now, I run the report in Sage and then copy\paste the accounts and dimensions into Excel and let Velixo do the work. 

But it would be great to have a SI.EXPANDDIMENSIONBALANCE or something along those lines that would pull in all of the various GL account and dimension combinations with balances.

Thanks.

Hi Patrick

This makes total sense, and thanks for posting here!

If I understand your requirement well, you'd like to see all underlying GL transactions for a given account (or set of accounts) by entity, date, and dimensions. And not in the typical layout of a Financial statement?

Have you tried using our prebuilt GL Transaction Details Report Template? It was released back in May of this year, so it's possible you missed it (if so I recommend checking this article from time to time: Sage Intacct Report Templates and Samples register : Velixo Help Center )

This report uses our SI.QUERY function that can query any object of Sage Intacct and return transactions (I added the link of the entire folder, so that you can also see other useful supporting Query functions).

Is this what you are looking for? If yes, I suggest that you customize this template to your needs, and feel free to contact me or support directly if you have any question.

Hope this helps.

Damien

Currently, I have to run the dimension balances report from Sage Intacct, then export to Excel, then copy/paste the Account/Department/Location into the report that I built with the Velixo functions for multiple tabs with various time frames.  Another issue with Sage is that the dimension balances report does not offer comparisons to prior years.

This function in Velixo would be a game changer.

We have been asked for this report recently by 3 separate clients. So I agree with this need!