I need to retrieve the FinYtdBalance field value from the GLHistory table for specific AccountIDs (e.g., 10000 and 10001) and display these values in separate columns but in a same row within a report. Additionally, I want to calculate and display the difference between these values in another column in the same row.
I have attempted to achieve this using variables, but it didn't work as the report designer processes line by line. When the report processes the values for AccountID 10001, it doesn't have the values for AccountID 10000, making it difficult to calculate the difference. I also tried using sub-reports to retrieve these values, but this approach was unsuccessful.
Is there an effective method to accomplish this in Acumatica Report Designer? Any guidance or detailed steps on how to implement this would be greatly appreciated.
Thank you!
Page 1 / 1
Hi @RKarunarathne51
Please refer to the “Trial Balance Summary” report (GL632000.rpx)
Best Regards,
NNT
Hi @nhatnghetinh ,
I referred to the “Trial Balance Summary” report (GL632000.rpx), but the problem is displaying the fields of different AccountIDs in the same row. In the trial balance report, it shows the values line by line, but I need to show the values in the same row. Thanks you
Hi @RKarunarathne51
You create a new report base on GL632000.rpx > Delete all fields and current Groups. At Footer of Ledger create columns corresponding to accounts > Create fields to get data corresponding to accounts of columns.
Best Regards,
NNT
Hello,
Alternate ideas to print accounts across the page include ARM reports in the GL and Veli xo reports in Excel.
I noticed one setting in Report Designer → Proces Order. Did you already try these different ProcessOrder options?
It’s not easy to find report designer Help - Form reference, field by field explanation, to confirm whether the above setting is what you need. (I searched, but it seems no page of Report Designer Help explains the ProcessOrder options.)
I wonder if you could use a SubReport to achieve the Variables processing and then connect your subreport to the main report? Subreport is just an idea…. I didn’t try it to see if it will work for your specific situation.
Laura
Hi @nhatnghetinh ,
How did you get the values for 10000, 10001, and 10003 in the same row? What formula did you use to achieve that? Thank you
Hi @RKarunarathne51
You can refer to the formula in GL632000.rpx Please see an example of how to get the end balance of an account:
= IIF($Format=0 And (Account.Type] = 'I' Or 'Account.Type] = 'L'), -1, 1) * SUM( IIf( (GLHistoryByPeriod.FinPeriodID] = @StartPeriod And (Left( AHCurrent.AccountID], 5) = '10000' or Left(rAHCurrent.AccountID], 5) = '10000' or Left(rAHCurrent.AccountID], 5) = '10000') , IIf( AHCurrent.FinPeriodID] = null, IsNull(lAHLast.TranYtdBalance], BAHLast.FinYtdBalance]) , IsNull( AHCurrent.TranBegBalance], BAHCurrent.FinBegBalance]) ) , 0 ))
Note that this method is very manual. On the same row, you must create a formula for each field of the account columns (10000, 10001, and 10003)
Best Regards,
NNT
Hi @nhatnghetinh ,
Thanks for your help. Actually, I want to get the values for AccountID 70000 and 70001, and then calculate the difference. I tried your method, but it didn't give the desired result and returned a value of 0. Do you have any suggestions on how to resolve this?
