Crystal Report 2012

[former_member191075]

I am using Crystal Report 2012 that comes with SAP Business One 9.0 I successfully created a CR report to show the 'father' item with all the 'child' BOM components items. However, some of the BOM components further contain sub levels of componenet items.

Message was edited by: Paul Finneran

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query up to six level of components:

SELECT

T0.[Father] as
'Assembly',T0.[code] as 'Component1', t10.[ItemName]
'Description1',T1.[Code] as 'Component2', t11.[ItemName]
'Description2', T2.[Code] as 'Component3', t12.[ItemName]
'Description3', T3.[Code] as 'Component4', t13.[ItemName]
'Description4',T4.[Code] as 'Component5', t14.[ItemName]
'Description5', T5.[Code] as 'Component6', t15.[ItemName]
'Description6'

FROM

ITT1 T0 LEFT OUTER
JOIN ITT1 T1 on T0.Code = T1.Father LEFT OUTER JOIN ITT1 T2 on
T1.Code = T2.Father LEFT OUTER JOIN ITT1 T3 on T2.Code = T3.Father
LEFT OUTER JOIN ITT1 T4 on T3.Code = T4.Father LEFT OUTER JOIN ITT1
T5 on T4.Code = T5.Father LEFT OUTER JOIN ITT1 T6 on T5.Code =
T6.Father left outer join oitm t20 on t0.father = t20.itemcode left
outer join oitm t10 on t0.code = t10.itemcode left outer join oitm
t11 on t1.code = t11.itemcode left outer join oitm t12 on t2.code =
t12.itemcode left outer join oitm t13 on t3.code = t13.itemcode left
outer join oitm t14 on t4.code = t14.itemcode left outer join oitm
t15 on t5.code = t15.itemcode

Thanks & Regards,

Nagarajan

[former_member191075]

Inst-ed of query , view work properly for me. Thanks to all of you.

[kothandaraman_nagarajan]

Hi,

If you got answer, please close this thread with correct/helpful answer.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Amol,

Please check below link.

Hope this help

--

--

Regards::::

Atul Chakraborty

[fbio_bilicki]

Hi,

I created a query using recursion, just have a problem, I can not multiply the quantities in levels.

WITH CTEestrutura (Levelid, Father, Code, Quantity, ItemName)
AS
( 

-- Anchor
SELECT 0 AS Level, T0.Father,T0.Code, T0.Quantity, T2.ItemName
FROM ITT1 T0 
INNER JOIN OITM T2 ON T2.Itemcode = T0.Code
WHERE T0.Father = '<<Your Code Item>>'
UNION ALL

-- Recursive Member Definition
SELECT Levelid + 1, T0.Father,T0.Code, T0.Quantity, T2.ItemName
FROM ITT1 T0
INNER JOIN CTEestrutura as T1 on T1.Code = T0.Father
INNER JOIN OITM T2 ON T2.Itemcode = T0.Code

)

-- Statement that executes the CTE
SELECT * FROM CTEestrutura
ORDER BY Levelid, Father

Rgs

Fabio

SAP 8.82 PL14 / SAP 9.0 PL11 (test).