Hello,
I need a way to view/pull the raw query(s) from an out of the box SSRS report. Specifically the inventory/wip reconciliation report in a Epicor hosted environment. Any ideas ? We are on 10.1.400.16
Standard E10 SSRS Reports utilize âReport Data Definitionsâ and âReport Stylesâ. Once you find the report based off the style. You can open the report, look at its data set expression and view the query there that reflects the E10 RDD.
1 Like
Thanks!!
The RDD for the Inv/Wip recon is just going to be some calculated fields if Iâm not mistaken. I donât think there is a way to see the Epicor magic that goes into populating those fields.
If someone smarter than me does know how though I would be very interested as I have spent many hours making BAQs for only a subset of the transactions that pull into that report.
I wish they gave documentation for all their magic fields.
1 Like
There is no query per se but a business object that builds temp report data tables that SSRS then renders. That being said, you can ALWAYS turn on SQL Profiler and pick up the actual query that is being run. Itâs just that sometimes the queries donât actually tell you anything about the business logic.
You can do this by by picking a single part with a single day and running the report a few times in Test or Pilot, (whichever system has the least number of active users). Then, after you know the report works and displays fairly quickly with the smallest dataset that you need, go to SSMS - Tools - SQL Profiler. Make sure you check the Show All Columns checkbox then Column Filters and select enter the DB name that you want to profile. Finally, start the profiler then go back and run the report. As soon as it finishes you can go back to profiler, stop it and start analyzing the queries that are made. This can be done with any BO, report, or anything that is reading or writing to the DB.
You will find a bunch of queries turning the Entity Framework code into actual SQL. If you filtered the report by a job number you can search for that job number to go right to the queries that were ran. In this case it pulls from JobHead, LaborDtl, PartTran, JobAsm, JobOper⌠etc. etc.
You can also look at the reporting tables that are created by finding the report ICE.SysRptLst and Queryng:
SELECT *
FROM sys.Tables
WHERE name LIKE â%{Report GUID}â
When I tested it there were 5 tables created but I think you only care about the Recap and Recon tables.
Now I think what you are really asking is how is the Recon table created? For this you can look at the Erp.Services.Rpt.InvWIPRecon.dll which builds the dataset and passes it to Erp.Internal.IM.IMR70 which runs it through the posting engine and writes the Recon table one line at a time (which is why it takes a looooong time to run for large datasets).
Do you have a specific data error that you are trying to troubleshoot or are you just looking to replicate it in a dashboard or query? There was a similar post here: Inventory/WIP Reconciliation - #4 by acicala - ERP 10 - Epicor User Help Forum
Also, we love your grills at our house!
1 Like
They do⌠itâs called the âSoftware Development Toolkitâ and some form of âto giveâ is probably not the appropriate verb to use.
The golden SDK.