You guys write so complex queries that they are hard to translate in tabular representation of Ice.Query* tables. And many parts of the system relies on that representation. So free form query is not an easy task to implement
If I don’t need interactivity, I’ll build a query, build a SSRS report around it, and push it to the server, and drop a URL link in the Epicor menu.
Half the BAQ’s I create actually power some functionality (dashboards, UI drop-downs, BPM’s, REST calls) elsewhere in the system or wider environment. Sometimes I write a SQL query first to help me wrap my head around things since the BAQ designer isn’t very intuitive.
I’ve written one external BAQ, and that one just references the E10 db itself and I built it entirely in the BAQ designer. I did it because it was so complicated (multiple subqueries, maybe 2 dozen tables in all) that the system load of all the permission checks was bloating the update time to 10 seconds. External BAQ bypasses that and cut the refresh down to 1.5 seconds.
I’ve also recently learned LINQ, which is SQL’s dimwitted cousin living in C# land, and that is an extremely useful query language that has made some BPM’s quite feasible that previously were not worth the effort.
Right - but I assume there’s another point in there. Are you saying that in order to accomplish the other functions, you are forced to use BAQ Designer?
I never use External BAQs, so I don’t know how they play with others.
Not at all. They’re just the most convenient option. I mean, it’s hard to avoid BAQ’s if you want to build a dashboard, but you’re not really forced to use Epicor’s dashboards, are you?
I was told that Epicor has a way to register a SQL View with the BAQ Designer, and you can free form the SQL View… This was before the King of BAQ left Epicor, he said it’s complete - just no User Docs exist on how to Register it.
Yeah, I’m not going to make another poll, but maybe I should have allowed multiple selections. Might have been a better picture of what people do. I’m just wary of giving people too many options to choose.
I dont have a good way to show this… But I have one BAQ Query, completely built using the BAQ Designer that spans multiple pages when pasted into Word.
I have more complex ones, they run all under < 10 seconds. Definitely alot of Respect for the folks at Epicor who built the BAQ Designer.
There are alot of tricks folks don’t know about that the BAQ Designer lets you do. Recursive CTEs…
Being able to do Expressions in the JOIN or even running a SubQuery etc…
@Olga is right we use external BAQs a LOT. We replicate our Epicor DB into an Azure SQL Instance (transaction replication / real time) and we write a lot of BAQs using this external DB
Mostly to bypass the Territory and Company restrictions in Epicor that while useful sometimes cause incredibly painful issues and the way they build this in is by appending a bunch of CTE’s and on the fly views to any query you execute which can cause performance issues.
But Yes we have a lot of External BAQs though I will say @JasonMcD we don’t write VIEWS and then pull them in as external, we use the tables as they are in the external baq. There is one exception to this and that is we create a view that Splits SalesRepList into a table because
storing~shit~like~this~is~f*cking~stupid
So we have 3 views InvcHeadByRep, OrderHedByRep and… ProjectByRep which gives you one row per rep and their split %