By using this website, you agree to our Terms of Use (click here)
Good afternoon,
I am trying to modify Acumatica's stock Trial Balance Summary report (Finance > Reports > Trial Balance Summary) so that it lists out all possible account/subaccount combinations, even if there has never been a transaction for the account/subaccount combination before. The report uses a parent account of GLHistoryByPeriod, and that only holds a list of account/subaccounts that have had at least 1 historical transaction in the past. I have tried numerous things to generate the report the way I'm looking for, but the system errors out when I change the relationships on the report. I have tried these 2 combinations to start off the relationships of the report:
Account-Inner-Sub
Account-Left-GLHistoryByPeriod
Sub-Left-GLHistoryByPeriod...
Account-Left-GLHistoryByPeriod
Sub-Left-GLHistoryByPeriod
I'm sure I've tried others, but can't think of what they are at the moment. I have written a GI that almost gets me there using only the Account, Sub and GLHistory tables using the first linking combo above and it works, but still has its flaws due to a lack of transactions in a specific period. If no transactions in the specified period, it won't provide a beginning and YTD balance. I have also toyed with the idea of creating a master report to pull the COA, then have a subreport to pull the balances of the specific account/sub combo, but I'm not sure how that linking works in Acumatica. It looks like the linking is done through parameters and not linking fields/tables like in Crystal Reports.
If anyone has any ideas/suggestions, please let me know. I'll be happy to provide additional information if needed. Thanks,
Chris
Hi Chris,
It would probably be easier to use the Financial Report Writer (ARM) for this. There is a setting for displaying zero balances.
Thanks for the idea Tim. I have tried doing this and I'm still struggling getting the results I'm looking for. It's either that all of the subaccounts are summed up in the main account, or the main accounts are summed up into the subaccounts. I'll admit that I'm not too experienced with ARM, so maybe there's something I'm missing in the setup of the row sets. The Expand field on the Data Source Column only seems to work on the first line and not the subsequent lines.
Argh, you're right. It's either you expand Account or Subaccount, but not both in ARM.
So back to Report Designer then.
Have you tried doing a Cross join between the Account and Sub tables? That should produce a row for every combination of values between the two tables.
Tim, yes, I have tried that before and get the results in the screenshots attached.
Shawn, this is pretty much what Tim suggested in his first comment. The issue with this is it still only sums up by Account OR Subaccount. It won't provide me Account and Subaccount. I need the columns to be:
Account | Subaccount | Type| Description | Beginning Balance | Debit | Credit | Ending Balance
The Arm reports won't run a report in this fashion. I have figured out how to add the subaccount into the stock TB Summary report in GL, however it will only give me account/subaccount combos that have history. If it doesn't have history, then the combo doesn't exist in the history tables and therefore won't populate the data the way I'm hoping it would.
Have you tried the detail as a sub report? I've used the concept for other reports where I need to show data that doesn't have historical combos.
Did you try this trick? I saw it a while back but haven't tried myself as of yet so no promises.