Hello, this is the companion code for Systima Technology’s Epicor Insights 2023 Presentation!
This code provides a WBS report of Epicor projects, phases, jobs, materials, and operations as an indented grid of cost elements. The report is unique in that it correctly reports costs in many layers of jobs that fall inside a project, which is essential to the business and not obtainable via Epicor’s Project Analysis calculation so far. The data comes from various Epicor job and project related tables. No posted journals of any kind are required.
To try it yourself, the code may be ran on an Epicor demo database. It’s been tested on the demo database for Kinetic 2022.1.9.
Adjustments may be needed to make it operational for your ERP. This is a simplified version and is for reference only, use at your own risk.
This code is available to you under the MIT license.
I attended your customer lead session at last weeks Insights conference related to using SQL to generate a cost roll-up. I spoke with you briefly after the session regarding the ability to use the same approach, but instead of project based, I would apply it to Make To Job and Multi-Operation connections.
As I am not a SQL guy by trade, I just had some general questions to allow me to better point my IT in the right direction.
• What application are you using to wright the SQL code/scripts and is this the same application you are connecting/calling your Epicor database?
• How are you running the script and then returning the results to an Excel application. Are you able to execute this directly out of an Excel application via a macro, or something similar.
Feel free to respond in a more technical manner, as I’m sure my IT/SQL guys will better understand it then me. Sure there is more ways then one to apply this approach, just wanted to get a base line.
Thanks again for sharing this during the conference!
Hey Lucas, Thanks for coming to my session!
SQL Server Management Studio (SSMS) is a great tool to use for all things SQL. And it’s free! Microsoft makes enough $$$ off licensing the actual SQL Server, so they don’t charge for this tool. Many developers use it for all kinds of things like queries, database maintenance, and the like. There are even tools out there that let you search your database for any occurrence of a keyword. Which is particularly useful when trying to understand Epicor sometimes. One thing to note, is that SSMS may not be useful to you, if you have Epicor in the cloud. I know they do not expose the database to their cloud customers.
Once you have a query you’d like in a report, you can actually embed it in Excel as a datasource. There’s many great tutorials on this if you search for “Excel connect to sql”.
One popular way is to use the Data > Get Data > From Database > From SQL Server Database wizard in Excel. This is very similar to how you might load a query in PowerBI, which is Microsoft’s BI platform.
