if you have CRM, then the person running the BAQ will have different results depending on their assigned workforce ID. This is because CRM adds additional filters to every query when the Customer (and/or OrderHed?) tables are on the query. This is all done in the name of SalesPerson Security.
SO, you should look at the person to see what sales region / territory they have assigned.
2 Likes
We do have some people with a CRM license and some that don’t have it. The only work force ID they have access to is a shared one with all territories.
It turns out the other debt person is also running into this issue though. Friday we were on 10.2.400 and they were able to run it. We upgraded to 10.2.700 over the weekend. Since then, they haven’t been able to run it, though everyone in IT can.
Ok, we have narrowed it down further. The issue is that it’s cross company. When we uncheck it, it works for the current company. Yet the people running the report DO have access to the companies involved. I’m thinking I may have to open a case with Epicor support.
It’s not JUST that I don’t think, there has to be more. Our boss has the same issue with the report (which is how we figured it out, he was willing to test), the report times out when Cross Company is checked, but not when it’s un-checked. Updateable is fine. We had him compare with a user who CAN run it, and even setting all the same options didn’t make it work.
I hate things like this.
I just looked at your first screen shot. It’s a timeout issue. The security stuff that Epicor adds to the BAQ can be a drag on the BAQ. When you uncheck cross company, it effectively reduces the amount of data coming back. If you add that, but filter something else down, it could probably work.
The fix for this is a lot of times looking at indexes and doing some SQL tuning so that the query can run faster. Unfortunately this takes some skill and is hard to explain.
1 Like
Thanks for the reply 
The problem is that when I run it, or other people in IT (besides our boss) run it, it comes back very quickly. 247ms this last time. So why is it timing out only for them?
Could be a cache thing. In the BAQ try going to the execution settings, add a setting, like shown and see if it fixes it.
If you remove the OrderBy’s, does the BAQ run for those who it times out for?
You could then apply the ordering in the RDL
@MLamkin If you add it to a dashboard it won’t time out. Won’t go any faster, but should finish. Curious why you have any users that are able to run BAQs especially updatable.
Actually, it IS in a dashboard and times out. I just had them test the BAQ directly to make sure that was the issue.
I was able to fix it finally, I had to bump the execution settings QueryTimeoutValue up considerably. We’ve had to do that before, but that was when the timeout was bad overall, this is the first time we’ve had to do it because of one group failing while others had super fast results.
I appreciate all the help though, it led us to trying more things until we figured it out
@MLamkin Glad you got it working. I wonder if @timshuwy nugget on OrderHed and Company from here applies to this baq.
It was an interesting read, thanks
I tend to not bother with company for the primary table in BAQs but use Company in all the joins. In this particular one, we need Company on the joins, but we want to see Orders regardless of which Company they might be in. (it’s purpose is for Debt to be able to see which Orders are on hold, and which ones have had a hold removal requested, which is what we are using UserChar2 for). It’s updatable so that the Debt person can release the hold. (the dashboard also has a button which will launch the credit manager screen for the appropriate company and customer.) That and a similar one for Quotes (which is also on the dashboard) are really the only Cross-Company BAQs we use, other than built in Epicor stuff. With the auto-refresh set for every minute, they can sit in the dashboard and manage the hold removal requests instead of having an email chain for each order.
I’m pretty sure that it’s just the join to customer where the company join slows things down. Since CustNum is unique to the whole database, you can usually get by without having company in the join to the customer table.
We have multiple Companies, and the key is Company, CustNum. Going into SQL directly, CustNum is NOT unique by itself. If it was, then yes, we could avoid using Company
Interesting! I thought it was. I stand corrected.
Kind of a side topic (mods feel free to split this off to a new topic)
Does SQL optimize a Query expression, or pretty much process it as written?
The expression posted (slightly reformatted for readability) is:
select
[OrderHed].[Company] as [OrderHed_Company],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[EntryPerson] as [OrderHed_EntryPerson],
[OrderHed].[OrderAmt] as [OrderHed_OrderAmt],
[Customer1].[CustID] as [Customer1_CustID],
[Customer1].[Name] as [Customer1_Name],
[OrderHed].[UserChar2] as [OrderHed_UserChar2],
[UserFile].[EMailAddress] as [UserFile_EMailAddress]
from Erp.OrderHed as OrderHed
inner join Erp.Customer as Customer
on OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
inner join Erp.Customer as Customer1
on OrderHed.Company = Customer1.Company
and OrderHed.BTCustNum = Customer1.CustNum
and ( Customer1.CreditHold = 1 )
left outer join Erp.UserFile as UserFile
on OrderHed.UserChar2 = UserFile.DcdUserID
where (OrderHed.OpenOrder = 1 and OrderHed.CreditOverride = 0)
order by OrderHed.UserChar2 Desc, OrderHed.Company, OrderHed.OrderNum
The WHERE clause applies to just one table (OrderHed), and the main tables are all joined with inner joins.
I’m no SQL expert, but that looks like a dataset is created based on the joins, then filtered by WHERE clause criteria, then sorted.
Would applying that criteria to OrderHed before all the joins reduce the number of matches required between OrderHed and Customer (as well as those between Orderhed and Customer1) speed things up?
Would the best way to do this be to make a subquery that first pares down the OrderHed table to just those records where (OrderHed.OpenOrder = 1 and OrderHed.CreditOverride = 0) Then use that subquery in place of the OrderHed table on the top level.
I’m assuming the expression above was generated for a design that used Table Criteria on OrderHed. Is that the case @MLamkin ?
Yes, the actual BAQ design is
If I were writing the raw SQL I’m sure it could be coded more efficiently, but I have found that in nearly all cases, SQL server itself is almost never where things are slow. I copied and pasted that code into a query window and tried it, the execution time displayed was 00:00:01, in otherwords, <= 1 second.
For this query, one could possibly speed it up by using the link from OrderHed to Customer1 (which is where the other credit hold factor we need is) and then linking to the other tables. And when we were testing, it’s the CreditHold field from Customer1 which is the big issue (there’s a warning in BAQ about security for it), removing a reference to it made the query work fast for everyone.
The combination of Cross Company and accessing that field
seems to be what is making it slow for some people and not for others.
I wish I knew more about the why behind it.
If you need that criteria of Customer1.CreditHold = 1, you can take it off the Customer1 table, and add it as a SubQuery Criteria. That should move it down into the WHERE clause.
When you upgrade some of the datalink become unlinked in the BAQ. The quick and simple solution is to open the BAQ and resave it. This has worked for me each time we upgrade.
I’m running into this situation for the first time and am stumped…
BAQ runs in ~3 seconds for some users and times out after 30 seconds for others. Maybe 10 people have tried it and half of them time out, other half are fine. Single Company, on prem, v10.2.700, sql server 2019. BAQ is not updateable, Cross-company is not checked, All Companies is checked.
I looked at Territories, WorkForce, Buyer, User, Employee setup and can’t find any rhyme or reason to explain who is slow and who is fast. We’re pretty sure we’ve ruled out client-side variables; it’s gotta be something inside Epicor.
BAQ has 3 tables in the upper level (Part, PartPlant, Vendor) and there are 3 subqueries left joined to PartPlant. Sub1 has PartWhse and Warehse, Sub2 has PODetail and PORel, Sub3 has TFOrdDtl. There is criteria for plant = currentPlant on several tables, both upper level tables and subquery tables.
Any ideas?
anybody tried running field permission reports on the display or join fields with this kind of issue? I had one that didn’t display the usual warning in BAQ analyse, but the report (can’t remember its real name) on field-specific security turned up an obscure deny permission. Nobody admits to having changed it, but when we went live a dozen persons could have.