Solved

Adding additional periods on AP aging report AP631000.rpx

  • 13 March 2023
  • 6 replies
  • 204 views
L
Rank
Userlevel 6
Badge +5

Hello,

I am working on adding additional periods to the AP aging.  AP631000.rpx

I can manually set the bucket within the report successfully.  However, I cannot add addtional buckets.

Original pulls days from AP settings.

= Payments.GetBucketNumberForAgedReport(
    @AgeDate,
    $DueDate,
    0,
    CInt([APSetup.PastDue00]),
    CInt([APSetup.PastDue01]),
    CInt([APSetup.PastDue02]),
    False,
    False)

 

I manually set the days and this works as expected.

= Payments.GetBucketNumberForAgedReport(
    @AgeDate,
    $DueDate,
    0,
    15,
    30,
    45,
    False,
    False)

 

However, if I add another value, so I can have an additional bucket value, it fails with a trace error.

= Payments.GetBucketNumberForAgedReport(
    @AgeDate,
    $DueDate,
    0,
    15,
    30,
    45,
    60,
    False,
    False)

It seems like “= Payments.GetBucketNumberForAgedReport” may be referencing a stored procedure or some other procedure outside of the report.

Can anyone shed any light on this?

Thanks for any insight!

icon

Best answer by aaghaei 14 March 2023, 18:08

View original
If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

6 replies

aaghaei
Rank
Userlevel 7
Badge +8

Most of the report functions live in “PX.Objects\CA\Descriptor\ReportFunctions.cs”. You can call the out-of-box functions as per their given parameters. in your case it is:

 

public object GetBucketNumberForAgedReport(

DateTime? reportDate,

DateTime? dateToAge,

int? dayBucketBoundary0,

int? dayBucketBoundary1,

int? dayBucketBoundary2,

int? dayBucketBoundary3,

bool? isByFinancialPeriod,

bool? isForwardAging,

int organizationID)

{

//Do stuff

}

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
L
Rank
Userlevel 6
Badge +5

Reza THANKS for the response!

I appreciate your time and effort!

I wanted to look at the logic behind this function but cannot fine it in the DAC browser.

It appears this function returns the bucket number which is used to assign the proper bucket for the AgeBal variables.

My overall goal is to be able to add additional aging buckets to the report.

So it seems I need to alter this procedure to add additional buckets.  I am unsure how to do that and prefer to avoid an actual customization if not required.

I could possibly hard code the criteria for each bucket variable if I knew the logic of this procedure.

Do you have any insight into what that procedure’s logic is do I can hardcode it on the AgeBal variables?

 

Instead of “= IIF($BucketNumber = 2, $DocBal, 0)” it seems I could use something like “= IIF(SomeDate-currentDate>7 and SomeDate-currentDate<15 , $DocBal, 0)”

Or possibly editing the “Payments.GetBucketNumberForAgedReport” procedure to add additional buckets is easier that I think.

Any insight on how that is done?

I am not of the best path forward and would sincerely appreciate any guidance.

I will be hardcoding the bucket periods no matter what path is used.

THANKS!

 

 

 

 

If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
aaghaei
Rank
Userlevel 7
Badge +8

I provided the link to the website source code in my first comment if you want to look into the C# Code. I do not think that you can bring that code into the Report Designer directly considering its complex nature.

Alternatively, if you want to hard code you could do something like the below for your periods. I am using the below in GI for the 31-60 days bucket based on today's date for AR Aging but the same applies to AP Aging. You can replace today’s date with the cut-off date input of your report if you want.

=IIF(DateDiff('D', [ARInvoice.DueDate], Today())>30, IIF(DateDiff('D', [ARInvoice.DueDate], Today())<=60, [ARInvoice.CuryDocBal],0), 0)

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
miguel80
Rank
Userlevel 5
Badge +1

Hi,

You can probaly use the Switch function in the Bucket Number variable:

=Switch(SomeDate<7,1,SomeDate<14,2,SomeDate<21,3,...)

The switch command stops as soon as a condition is met so you don’t need to set ranges on each condition. 

With that you can get custom buckets for this report.

Could that work for you?

Miguel Q
L
Rank
Userlevel 6
Badge +5

I am working through this, and it looks promising!

I’ll let you know when/if I get it.

I just wanted to say thank you to you both for your help!

If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
L
Rank
Userlevel 6
Badge +5

Thank you both!

Utilizing your suggestions I was able to get this completed.

Thank you for your time and efforts!

If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved