I am going to drop this here as a reminder that we ARE making some really cool features that you have always requested. This feature is coming with Kinetic 2024.2 The image below is from the Feature Spotlight video that everyone will be able to view as part of our release.
Here is one of the great new features (also referenced here: Is there a way to create a BAQ from its SQL code? )
This was in Epicor Ideas with 84 votes: Log In - Epicor Identity
AI AI Captain!
Is this the first instance of an Epicor Idea being thrown into Prism?
initially it was going to be done with prism, but instead, we were able to do it without prism I believe. It is “simply” back engineering the BAQ. Once it generates the BAQ, it still uses all the normal BAQ security, applying whatever user security the user has, so just because you can create a SQL Query doesn’t mean you can override core security features.
Very cool! Do you know if it will be compatible with Table- or Scalar-value SQL Functions?
My guess, and I could be wrong, but if you can’t do it in a BAQ today, I’m doubting you’ll be able to do it with a tool that converts to a BAQ.
That’s my assumption too. Converting to BAQ will apply the BAQ input santitization, so if that flags on APPLY then you’re not gonna be able to join parameterized functions.
On the other hand there’s currently no support for APPLY in BAQ and I haven’t made the effort to inject one to find out if it’s hard coded into sanitization or if the Scratch-ish UI will catastrophically faceplant.
Regardless, I often want APPLY for one thing or another and that’s going to be the first thing I try the minute I have the chance. Either way it goes, writing SQL as text will be a great improvement to my quality of life! I am approximately this excited:
if you have a sample simple function to test, I can push it through the converter to see what it does. do something off partbin or partwhse to see.
Separate from the conversion tool, have you had performance issues with your functions. I used to use them but the performance on the queries went to pot. Now, that was a while ago (2008 or so). Doing a quick search at Brent Ozar’s site, it doesn’t appear that things have gotten any better.
Next step, the ability to manually edit the SQL directly in the BAQ? (Wishful thinking I know)
So the Scalar-value functions I have in use are for stored procedures that I use as Data Sources for SSRS, and have never noticed any performance issues. Generating and printing 600+ SSRS reports per day, I’ve never seen anything like the 6000+ ms query execution time he described by using a single function in a query, and several of mine have multiple functions that are executed multiple times each in a query. One thing I did see in that first article is:
Blockquote
Until SQL Server 2019, if your query has a scalar function anywhere in it, your entire query plan goes single-threaded. Ouch. (Ironically, the contents of the function are allowed to go multi-threaded, so…yay.)
So all of these data sources are running on SQL 2019. I don’t know that I ever actually used any scalar-value functions prior to using SQL 2019, but I don’t remember ever having issues.
It took 10-ish years to get this so maybe in another 10. 
Maybe next step should be plain english to BAQ.
That might be where Prism comes in…
I was thinking that since it is basically what they were demoing at Insights as part of the AI, the ability to ask it a series of questions and from that get a result set back along with the option to turn it into a dashboard.
It would probably be easier to do Human → SQL → BAQ. Then they dont have to train a specialized model on the Epicor BAQ XML.
I do my BAQs in Excel then convert csv to xml and import 
lol only when it has alot of unions. It was easier to have AI work with a CSV and adjust it and then my csv2EpicorBAQ command line utility converted it back to a proper XML. But in general it was easier for me to make sure I have all the formulas the same and naming and positions.
I think Prism will work best if they build helpers and help the data be formatted half-way there, looking forward to it.
well, in a way, with this new feature, you will be able to do that, but not directly. Since BAQs already show you a SQL Statement, you can copy that, edit it, and then paste it into a NEW BAQ. We just are not allowing you to edit it directly in an existing BAQ. I am sure that someone will make an Epicor Idea for that one someday 
Agreed but like you say, import it into a NEW BAQ, which means if you have a dashboard or something connected to it you need to update that to point to the new BAQ, would be nice to be able to update the existing one through the import, or just make it directly editable.
Don’t get me wrong, I love this feature, just would be nice to be able to directly edit the code being produced by the BAQ, it is often nice to start with the GUI of the BAQ and then switch to code to refine it.
The workaround for that would be to import is as the same name, and it will connect fine (you have to delete the original BAQ first).
I’m assuming the “Generate from SQL” command is making a lot of assumptions/defaults with the extra data that’s need to save a BAQ and updating it with just the SQL is probably not going to save that info if you’ve changed anything so they disallow it. Kind of like when you “Customize” a tracker view in the dashboard editor (Which I argue you should never do)
