Auditors are typically resistant to the consolidated trial balance and still want to see a breakdown by account number, department, and location. So, we use the dimension balances report to satisfy that request. It would be great to be able to have a function that could easily pull the balances from any combination of accounts, departments, and locations (or any other dimensions).
Account
Department
Location
511100--Salaries
1010--Executive Office
110--Home Office
511100--Salaries
1020--Marketing
110--Home Office
511100--Salaries
1030--Finance
110--Home Office
2 Votes
5 Comments
Sorted by
Damien Zwillingerposted
3 months ago
Admin
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.
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.
0 Votes
Damien Zwillingerposted
3 months ago
Admin
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?
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
0 Votes
P
Patrick Youngposted
3 months ago
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.
0 Votes
Lisa Curlposted
3 months ago
We have been asked for this report recently by 3 separate clients. So I agree with this need!
2 Votes
Auditors are typically resistant to the consolidated trial balance and still want to see a breakdown by account number, department, and location. So, we use the dimension balances report to satisfy that request. It would be great to be able to have a function that could easily pull the balances from any combination of accounts, departments, and locations (or any other dimensions).
2 Votes
5 Comments
Damien Zwillinger posted 3 months ago Admin
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.
Attachments (1)
Expand Dimen....xlsx
104 KB
1 Votes
Patrick Young posted 3 months ago
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.
0 Votes
Damien Zwillinger posted 3 months ago Admin
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
0 Votes
Patrick Young posted 3 months ago
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.
0 Votes
Lisa Curl posted 3 months ago
We have been asked for this report recently by 3 separate clients. So I agree with this need!
0 Votes
Login or Sign up to post a comment