How to Add StdTotalCost and LastTotalCost to the BOM Listing Report

Epicor ERP 10
1

Hello,

I have been tasked with adding 2 fields to the lines in the BOM Listing report (StdTotalCost and LastTotalCost) from the PartCost table.

I have added the table in RDD and set up a relationship to PartMtl on Partnum field.
In the report I added the table as a Full Outer Join ON PartNum.

I am adding the fields together on the report (EX: StdBurdenCost + StdLaborCost + StdMaterialCost + StdMtlBurCost + StdSubContCost)

I seem to be only getting the Main Part cost repeated on each line.

Any ideas on where I went wrong?

I am happy to post specifics if needed.

Thanks,

Shawn

2

Before you go through more work on making it work, have you tried getting this data in BAQ? Most of the time that helps you determine how to structure your links and any calculated data. You don’t have to re-create the whole report, but just the last branch, like PartMtl to PartCost link.

If that all works, then I would try to make it work in the report. Btw… in this case I always use Left Outer Join, as last cost does not always exist.

3

Your choices in RDD are “Definition” and “Output”.

I think …
Definition = “INNER JOIN”
Output = “LEFT OUTER”

And you might need to join the CostID if you have more than one.

4

And are you trying to get the cost for the component of the BOM?

Then you should join with PartMtl.MtlPartNum to PartCost.Partnum

PartMtl.PartNum is the number of the parent item (the thing being made)

5

I tried that and it returned zeros instead of the parent cost.

6

If the BOM was like

PARENT-101
  - SUBASSY-101
    - COMP-123
    - COMP-124
    - COMP-567
  - SUBASSY-102
    - COMP-666
    - COMP-777
    - COMP-888
  - COMP-900
  - COMP-901
  - COMP-123

You just want the costs from the PartCost file for PARENT-101 ?

7

I am told I need the total cost for the parent part and the individual costs for each child part listed.

I created a SQL Query to pull the example info and it worked fine.

I would sum it up on the report itself.

SELECT T1.MtlPartNum, T2.StdBurdenCost, T2.StdLaborCost, T2.StdMaterialCost, T2.StdMtlBurCost, T2.StdSubContCost FROM Erp.PartMtl T1 LEFT JOIN Erp.PartCost T2 ON T1.Company = T2.Company AND T1.MtlPartNum = T2.PartNum WHERE T1.Company = 'CMI' AND T1.PartNum = '000500KB'
MtlPartNum StdBurdenCost StdLaborCost StdMaterialCost StdMtlBurCost StdSubContCost
000502C 6.62456 1.01916 136.02500 0.00000 0.00000
015035 0.00000 0.00000 0.02773 0.00000 0.00000
RM50721 0.00000 0.00000 4.39000 0.00000 0.00000
014175 0.00000 0.00000 2.75000 0.00000 0.50000
690072 0.00000 0.00000 0.37950 0.00000 0.00000
03050054 0.00000 0.00000 1.17500 0.00000 0.00000
03050054MAN 0.00000 0.00000 0.88640 0.00000 0.00000
8

The RDD is set for Output.
The Report, I changed to Left Outer Join.

9

One thing to consider, is that the BOM report is iterative/recursive. If the BOM is multi-level, then some iterations will see a component part as the parent part for its sub-components.

No idea if that is affecting you. Just thought I’d throw it out there.

With your new RDD and report RDL, you can add the Std_xxx_Cost fields to the report, but they always display zero?

10

The way that it is right now with the Left Outer Join of PartCost to PartMtl on PartMtl.MtlPartNum = PartCost.PartNum it is showing zeros.

I have the field in the same line as the MtlPartNum and it sums up all the Std fields.

If I have the join of PartMtl.PartNum = PartCost.PartNum, it at least shows the parent total.

I just built the baq that joins PartMtl to PartCost with a left join and it is pulling the same data as the SQL query I tried above.

11

Don’t forget that RDD’s aren’t just BAQ’s that you can’t see inside. They often do “tricks” in their processing. So the PartMtl table might not even be the actual PartMtl table.

12

OK, I finally kicked my brain after rereading everything and got each individual part to show their costs.
Now I am trying to make sure their sum equals what Epicor says.
My problem was that I was only changing the report dataset join and forgot about the RDD.

Repeatedly talking about the RDD finally triggered the thought to check if I changed it, lol.

All I can say is I am still out of it from being sick yesterday.

Thanks to everyone for your help.

If I have any other issues with totaling it, I will create a new thread.

1 Like