I’m just reaching out for some guidance on Standard/External BAQs following a recent upgrade to Kinetic.
I’ve created:
a standard BAQ that looks at our current invoices in the system
an external BAQ pointing to a SQL table that holds our pre-upgrade data, in this case the old InvcHead table
My initial thought was that I could reference the external BAQ from the standard BAQ to achieve something like a union between the current and historical data. However, when I open the BAQ report designer and go to the overflow menu with the main (standard) query loaded, my external BAQ doesn’t appear in the list to select from.
Is there an alternative or recommended approach to combine current and legacy data in this scenario?
I guess what I’m trying to do is look at InvcHead data from both the current version of Epicor and the previous version to determine the last invoiced date.
Any pointers or examples would be much appreciated.
You can only look at one database at a time. So in the same BAQ you can’t do that. The best you can probably do is put both of the BAQ’s into their own grids on a dashboard. Depending on what you are trying to do, maybe a publish subscribe in the dashboard might work.
They can definitely work in separate grids, I’ve done it with an External BAQ selector filtering a middle grid (also external BAQ) and the middle grid filtering a lower grid (regular BAQ). But as mentioned above, you can’t simply join them.
Disclaimer : do at your own risk, those are some on-prem shenanigans. Not recommanded, not supported, not upgrade proof, not cloud ready, nobody will support you, etc
We had the same issue, our old data was in a different database that our current Epicor database. Here’s what we’ve done
With SSMS, Establish a linked server, from your current production SQL database to the old database
On the current production database, create a sql view in which you query the data from the old database
This may seems weird, but create a external datasource “CurrentDB” that point to the current SQL Database of your prod environnement.
Still on the current production databse, create an external BAQ and configure the BAQ to be connected to the datasource “CurrentDB”
Now, in your External BAQ, you will be able to reference both the SQL View (querying the old database) AND all the current database tables. You can join them together and do stuff
