Getting Fiscal Calendar Start / End Dates in BAQ

Epicor ERP 10
,
1

I have a baq with parameters for Month and Year. I then use these parameters to get the fiscal period start and end dates from the FiscalPer table. I created an InnerSubQuery called FiscalCalendar with the FiscalPer table by itself. The table criteria limits data to fiscal month and year input by the user.

In another InnerSubQuery I have the following as subquery criteria: orderrel.reqdate <= ANY value(s) of FiscalPer_EndDate field from FiscalCalendar SubQuery. This gives me the ending date for the fiscal period since the table data is limited in the subquery using table criteria as mentioned above.

I use this same technique in another innersubquery to get invoices where the apply start / end dates are based on the same “ANY value(s)…” used above.

The problem is that the BAQ takes an extemely long time compared to when I just had the user enter the dates.

Snip of the FiscalCalendar Subquery with Criteria:

image
image768Ă—419 19.7 KB

Snip of usage of FiscalCalendar Query in Criteria:

image
image1009Ă—655 36.1 KB

Any help would be greatly appreciated. Should FiscalCalendar be a CTE maybe? Thanks Tony

2

Try adding Company and Fiscal Per to your SalesBacklog subquery. Join Company on OrderRel and then join FiscalPer to Company. Set the same criteria on FiscalPer you have in your current subquery (the parameters). Change the critera on ReqDate to be <= FiscalPer.EndDate. I believe this will speed your query up quite a bit because you’re not relying on the subquery.

3

Many Thanks! This worked like a charm. I had all the pieces, but they weren’t in the puzzle correctly. BAQ runs instantaneously!

1 Like
4

Awesome! Thankfully, I just did something similar recently. Glad to help.

5

This helped me with a similar request that I had!

1 Like