DEVELOPERS FORUM - Multilevel BOM with structure using SQL Server Reporting Services

Juriaan - Tuesday, September 15, 2015 3:20 AM:

Hi,

We have just started implementing aras innovator and I would like to use SQL Server Reporting Services as the prefered reporting solution. Can anyone help me with a SQL based alternative for the "Multilevel BOM Report", included in Innovator? I have been experimenting with the multilevelbom1 and multilevelbom2 sql stored procedure and function, but the resulting report is sorted by level. I would like a report where the structure of the BOM is preserved, like the original Multilevel BOM Report. 

Thanks in advance for your help!

DavidSpackman - Wednesday, September 16, 2015 9:55 PM:

I've found CTE as the good method to get a recursive structure.

declare 
	@sItemNumber varchar(50),
	@sId varchar(32)

set @sItemNumber = 'Your Part Number'

SELECT 
	@sId = [PART].id
FROM
	[innovator].[PART]
WHERE
	[PART].ITEM_NUMBER = @sItemNumber
	AND IS_CURRENT = '1';
WITH RecursiveBOM(SOURCE_ID, RELATED_ID, QUANTITY, indent_level) AS
(
		SELECT -- anchor member 
			pb.SOURCE_ID,
			pb.RELATED_ID,
			pb.QUANTITY, 
			0
		FROM 
			innovator.PART_BOM pb
		WHERE 
			SOURCE_ID = @sId
    UNION ALL
		SELECT -- recursive member
			pb1.SOURCE_ID,
			pb1.RELATED_ID,
			pb1.QUANTITY, 
			indent_level +1
		FROM 
			innovator.PART_BOM pb1
			--INNER JOIN with the CTE  
			INNER JOIN RecursiveBOM rb ON rb.related_id = pb1.SOURCE_ID
)
SELECT 
	indent_level AS [Level],
	sp.ITEM_NUMBER PARENT,
	sp.NAME PARENT_NAME,
	source_id as [PARENT ID],
	rp.ITEM_NUMBER CHILD,
	rp.NAME CHILD_NAME,
	related_id as [CHILD ID],
	rb.QUANTITY
FROM 
	RecursiveBOM rb
    INNER JOIN innovator.PART sp ON rb.source_id = sp.ID
    INNER JOIN  innovator.PART rp ON rb.related_id = rp.ID

Juriaan - Thursday, September 17, 2015 4:27 AM:

David,

Thanks for this! It does provide a much better result than I have come up with sofar. The only thing is that the result is sorted by level (additional columns omitted for clarity)

And I would like it to be sorted by BOM structure tree, like this:

Any Ideas how to sort the result properly? I am sure there should be something possible using the childs and parents in the current result to create a sort order.

Many thanks in advance for your help!

Regards,

Juriaan

DavidSpackman - Thursday, September 17, 2015 9:21 AM:

Juriaan - Wednesday, September 30, 2015 9:15 AM:

David,

Thanks for your help in pointing me in the right direction! I finally managed to create the report I wanted, using the information found here:

http://social.technet.microsoft.com/wiki/contents/articles/21062.t-sql-hierarchical-table-sorting-with-a-parent-child-relation.aspx

I managed to come up with the working SP below:

USE [InnovatorSolutions]
GO
/****** Object:  StoredProcedure [innovator].[MultiBom_GetCompleteBomNAC]    Script Date: 9/30/2015 3:08:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [innovator].[MultiBom_GetCompleteBomNAC] (@partId CHAR(32))
AS
BEGIN

-- We need a table variable to add the the selected part or assembly to the BOM report later
DECLARE @tempbom TABLE (
ID VARCHAR(32),
SOURCE_ID VARCHAR(32),
QUANTITY INT,
SORT_ORDER VARCHAR(32),
RELATED_ID VARCHAR(32),
PATH VARCHAR(MAX),
LEVEL INT,
XR FLOAT(53));

-- Use CTE to create the multilevel BOM
-- The [path] column explains the level the (sub)part is in the hierarchy (can be left out)
-- The level column counts the level in the BOM
-- The xr column is a numeric field for the BOM sorting
  WITH BomTree (id, source_id, quantity, sort_order, related_id, [path], level, xr)
AS
(
SELECT
    ID,
 SOURCE_ID,
 QUANTITY,
 SORT_ORDER,
 RELATED_ID,
    CAST(ROW_NUMBER()over(partition by source_id order by sort_order) as VARCHAR(MAX)) as [path],
    1 as level,
    ROW_NUMBER()over(partition by source_id order by sort_order) / POWER(10.0,0) as xr
FROM PART_BOM
WHERE source_id = @partId
UNION ALL
SELECT
 t.ID,
    t.SOURCE_ID,
 t.QUANTITY,
 t.SORT_ORDER,
 t.RELATED_ID,
    [path] +'-'+ CAST(ROW_NUMBER()over(partition by t.source_id order by t.sort_order) as VARCHAR(MAX)),
    level+1,
    xr + ROW_NUMBER()over(partition by t.source_id order by t.sort_order) / POWER(10.0,level+1)
 FROM
    BomTree
 join PART_BOM t on BomTree.related_id = t.source_id
)

/*
We need to add the selected part or assembly as top level of the BOM.
Since we cannot reference a CTE table more than once, a
table variable is used here
*/
INSERT INTO @tempbom
SELECT *
FROM BomTree

/*
Adding the selected part at the top of the BOM tree.
Source ID is set to zero, because the part or assembly has no parent
*/
INSERT INTO @tempbom
SELECT ID, '0' as SOURCE_ID, QUANTITY, SORT_ORDER, RELATED_ID, '0' as path, 0 as level, 0 as xr
FROM PART_BOM
WHERE RELATED_ID = @partId;

/*
Create the BOM report output, while querying the
required details from the PART and PART_BOM tables

You can add additional columns from the PART and PART_BOM tables here, as needed
*/
 SELECT sp.id source_id, sp.item_number source_number, sp.name source_name,
        pb.quantity quantity, pb.sort_order sort_order, pb.id bom_id, pb.remark related_remark,
        rp.id related_id, rp.name related_name, rp.item_number related_item_number, rp.classification related_classification,
  cb.path path, cb.level level, cb.xr xr
  FROM @tempbom cb LEFT JOIN PART sp ON cb.source_id=sp.id
         LEFT JOIN PART_BOM pb ON cb.id=pb.id
         LEFT JOIN PART rp ON cb.related_id=rp.id
  ORDER by xr --sort the BOM in the correct order

END