Dynamics Community Forum Thread Details

Bills of material with sub-bom Report

I have requirement to create a new report of Bills of material which will show boms and sub boms in same report. Following is the current report with current data

Bills_of_material

and client requirement is to get those blue color item also show in same report but now it will show when we click on LINE on menu

Bills_of_material

I have created a query to confirm its relation between tables which given below:-

select bt.BOMID,t.NAMEALIAS,b.ITEMID,b.UNITID,b.BOMQTY,b.BOMQTYSERIE,t.COSTGROUPID
from BOMTABLE as bt left join bom as b on bt.BOMID = b.BOMID
left join INVENTTABLE as t on b.ITEMID = t.ITEMID
where bt.BOMID IN(select b.ITEMID
from bom as b inner join BOMTABLE as bt on b.BOMID = bt.BOMID
inner join INVENTTABLE as i on b.ITEMID = i.ITEMID
where b.BOMID=' 051170500007' and i.COSTGROUPID = 'SFG')
union all
select bt.BOMID,i.NAMEALIAS,b.ITEMID,b.UNITID,b.BOMQTY,b.BOMQTYSERIE,i.COSTGROUPID
from bom as b inner join BOMTABLE as bt on b.BOMID = bt.BOMID
inner join INVENTTABLE as i on b.ITEMID = i.ITEMID
where b.BOMID=' 051170500007' and i.COSTGROUPID != 'SFG'

what will be the best way to create this report on dynamics ax 2012

Quick Links

Leaderboard