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
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
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
