Solved

Adding additional periods on AP aging report AP631000.rpx

  • 13 March 2023
  • 6 replies
  • 203 views

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

6 replies

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

}

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!

 

 

 

 

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)

 

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?

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!

Userlevel 6
Badge +5

Thank you both!

Utilizing your suggestions I was able to get this completed.

Thank you for your time and efforts!

Reply


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