Nested/Structured/Indented Bill of Material SSRS

Epicor ERP 10
1

I have created a bill of material report showing the bom level, the top level part, the child part and the child parts, children. It gives me the right data and end users are using it but they have to a wee bit of cross referring

One user has asked if they can get this as an indented bom. They want to see each assembly, it’s sub assemblies and it’s sub assemblies. I was intending to modify the existing SSRS report but given we can have up to 8 levels on the bill of material I am struggling to come up with any sensible way to do this with grouping/sub reports etc.

The first example below is how the data currently comes out of the report

BOMLEVEL TopLevelPartNum PartNum	RevNum	QtyPer	Apprvd	FQty	ChildPartNum	PartQty
1	379-3778	379-3778	0A	1	1	0	194-4302	1
1	379-3778	379-3778	0A	1	1	0	310-6810	1
1	379-3778	379-3778	0A	2	1	0	363-6269	2
1	379-3778	379-3778	0A	1	1	0	364-5733	1
1	379-3778	379-3778	0A	1	1	0	382-1712	1
2	379-3778	382-1712	0A	0.04761	1	0	4330X15X3	0.04761
2	379-3778	363-6269	0	1	1	0	363-6268	2
2	379-3778	363-6269	0	2	1	0	8T-3490	4
3	379-3778	363-6268	0	0.00606	1	0	4325X12X3	0.01212
2	379-3778	310-6810	0	0.00699	1	0	5825X12X3	0.00699
2	379-3778	194-4302	0	0.00543	1	0	4325X12X3	0.00543

What I actually want to structure this like is

q
q.jpg830×310 97.2 KB

This is all based on querying the part material table. What I essentially want to replicate without creating a job, is a structure similar to what the system does when populating the job assembly table. Does anyone please have any ideas on how to do this without having an infinitely cascading series of sub reports.

The underlying query used the generate this data is below

with pm as (select x.Company, x.PartNum,x.MtlSeq, x.RevisionNum, QtyPer, pr.Approved,
                   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 BOMLEVEL, TopLevelPartNum,PartNum, RevisionNum, QtyPer, Approved, FixedQty, ChildPartNum, PartQty
  from bm
  where TopLevelPartNum='379-3778'
2

This might be an obvious questions but have you used the BOM Listing Report with the Indented Level format option checked? Or the BOM Cost Report, again with the indented Level format option checked?

I copied the report style for the BOM Cost Report and then modified the Data Only Report so they could export it directly to excel to their liking.

3

There is also a good example of how to build an Indented BOM BAQ in the ICE Tools User Guide on EpicWeb. The screen shot is from the 10.2 user guide but I believe it works in 10.1 versions also. Might help with your report. It walks through all the steps

image
image.png1101×752 71.5 KB

4

Thanks for the reply.

We are on 10.0.7.4 - the bom listing report doesn’t work in our version - might be data/might be a bug but we have no maintenance and did not have it before we went live so haven’t got this fixed - we had a long drawn out implementation and business took the decision to not renew maintenance until we were live and then didn’t like the maintenance back dating when we were live so can’t do anything about it.

It would be great if you can post a screen shot of this report, so as I can see if it would work for us and add to the list of reasons why we need to get back onto maintenance.

5

Managed to get the bill of materials report to work and this gives the end users what they want. Definite bug in our version.

When you choose the part revision, irrespective of what the actual part rev effective data is, on the filter screen it will default the effective date to today and error. I manually change the effective date to match the part rev effective date and it works. I’m doing all this work on Friday 16th March, if on the filter screen I set the effective date to the 15th March it falls over, 14th March falls over, 13th March falls over, 12th March works without issue. Not sure if it is the number of days or the fact that 12th March is a Monday.

Really reinforces my opinion that 10.0.07.4 clearly wasn’t extensively quality tested - loads of perplexing stuff like this - unfortunately unless we find a magical money tree we are stuck on this version.

6

I hear you. We are on 10.1.600 and it still has it’s issues too. I just know if you we don’t keep up with the new versions we could be bleeding more money (and loosing more hair) in workarounds and trouble shooting than what it would take to keep up on the versions. Tech support gets a little harder to work with over time.(They like to try to replicate it on new versions first then come back with “working as designed”) Our team is working on a better upgraded plan to keep current. I think the bug fixes out weigh the chance of new bugs introduced in the upgrade.

Good luck!