In this post we will discuss about the use of recursive querying in SQL Server.
Recently, I got a request from a Partner to develop a SSRS report for the Indented Bill of Materials in the Inventory module. I was going through the Dexterity Indented Bill of Materials report and found out that the report uses a temporary table. For filling the temporary table with the Inventory Bill of Materials hierarchy, Dynamics GP recursively loops around a logic written in Dexterity. We are stuck here, because we won’t be able to consume the logic that Dynamics GP uses to build the Inventory Bill of Materials hierarchy in SSRS. But, luckily we can create a recursive query in SQL Server to get the Inventory Bill of Materials hierarchy without spending much time.
Recursive Query to get the Inventory Bill of Materials hierarchy
DECLARE @billNumber CHAR (31) DECLARE @BMReporting TABLE ( ITEMNMBR CHAR (31), CMPTITNM CHAR (31), ITEMDESC CHAR (111) ) SELECT @billNumber = 'FAXX-FG3-0001'; -- By Recursive Query using CTE build the Indented BOM WITH BMReporting (ITEMNMBR, CMPTITNM, ITEMDESC) AS ( -- Anchor member definition SELECT CAST (NULL AS VARCHAR (64)) AS ITEMNMBR, CAST (LTRIM (RTRIM (BMHDR.ITEMNMBR)) AS VARCHAR (64)) AS CMPTITNM, LTRIM (RTRIM (ITMMSTR.ITEMDESC)) AS ITEMDESC FROM dbo.BM00101 BMHDR INNER JOIN IV00101 ITMMSTR ON BMHDR.ITEMNMBR = ITMMSTR.ITEMNMBR WHERE BMHDR.ITEMNMBR = @billNumber AND BMHDR.Bill_Status = 1 UNION ALL -- Recursive member definition SELECT CAST (RTRIM (LTRIM (BMDTL.ITEMNMBR)) AS VARCHAR (64)), CAST (RTRIM (LTRIM (BMDTL.CMPTITNM)) AS VARCHAR (64)), LTRIM (RTRIM (ITMMSTR.ITEMDESC)) FROM dbo.BM00111 AS BMDTL INNER JOIN dbo.IV00101 ITMMSTR ON ITMMSTR.ITEMNMBR = BMDTL.CMPTITNM INNER JOIN BMReporting AS BMReporting ON BMDTL.ITEMNMBR = BMReporting.CMPTITNM WHERE BMDTL.Bill_Status = 1 ) -- Statement that executes the CTE INSERT INTO @BMReporting SELECT ITEMNMBR, CMPTITNM, ITEMDESC FROM BMReporting SELECT * FROM @BMReporting --@billNumber :- The Bill Number for which need the hierarchy
Please refer the following link for more information on Recursive Querying in SQL Server.
Using the above recursive query as a base, built a Stored Procedure that accepts ‘Bill Number’ as an input parameter and returns the Inventory Bill of Materials hierarchy as a result set.
I have attached the SQL object and RDL file developed along with this post for your reference.
Hope this helps...
Until next post !!
// Copyright © Microsoft Corporation. All Rights Reserved.// This code released under the terms of the // Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
Yes, but not as nice as the one in my article Using T-SQL and recursive CTE to generate a BOM tree, dynamicsgpblogster.blogspot.com/.../using-t-sql-and-recursive-cte-to.html. This one displays the tree directly with the levels of indentation, making it even easier to pull the actual tree in SSRS.
Mariano Gomez, MVP
Posting from Mark Polino at DynamicAccounting.net
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.