New Reports


(John Mitchell) #1

First of all keep all of the current reports as they are.

Step 1: Look at the top 3 - 4 longest running reports and the top 3 - 4 most used reports from the telemetry data.
Step 2: Rewrite them as SQL stored procedures
Step 3: Introduce them as new E10 reports at Insights and wait for the standing ovation when you show a report that takes 8 hours with the BO now takes 8 seconds.

These can still function the same as the BO’s by generating temp tables and passing those temp tables to SSRS to render.

Here is why I make the request: Run the AR Aging Report and look at how long it takes just for the SysTask to run it. In my case it was 69.75 seconds. Then run the zHomepage_ARAgedReceivables and look at the Query execution total time. In my case: 394.3897 ms. Then copy and paste the same BAQ code into SSMS (replace the Constant.Today with GetDate()) and run it with Time Statistics on. In my case: 318 ms.

So, for a very simple report:
Epicor BO: 69,750 ms (generation only, not the render time)
Epicor Supplied BAQ: 394 ms
Native SQL Query: 318 ms

That is a 177x improvement just with a BAQ and another 23% with a query. Our Stock Status report went from 9 hours to 1:20 (one minute and 20 seconds) with a query. That is over a 400x improvement. The issue is that SQL server is insanely fast, BAQ’s are relatively fast, and the BO’s are absolute dogs. I’m not saying get rid of the old dogs, I’m only purposing adding a few new ones for the absolute worst cases.


(Jose C Gomez) #2

I think the plan (as I understand it) with the creation of the Multi-BAQ Report Definitions is to move all reports to BAQs… eventually… But don’t quote me on that.


(John Mitchell) #3

That would sure be nice! When you trace the BAQ and run that code it still takes 393 ms. Which means you are only paying 75 ms for Entity Framework and all of the ICE tools. I don’t know if that would be the case with a much larger dataset like Stock Status or COS/WIP.


(Jason Woods) #4

There has been a lot of talk around moving the reports to BAQs. The major reason is the functionality. It is easy to manage a BAQ visually and it is easier to add new fields (and exclude fields). This would also remove the black box of calculated fields.
I would be surprised if we see this in 10.2.x, but maybe soon. I would bet that most or all reports will be converted prior to releasing this though. Major overhaul!


(John Mitchell) #5

There is an Uservoice request under review to allow SSRS to render REST datasets. Then the report would only need to make an API call and pull all of the data from the BAQ. https://feedback.azure.com/forums/908035-sql-server/suggestions/33245377-ssrs-json-datasets