Martin Horton posted an example on his Blog how to execute a SQL Procedure from a BAQ. I think this is promising, as long as you keep it on the dbo.* object and not Epicor!
You can also do Cross-Database SELECTs and create a few nifty Procedures that you think you will re-use! How many times do you have to repeat in a BAQ “Get Default Plant Cost ID”
But I am curious what @Bart_Elia and others alike @danbedwards @josecgomez @timshuwy @Edge @aidacra etc think of this? Bad, Good if Used Properly?
Martin Writes:
BAQ’s have always been very useful things, especially now with the sub query function allowing better use of aggregates. Calculated fields, in BAQ’s, have always been a very important part of the creation of BAQ’s and ultimately Dashboards. But in the past the function set available has been quite limiting - especially in the older versions of Epicor ERP. External BAQ’s in ERP E9 would be used to enable the more useful aspects of SQL - like it’s excellent function set. An External BAQ’s would also give you access to user defined functions. Sometimes you need a custom calculation that SQL doesn’t do as standard.
Something that I’ve not seen commented on or suggested (which is why I thought I’d share it here) is the ability to use more than just the standard set of Epicor built in functions. All the basic ones are there for conversion and string manipulation but what if you wanted something like DateAdd or DateDiff - two very useful built in SQL functions but not in the Epicor basic set? Well the good news is you can use them! The Calculated Field screen will allow the use of any SQL statement that would work in SQL Server Management Studio (SSMS). Think of the Calculated Field screen as a smaller version of the query window in SSMS. This ability isn’t advertised in the screen but it is there.
To demonstrate, I created a little function in my SQL DB. It’s a little pointless but it’s just to demonstrate it can be done. It takes a gross order amount and returns the value without tax on it.
With that added, I’ve gone into Epicor ERP and created a new BAQ, added the Erp.OrderHed table to the query and then gone to add my calculated field.
I usually keep my functions and stored procedures away from Epicor’s schema for tidiness so I always leave them in the dbo schema as default. So in the above screenshot, I’ve fully qualified the name with the correct schema for it but I’m using my custom function which obviously isn’t in Epicor’s palette of functions.
If you save that, I’ve added the order amount field on to the BAQ as well just to show it’s calculating correctly. So the BAQ will return 2 columns of data.
Now if we run the BAQ by on to the Analyze tab and pressing analyze you will get the results.
@Chris_Conn any thoughts sir?
