Transforming AR aged report into a BAQ

Epicor ERP 10
1

Who has built BAQ before. Please share

2

Anybody who knows how to do this?

3

Found this thread with a quick search Aged AR Report in BAQ - #2 by lgraham

4

The BAQ gives the current status only. You cannot use it for historical data. An invoice might have been open in January but in february its closed. When you age by 31st of January it should appear in the aged report which is not the case on this BAQ

5

I think this may be a disconnect. An “Aged Receivables” only includes unpaid invoices by definition. An Average Days to Pay report sounds like what you are looking for possibly.

6

Have a quick look at how it works. If you chose age as last week Tuesday you will get different data when compared to Age as of Today

7

That BAQ yes, you’d have to create a more complex query to walk through the transactions from today to whatever date backward they’d want. I’m not sure a BAQ could do this without timing out as I’ve never tried.

If they want the expanded buckets using current aging is fairly simple, just make sure they want it aged by either Transaction date or Invoice date as they can be different. If they’re wanting to do historical you’re going to be in for more heavy lifting.

Is it possible they’d be happy to use the stock report for the historical and a custom BAQ Report for the current aging into the desired buckets?

8

the AR Aging report that Epicor delivers does not use a BAQ to generate, because of the complexity to calculate the aging “as of” a date… if you are running it “as of now” then a BAQ could certainly be used, but if you want it “as of Jan 31 2023” then you must take the current balance and back out all payments, and back out all new invoices and adjustments… oh… and you have to age everything into buckets “as of” that date.

BUT i have often created a simple BAQ that does some form of aging report… it is not that difficult.

  1. you create several calculated fields each with an IF statement to determine if the value of the current invoice goes into that aging bucket
  2. the baq does this for every open invoice in the invoice table
  3. then you can choose to summarize this information and only show the data with one line per customer, giving a very high level aging set of data.

I have also created dashboards that show the summarized view by customer in the top card, and then when you click on the customer, that is published to the bottom where it shows the details of each invoice. But again… this is “as of NOW”…

1 Like