Query to replicate the Bill of Materials Report

[Former Member]

I'm looking to recreate the Bill of Materials report as a query, mostly because i cannot add any new database fileds to the pld report.

As i cannot amend/change the screen layout or PLD i would like to recreate the report as a query but have struggled to get a query to show the expanded layers of the BoM as the report does.

[Former Member]

Gordon, thankyou for the query.

The problem i have is that i want the query to show the exploded BoM not just the parent.

On the BoM report it details the parent and child as levels within a top level BoM so you get to see the full component list.

[Former Member]

Just add in the T0.Code into the select statement should give you that?

[Former Member]

Gordon,

each query i tried to create failed so i never saved them, i struggled to one get the BOM levels replicated and secondly couldn't find the link to bring in prices from various pricelists.

[Former Member]

Following is one I wrote to find cost vs sell price & GP for Production BOM's.

You may be able to modify it to include what you need?

{SELECT T0.[Father],

sum ( T2.[AvgPrice]* T0.[Quantity]) as [Cost],T3.[Price] AS [Sales Price],

(t3.Price - sum(T2.[AvgPrice] * T0.Quantity)) as [GP] ,

(t3.Price - sum(T2.[AvgPrice] * T0.Quantity))/sum (case when isnull(T2.[AvgPrice],0)= 0 then 1 else isnull(T2.[AvgPrice],0) end * T0.[Quantity])*100 AS [GP%]

FROM ITT1 T0 INNER JOIN OITT T1 ON T0.Father = T1.Code INNER JOIN OITM T2 ON T0.Code = T2.ItemCode INNER JOIN ITM1 T3 ON T1.Code = T3.ItemCode

WHERE (T1.[TreeType] = 'P' or T1.[TreeType] = 's') and T3.[PriceList] = 1

GROUP BY T0.[Father],T3.[Price]}

[Former Member]

Could you show you query created so far? This should be a simple query to create.

Thanks,

Gordon