Just for showcase here is another way to leverage Functions, if you ever need to repeat some tedious Formula over and over and over again. Again dont use this for non-trivial formulas, just more dependencies to juggle during upgrades.
That’s it for my passion for the tool I better stop showing all the tricks, many picked up from @josecgomez, before @pferrington considers them a risk in the cloud and patches them all
Hey Patrick I used to be able to free form the table and set it as a Peer or Temp Table in 10.1.500 and I could make a BAQ pull IM Tables whyd you patch it
Well, unless you know how to create function in the cloud database, personally i don’t see it as cloud problem
Also scalar functions are not good for performance either
SSMS is the easiest way for me to develop a query. Once I do that, I have the data set to compare when I move it into a BAQ. There are too many times that I didn’t click the right box or put the right prefix “(” or suffix “)” in, and my BAQ looked right, but the data was wrong.
Having it in SSMS first works for me as an audit before I put it in production.
I use BAQ designer mostly as source of forms and dashboards. Moreover, I find it easier to test subquereies, grouping etc… than natively write it in ssms.
BUT
I will transform such queries as views in ssms, for some users who needs Excel to analyze their data. They open the file, refresh data and that is it. Faster than using the BAQ.
Pierre
G.) I use BAQ Designer by choice even though I know how to use SSMS.
Well, y’all know why I shy away from SSMS and External BAQs: Security.
Unless everyone using external queries is adding logins with associated roles granting access to only the tables/columns those logins need… (Probably easier in Jose’s replicated database…)
So, most of the reasons for external BAQs are workarounds. I agree with @haso that having BAQs of BAQs might eliminate a fair number of these.
Are some of the other reasons for external BAQs related to implementation choices that require a more complicated query?
I use BAQs by choice BECAUSE they are the most upgradable.
But i also know how to create fairly complex C# LINQ statements in BPMs to gather data when needed. I have found that i can write more efficient LINQ statements than the BPM’s QUERY widget. Reason is that the query widget always returns values, but instead in LINQ, you can do an “.any” query which returns a true/false value, and stops querying if any record is found.
Example:
I find the BAQ editor easier to use for more complex queries. It’s easier for me to visualize what’s going on. I use SSMS a lot for getting quick data out because it’s faster, especially because we have a bazillion instances, and it’s easier to switch around. But by the time I start adding joins and sub-queries I’m better at that in the BAQ designer. I learned the BAQ designer well before I learned proper SQL though, so I’m still a lot more comfortable with it.
I usually write my queries in SSMS or Linq if embedded in a program and go straight to the database. Been doing this far to long to spend time with a designer that takes far longer and requires too many clicks to accomplish a simple task.
I’d agree with this. As I said above, I’ve only ever used one, and it was for performance reasons. Otherwise I’d have used a normal BAQ. In theory, eBAQ’s let you query other databases, but it’s a better practice to use API calls for that.
Confession: I did this once too. I was migrating an old Access application to an Epicor UD table. One requirement was to track who was assigned to the project. The Access DB had six columns, 1 for each name (because teams cannot have more than 6 people, ever. It is the law. ). The entire purpose of this was to list names out in a form we submitted to a stakeholder. So I totally just concatenated those columns (after converting the free-form text to Epicor PersonID’s) into one UD field and ran a CROSS APPLY + STRING_SPLIT on the SSRS side to spit the names back out.
I better stop showing all the tricks, many picked up from @josecgomez, before @pferrington considers them a risk in the cloud and patches them all 
(Probably easier in Jose’s replicated database…)
). The entire purpose of this was to list names out in a form we submitted to a stakeholder. So I totally just concatenated those columns (after converting the free-form text to Epicor PersonID’s) into one UD field and ran a CROSS APPLY + STRING_SPLIT on the SSRS side to spit the names back out.