Hi, I am new to Epicor, and have no knowledge of this system or BAQ’s. I am tasked with doing profitability analysis at a sales order level, where I can drill down into job lines as well. I need to be able to generate a report that gives me a list of all Sales Orders with the ability of selecting closed and open, Total Sales Order Amount, Job Lines (closed and open), and all revenue & cost incurred to date (actuals vs estimated), as well as any budget inputted in the system. I have found where I can do it one sales order at a time, but I do not have the manpower to do that. I need a report that will give me all above activity for all Sales Orders and Jobs on one report.
@cathyol this can be done with a BAQ and an SSRS Report or even a dashboard however there is a lot (an ocean) of knowledge involved between where you are now and where you need to get.
Is there no one at your company that knows BAQs, SSRS , Dashboards or any of those tools?
We could try to help you out as much as possible but it appears you are starting from zero.
I just saw you are in Epicor 9 which complicates things a bit. (most people are in some version of 10 now a days, nothing wrong with 9 but vastly different technology)
I would recommend you approach your company about some training before you head into this endeavor. Do you have access to epicweb? There are some wonderful manuals available online to get your started.
Do you know Crystal Reports? If this is a quickly needed report you may want to reach out to that consultant (or a new one) and get his help. If its something that can wait till you get some knowledge we’d be more than glad to point you in the right direction.
The gist of it is, you need a BAQ which contains all the data you are looking for Orders -> Jobs then youll need to group that out in a report hierarchically Order -> then jobs -> order then jobs etc
Fairly easy to do in Crystal once you have the data.
You mention “budget” that’s a different beast all together are we talking estimates (job level)? Quote Level? etc?
You’ll need more specific details, but this is definitely doable
I am not familiar with epicweb, but I have found a lot of great user manuals on the internet.
I am familiar with Crystal Reports. And will need to continue to educate myself on Epicor.
I do understand I need a BAQ, which I have capabilities of building a BAQ myself, I am just not familiar enough with building one to be able to create me the needed report.
If you can get up to speed on BAQ’s and Reporting, that would be best. But as Jose said, there is an ocean between you and your end goal.
If you understand CR pretty well you might be able to leverage that by doing some post processing in CR, instead of making complex BAQ’s. Basically just have CR ask for more data than you need and filter it in the report. This might make your reports run slow and inefficient, but will reduce some of the uncertainty of whether the BAQ is working as you expect it to.
Another option would be to setup an ODBC connection to the database, and just use Excel to pull from the DB, and distill your data. I used to do this as my primary method for getting data out of V8, before I learned about BAQ’s and the Reporting system.
I’d have a sheet for each table, and a few sheets that combined (usually using VLOOKUP() function). You can have cells on the worksheet that act as inputs for parameters of the query. They can help reduce the data returned to Excel.
My first question when I came to this company was regarding the ODBC connection, and the consultant told me I could not use with this version. I will have to revisit with this consultant or let my upper management he is more milking us than helping us.
Thank your for your response as well. All this information is very useful.
One more thing … Even if you knew E9’s BAQ and Reporting systems inside-and-out, the information required to derive “Profitability” is widely scattered throughout the db.
For example, a line item on an Order my be fulfilled by any of several ways:
From Inventory
From WIP
As Buy To Order (this might only be on E10)
So to determine your cost you need to look in several places and sum them up.
From Inventory, you want to pull the STK-CUS tran from the PartTran table
From WIP, you want to pull the MFG-CUS tran from the PartTran table. And even then this might not be correct if any transactions (like material corrections) happened after it shipped.
I’m not saying what you want is impossible, but just know that its a long road, and expect lots of potholes.
On the plus side, many folks here are very willing to help out. Especially when sharing their experience when it comes to “gotcha’s”
Oh, I have found that Epicor is not as user friendly with reports as other ERP’s I have worked in. I will make the most out of it, but thank you for the direction. This helps tremendously.
On another note, this has been a question circling the office. Is there a way in Epicor to allocate all cost to the Sales Order when the order is 100% complete, or does cost have to be allocated at the job line?
That doesn’t seem right. I’ve Used ODBC with Vista 8 (on a Progress OpenEdge DB), and with E10 (on a SQL DB). We skipped over E9, so I’ve no experience there.
Using MSQuery with ODBC connections is a great way to browse about the database. MSQuery has very basic functionality, but does let you create relationships between tables( somewhat limited), and filter on fields, and allows for input parameters.
Because Epicor is very flexible (allowing transactions out of order) job costing can be tricky, if you don’t do the processing the same way every time.
Take the following example:
Job is created (linked to Sales order)
Material is issued to job. (say $1000 worth of inventory was issued)
Job is shipped to customer (cost of $1000 is recorded)
It is discovered the wrong material was issued, and after making corrections the actual cost of material was $1200. Now there is an extra $200 in WIP that will sit there until the Job is closed, and then it will become a MFG Variance.
It is possible to tie that MFG Var back to the job and then to the sales order, but that’s not very straightforward. And if you ran your profitability report prior to that correct being made, it would be incorrect.
ODBC does work with Epicor 9 if you use MSSQL as your database. I think there is a version of E9 that uses a progress only db, I don’t know if a ODBC driver exists for that but probably does.
So, with MSSQL db it’s not so much as connecting to E9 but logging into your MSSQL server’s Epicor 9’s database, and potentially any other database on the SQL server. It uses the Microsoft SQL Server ODBC driver to connect. Your DBA would need to add a user that you can connect with, or if he could add your windows user to connect and set the needed permissions to the databases. I made a read only generic user for our database.
I have an Order Profits dashboard and report that may be helpful for a starting point when you’re ready. It pulls in information from orders, invoices, and direct jobs. Invoice costs hold standard costs for stock parts and job costs for direct jobs (gotta watch timing on these). It seems to tie in nicely to GL amounts. Estimated job costs are included in the dashboard, but not the report as they don’t have a bearing on finances. The report notes that costs may not be complete if jobs haven’t been closed or WIP hasn’t been captured. However, this dashboard and report is only for single orders.
BTW, we are on 9.05.701 with Progress. I’ve used DBVisualizer to set up ODBC connections with our Progress database. I don’t have many and am not even close to being an expert, but do know it can be done.