So, I want to know if it’s possible to get the SSRS report table of the Inventory WIP Reconciliation report (Recon_GUID and Recap_GUID) in a BAQ. This way, I could get the data in a dashboard and apply filters, sort, etc.
I would schedule the report to archive every day and I would also like to know if it would be possible to get the most recent data everytime I load the dashboard / BAQ.
The report data is stored in a different database. So you’d probably need to make an External BAQ (A BAQ that queries a database outside of the one that holds all the info E10 uses).
As for your second question, a dashboard can be setup to auto-refresh on opening. Or did you mean have the ext BAQ fetch the most recent data from the report DB? To do that you’d need to query the System Report table to find the GUID for the most recent WIP Recon report.
Thanks a lot, I was actually in the External BAQ Designer to set up a data source and try it there. It confirms what I just thought.
Yes I meant the most recent data from the report DB. What is the System Report called? Is it on the original database or is in on the SSRS database? I can’t find it
The table you’ll find the GUID in is Ice.SysRptList.
Here’s a screen shot of a BAQ of Ice.SysRptLst, and the System Monitor
I’d suggest that your BAQ also checks the User Description (SysRpt.Lst.RptNote) for a value you enter when in the scheduled running of WIP Recon. That way your BAQ wouldn’t grab the data from a WIP Recon run more recently by someone else.
Great idea for the description.
However, from the external BAQ Designer, I don’t have access to the Ice.SysRptLst table. So, the only solution that I see would be to create a new VIEW in SQL Server (in the external SSRS database) that would link the Ice.SysRptLst and the SSRS tables and get the data that I want in the view. The External BAQ is able to get the data from the view
Thanks for your help!
Can I ask why you even need to do this?
Is there a condition where running the WIP Recon report on a later date (with the same parameters) would give you different information? Other than if transactions were posted or not.
If you use the Transaction System Date option, the report run for a certain date range (with the same other parameters) should be identical, no?
Also, I don’t think an extBAQ would really work, as the table name must be known when designing the extBAQ, and the temp table hasn’t been created yet.
Maybe in a std BAQ you could use LINQ in custom code to get the data you want.
Scratch the, I was thinking of BPM when I mentioned Custom code.
I’m not sure that I understand your question, sorry. If I schedule the report to run every hour or every day, I guess it will create a new table every hour or every day in the SSRSData database?
So, I would get Recon_GUID1, Recon_GUID2, Recon_GUID3, Recon_GUID4, etc.?
I want to get the most recent WIP Recon data so I thought that the only way was to do that.
If it’s always the same GUID even on a scheduler, it wouldn’t be a problem. (To be fair, I haven’t test it yet because I don’t know how to do a recurring report and I don’t have any option right now except to run it for ‘Now’)
I think I mistook you wanting snapshots of the WIP Recon once a day.
What you could do is to make a Report Style for WIP Recon, that is Excel Friendly (just the data in columns). Then Use DMT to upload that into a UD table, and link the UD table in your standard BAQ.
Here’s what I would do (FTR- this is very hacky):
Great solution, I don’t see why it wouldn’t work.
Instead of a view, I thought about doing a stocked procedure in SQL that would be called hourly (or daily) and that would just create a new table ‘WIP_Recon’. The reason for the stocked procedure is that a VIEW can’t use a variable table name, so I can’t easily get the table from the Recon_GUID table since the GUID is variable. With a stocked procedure, I can just get the GUID and after get the data from the report table and finally insert everything in WIP_Recon (my custom table)
If it doesn’t work for any reason, I’ll try your solution. Thanks for helping me, it was really helpful !
But back to my original question, does the WIP recon do something that you can’t do in a BAQ?
As far as I know, there’s no easy way to get the data from the WIP Recon report when searching for the unposted data. A director in our admin department prefer Epicor Dashboards vs. the Epicor Reports that needs to be generated (it’s long sometimes).
Also, to have better control on what they want exactly (they will apply multiple filters everyday to search in this data) and since the base report is in PDF format, they would have to regenerate it every time.
And to be fair, the fact that we can easily get the data from a system Epicor Report with this procedure is a great way to easily replicate Epicor complex queries and we’re sure that we won’t left something since we’re using exactly the same data. It’s long to do the first time but if I need to do it again someday it probably won’t take much more than 1 or 2 hours.
And honestly, i tried to replicate the WIP Recon in a BAQ but it was way too complex (there’s like 50 types of transactions) to do
That’s all I needed to hear. Good luck with it.
@Stevensi1018 Go vote to add more data to the WIP Recon report. It’s currently under consideration but only has a single vote.
