Hello, the daily AR and AP reports can easily be exported to Excel via SQL query.
The year end report (or any other dated report) is a typical report that can only be created via Epicor itself.
Is there a possibility to get a dated report via SQL query? What I get is only “today”…
Thanks
Mark
Does anyone know if the above is possible?
Or is the report created by Epicor the only solution? I assume that reports “sees” a date somewhere when the AR/AP reports are created. I wonder if that field can be part of a SQL query…
Are you looking to run the Epicor report with SQL or to recreate the report with a SQL query?
Which reports are you referring to?
A good place to start would be the zHomepage BAQ’s which include APAgedPayables,APBalance,ARAgedReceivables and ARBalance.
are you wanting a snapshot report as of a specific date?
I mean the Aged Receivable and Aged Payable reports.
The standard Epicors can be created based on a date in the past.
I would like to be able to create these via SQL to Excel, like easily done for today (not a passed date).
Reason is that we have about 12 companies in Epicor, so instead of having to print all these reports I want them in Excel so I can easily check if AP/AR matches between all the companies - on any date.
I have it setup for “today”, but wonder if it’s possible to set a date in the past.
Epicor can do it with their standard report so I wonder if it can be done via SQL
You can write a cross company BAQ I wouldn’t do this in sql not sure what the benefit would be
But yes you can create these reports in a query
I prefer the SQL method so I can easily use Excel to calculate and use Lookup formulas etc…
I wonder which field exactly would be used (in SQL or BAQ) to get the report show the details of a certain date.
I cannot find this field.
The Aged As Of Date is just
Specifies the aging date for both the Due Date and Invoice Date methods to compare against in the report.
I read this as you are not getting a snapshot as of that date but you are just changing the calculated date difference for the current open invoices in AR.
I would duplicate the zHomepage_ARAgedReceivables BAQ and replace the Constants.Today in the calculated Aged Column with a date parameter. I would assume that this is the calculation being done in the report as well.
If you want to actually see the AR status as of a specific date you would need to add logic for where the Apply Date is before and the Closed Date is after the selected date. That should give you the open invoices as per a specific date.
