BAQ Help - Adding Fields from GlJrnDtl Results in Execution Timeout Error

I have a BAQ for LaborDtl working well. Returns results in 315.8742 ms. I now need to gather Fiscal Period for these transactions. Epicor support says to join LaborDtl with TranGLC, and then join TranGLC with the GLJrnDtl table. I believe I have all of the table relations correct, as I followed the KB Article notes on it.

However, as soon as I add GLJrnDtl.FiscalPeriod to the select statement, the BAQ times out. I’ve changed the execution settings to allow queryTimeOut = 120 (according to a KB article, the default is 30, so this should be a significant increase). Still nothing.

Does anyone have pointers on getting past timeout errors? I would prefer to come up with a more efficient solution than to keep increasing the allowable execution time.

Or does anyone have other ways I could gather the FiscalPeriod for the LaborDtl results?

FYI I’ve been working with Epicor support on this, and they’ve said they can no longer help, so here I am :slight_smile:

Would you be able to post the query phrase? That would help if the query needed review.

so the query runs fine with all the tables being joined.

when you add a new column, timeout occurs?

Hi Ken! Yes, that is correct. All is good until I add the new column from GLJrnDtl. Here is the query phrase:

select distinct
[LaborDtl].[LaborType] as [LaborDtl_LaborType],
[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
[LaborDtl].[LaborHrs] as [LaborDtl_LaborHrs],
((case when datepart(month, LaborDtl.ClockInDate)=datepart(month, Constants.Today)
then 1
else 0
end)) as [Calculated_monthly],
((case when (LaborDtl.ClockInDate >= Constants.FirstDayOfPrevWeek) and (LaborDtl.ClockInDate <= Constants.LastDayOfPrevWeek)
then 1
else 0
end)) as [Calculated_weekly],
((case when LaborDtl.LaborType=‘I’ then Indirect.Description else OpMaster.OpDesc end)) as [Calculated_Code],
(LaborDtl.LaborHrs*(48)) as [Calculated_Cost],
(datepart(month, Constants.Today)) as [Calculated_CurrentMonth],
[LaborDtl].[TimeStatus] as [LaborDtl_TimeStatus],
(convert(varchar, Constants.FirstDayOfPrevWeek, 101) + ’ - ’ + convert(varchar, Constants.LastDayOfPrevWeek, 101)) as [Calculated_PrevWeekDates],
[EmpBasic].[Name] as [EmpBasic_Name],
[LaborDtl].[JobNum] as [LaborDtl_JobNum]
from Erp.LaborDtl as LaborDtl
left outer join Erp.Indirect as Indirect on
LaborDtl.Company = Indirect.Company
and LaborDtl.IndirectCode = Indirect.IndirectCode
left outer join Erp.OpMaster as OpMaster on
LaborDtl.Company = OpMaster.Company
and LaborDtl.OpCode = OpMaster.OpCode
inner join Erp.EmpBasic as EmpBasic on
LaborDtl.Company = EmpBasic.Company
and LaborDtl.EmployeeNum = EmpBasic.EmpID
left outer join Erp.TranGLC as TranGLC on
LaborDtl.Company = TranGLC.Company
and LaborDtl.LaborHedSeq = TranGLC.Key1
and LaborDtl.LaborDtlSeq = TranGLC.Key2
and ( TranGLC.RelatedToFile = ‘LaborDtl’ )
left outer join Erp.GLJrnDtl as GLJrnDtl on
TranGLC.Company = GLJrnDtl.Company
and TranGLC.FiscalYear = GLJrnDtl.FiscalYear
and TranGLC.FiscalCalendarID = GLJrnDtl.FiscalCalendarID
and TranGLC.JournalCode = GLJrnDtl.JournalCode
and TranGLC.JournalNum = GLJrnDtl.JournalNum
and TranGLC.JournalLine = GLJrnDtl.JournalLine
where (LaborDtl.Company = ‘CTS’ and LaborDtl.ClockInDate >= convert(datetime, convert(char, Constants.Year * 10000 + 01 * 100 + 01), 112) and LaborDtl.ClockInDate <= convert(datetime, convert(char, Constants.Year * 10000 + 12 * 100 + 31), 112) and LaborDtl.LaborHrs >= .25)

Sorry it’s kind of hard to read. I can’t figure out how to keep the indents when posting here.

Interestingly, when I change from “select distinct” to “select all,” the BAQ doesn’t time out. But I end up with tens of thousands of rows, which doesn’t make any sense to me. The LaborDtl results were only giving me around 9200 rows before I added the TranGLC and GlJrnDtl tables.

@jnbadger Epicor has a few database functions that you can call from inside of the BAQ. In this case if you want to get the Fiscal Period of a date you can just use the Erp.FiscalPeriod() function. To call this you just need to create a calculated field with an int data type and enter any parameters required. So Erp.FiscalPeriod(LaborDtl.Company,LaborDtl.ClockInDate).

@hkeric.wci wrote a post a while ago about building your own custom ones.

3 Likes

Wow, fantastic!! I’m surprised I’ve never noticed that function. I’ll just have to hope that the recipients of this data are okay with the fiscal period according to the ClockInDate and not actual date posted (that’s usually a week or so later for us).

Thanks so much, John!

You can try putting TranGLC in a subquery so it runs only once.

1 Like