Skip to main content
Solved

How to Display and Compare FinYtdBalance for Different AccountIDs in a same row in a Report?

  • 26 July 2024
  • 8 replies
  • 75 views

Hi,

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!

8 replies

nhatnghetinh
Rank
Userlevel 7
Badge +5

Hi @RKarunarathne51 

Please refer to the “Trial Balance Summary” report (GL632000.rpx)

 

Best Regards,

NNT

Hi Everyone. If a member helped you, please *Like* their post, and if a member provided an answer to your question, please mark it as *Best Answer*
R
Rank
Userlevel 1
Badge

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

nhatnghetinh
Rank
Userlevel 7
Badge +5

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

Hi Everyone. If a member helped you, please *Like* their post, and if a member provided an answer to your question, please mark it as *Best Answer*
Laura02
Rank
Badge +18

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

Laura ~ Klear Systems, Inc. Irvine, CA. ~
R
Rank
Userlevel 1
Badge

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

nhatnghetinh
Rank
Userlevel 7
Badge +5

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([AHCurrent.AccountID], 5) = '10000' or Left([AHCurrent.AccountID], 5) = '10000')
    , IIf( [AHCurrent.FinPeriodID] = null, IsNull([AHLast.TranYtdBalance], [AHLast.FinYtdBalance]) , IsNull([AHCurrent.TranBegBalance], [AHCurrent.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 Everyone. If a member helped you, please *Like* their post, and if a member provided an answer to your question, please mark it as *Best Answer*
R
Rank
Userlevel 1
Badge

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?

nhatnghetinh
Rank
Userlevel 7
Badge +5

Hi @RKarunarathne51 

Please refer to the attached GL632006.rpx file.

 

Best Regards,

NNT

 

1 Attachment
Hi Everyone. If a member helped you, please *Like* their post, and if a member provided an answer to your question, please mark it as *Best Answer*

Reply


Terms & ConditionsCookie settings