Hello Epicor community!
I am interested in finding out the best way to create a BAQ and consequently a Dashboard with all components of a MOM (materials, operations, and resources). Anybody out there that would like to share that with me? I’d appreciate it very much.
Thank You
Neco
Create a copy of the Part Method tracker and review how that is constructed.
Charlie
I had senior management needing a full costing of a part and its subassemblies and method tracker had a few bugs in it around which revision it was selecting for aubassemblies, and it also didn’t lend itself to a PDF print that the users could take to board meetings.
I know it’s a bit of a ‘cop out’, but after trying my best using the BAQ designer, in the end I opted for creating a view in a database, that I use for custom objects, and referencing that in an external BAQ.
I then created a BAQ report and everyone was happy and the pressure eased 
It’s not ideal, but occasionally, needs must.
FYI - I have appended the SQL I used to create the view below.
Cheers,
Andrew.
USE [XCustom]
GO
/****** Object: View [Custom].[XBOMTree] Script Date: 20/11/2018 08:26:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [Custom].[XBOMTree]
AS
WITH PartList AS
(SELECT pm.partnum AS toplevelpartnum, pm.revisionnum AS toplevelrevisionnum, pm.partnum, pm.revisionnum, pm.mtlpartnum, pm.qtyper, 1 As BomLevel, pt.typecode
FROM epicorerp.erp.partmtl as PM
INNER JOIN epicorerp.erp.part as PT
ON PM.mtlpartnum = PT.partnum
UNION ALL
SELECT pl.toplevelpartnum, pl.toplevelrevisionnum, nplus1.partnum, nplus1.revisionnum, nplus1.mtlpartnum, nplus1.qtyper, PL.BomLevel + 1, pt.typecode
FROM epicorerp.erp.partmtl as nplus1
INNER JOIN epicorerp.erp.part as PT
ON nplus1.mtlpartnum = pt.partnum
INNER JOIN epicorerp.erp.partrev PR
ON nplus1.partnum = pr.partnum
AND nplus1.revisionnum = pr.revisionnum
AND pr.approved = 1
INNER JOIN PartList AS PL
ON nplus1.partnum = PL.mtlpartnum
)
SELECT * From PartList
GO
I modified the SQL below from something posted on one of the old vantage forums - runs in one second on our db - only brings back active revisions at all levels. I tried Andrews sql and it ran for 2 minutes 53 seconds on our db. Results are not quite as elegant as his though.
with pm as (select x.Company, x.PartNum, x.RevisionNum, QtyPer,
FixedQty, MtlPartNum as ChildPartNum
from erp.PartMtl x
inner join erp.PartRev pr on pr.Company = x.Company
and pr.PartNum = x.PartNum
and pr.RevisionNum = x.RevisionNum
and pr.Approved = 1
and pr.EffectiveDate <= GetDate()
and pr.EffectiveDate = (select max(effectiveDate)
from erp.PartRev pr2
where pr2.Company = pr.Company
and pr2.PartNum = pr.PartNum
and pr2.Approved = 1
and pr2.EffectiveDate <= GetDate()) ),
bm as (select 1 as BOMLevel,
root.PartNum as TopLevelPartNum, root.,
cast(root.QtyPer as decimal(30,10)) as PartQty
from pm as root
where not exists (select null
from Erp.PartMtl b
where b.MtlPartNum = root.PartNum)
union all
select BOMLevel + 1 as BOMLevel,
bm.TopLevelPartNum, node.,
cast(node.QtyPer * bm.PartQty as decimal(30,10)) as PartQty
from pm as node inner join bm on bm.Company = node.Company
and bm.ChildPartNum = node.PartNum)
select *
from bm
We are on an older version of E10 - 10.0.7.4 and this kind of thing seems to be too complex for the baq designer in that version hence why we took the direction we did and have an SSRS report hanging off this. I know the BAQ designer has continued to evolve so those on more modern versions maybe able to tell you if this is possible - ideally you be avoiding accessing SQL directly.
Sorry, I forgot to say you need to specify a top level part, if you don’t then its the MOM for every part.
You need to run an equivalent of
select * from xCustom.custom.xbomtree where toplevelpartnum = ‘???’ and toplevelrevisionnum = ‘???’
I just tried the above for a kit with a 3 level BOM and 186 parts and it returned in < 1sec
Not getting into a p*ssing contest but I can return all parts on our db in less than 1 second - we have tens of thousands of parts many with more than 10 bom levels and including hundreds of sub parts. One of the recurring themes on this forum is to avoid accessing SQL directly if at all possible - has a fervour akin to zealotry - one of the rationales being that you remove all the checks and balances that the E10 baq designer has to prevent problems in your query syntax impacting on system performance when executed.
My point in my post is that given how much longer your query takes to execute - given it is effectively doing the same thing - could introduce a significant performance hit each time it is executed.
OK - I was just correcting my post.
Hi Charlie, Andrew, and James
Thank you so much for the valuable input and great info provided. I am going to go with Andrew’s view on my test environment and see how it goes.
I appreciate your help.
Good day everyone.!!
First, let me agree with @mcfreedombaby, knowing how to create a well-running performant query is a great skill. I would say that the world of pre-subquery BAQs are generally pretty awfully written and would not be written that way if one were to do so in SQL Server Management Studio. I would also agree that one will always get far better performance from a stored procedure executing directly in the database.
Where this zealot rationalizes staying within the framework as much as possible certainly has nothing to do with performance. It has more to do with future time savings in other areas like compliance and upgradability.
As a public company, we are required to comply with SOX procedures. One of the procedures is to show controls around who has access to the database directly, especially those with SA access. By not having access, I save hours during the audit and even more with the ongoing documentation around such access. I also save a lot of time with Epicor Support. Nobody can claim that I made “some change” that corrupted the database.
This discussion of working within a framework vs not is not an Epicor-only topic. Developers who use an Object Relational Mapper (ORM) like Entity Framework face these same decisions. They are generally slower vs native access. OTOH, they provide functionality that would be very difficult to do otherwise. One can swap out a data source for testing code very easily for example. In the case of Epicor, when I use a BAQ, I get the ability to safely update the database from the same query. I get REST access for free making integrations easier. And BAQs generally upgrade well, although some refactoring may make them perform better.
The whole point here is managing risk. If the Epicor Admin won the Lottery and left the company, would the company be able to replace that function easier if there are a lot of out of framework work to manage? Is it well-documented? Running a stored procedure for reporting purposes is relatively low risk. Each company has to choose its own comfort level. But in order to do so, they should know all of the ramifications of their decisions.
Mark W.