I have a couple dashboards that I am getting some users say it will not return results. I did basic troubleshooting. It works for me and other users. No security settings on the BAQ that runs the dashboard. The only thing I can think of is that we had an unexpected update last Friday by our IT support company where they changed something with our Authentication server for Windows and it caused people to get kicked out of Epicor and ultimately required a restart to the Epicor server. Any ideas what else to check?
Could the BAQ be timing out? If some users get results and some don’t, I wonder if the BAQ might just be on the borderline of the timeout setting.
One of my dashboards I could see that. And I thought about how it shows all part records and that only grows over time so perhaps we’re just now bumping up against some limiting factor. But the other dashboard is a fairly simple exploded BOM costing dashboard. Yes it uses recursion but it’s fast. One BOM I was given loaded in less than a second. You know? No way that’s bumping against any limitations. I will check our error logs though and see if that helps me with anything. Thanks for the suggestion.
1 Like
Is the customer table in the BAQ anywhere?
1 Like
Not in either case. Good thought though. I wouldn’t have thought of that but I’m guessing you were thinking maybe has to do with territories and sales person visibility? Or something else?
Yeah, territory security is tied to the customer table, and catches a lot of people off guard.
You said you looked at the event logs on the server? If it’s a data problem (bad SQL error) you should see it there.
As far as “speed” goes, be careful how you think about it when it goes super fast for you but times out for someone else. There are caching problems that can happen, and if you are testing with the same parameters/filters, you can get very different results than someone using new and different filters.
One thing you can try is to add option (recompile) to the query. If you’re still on 2021.2 you’ll have to hack it into it. Usually I would go into the execution settings, add and order by then in the field type 1 option (recompile) which will add that onto the end of the phrase. That will make SQL rethink it’s plan every time so that it’s not doing something optimized for a totally different result set.
If you are on a newer version option is one of the thing that you can set. It’s called Query option, and you can type in recompile.
In newer versions
In older versions without query option. (note, if you have an order set, this won’t work, and you’ll have to get more creative to tack it into the end of the query)
5 Likes
It would be good to know if the issue is at the PC or at the user level. Login as yourself on your PC and try the BAQ, then login as the affected user on your PC and try again.
I assume you have checked security groups to see if the user is excluded from some things.
Also, I would want to see the System Monitor showing all tasks to see where those tasks ran (and/or failed) for the affected user.
Next, I would want to see the BAQ in question. Does the BAQ work for the affected user, while the dashboard does not?
1 Like
This is the error that I’m finding. It doesn’t happen when I run the BAQ/dashboard, but it does error when I run the BAQ as one of the users who is not able to get the dashboard to work.
Ice.Common.EpicorServerException: Operand data type NULL is invalid for sum operator. —> System.Data.SqlClient.SqlException: Operand data type NULL is invalid for sum operator.
That leads me to think there is an issue with the data returned to one user. Maybe a column of data (that is getting summed) is not available to the user (UD fields?). Try editing your sum to utilize a coalesce or isnull to account for possible null values.
2 Likes
oh my goodness I know what it is… bahhh We disabled part transactions while we complete a physical inventory and I set the PartBin.OnhandQty field security to default to None. I am a security user so I can still see that field. But in my BAQs, they both refer to on hand qty. A typical user would not be able to access OnhandQty at all. Obviously, the point was to prevent anyone from writing any new records but evidently it also messes up viewing the qty. We may want to rethink how we accomplish that lockout for next time. 
4 Likes
I need that shirt bad.
2 Likes
How do you know your security is working?
When you start messing up legitimate work…
3 Likes