BOM multi-level reports for GP 10

(0) Share

Report

Posted on by Beat Bucher GP Gee...

28,054 Super User 2024 Season 1

Hi everybody,

I was looking for someone that had already tangled this challenge :-) in Dynamics GP... We're using the manufacturing module in GP and the users are in need for a better report than the default multi-level BOM Text report. I've searched accrossed the net several forums and places, and aside from one blog where I could get the code for a multi-level Inventory BOM (thanks to Mariano Gomez), I couldn't not locate much infos. I tried several options in SQL, but the trick is to deal with an undefined amount of levels (could go from 1-n, in our case up to 6 levels deep).

Another reason why the default BOM Text report of GP is useles, is because of the way it reports out the item list. They are sorted in alphabetic order and not respecting the sequence order they were entered in GP. Some of our BOM could be as large as 33 pages and take quite a long time to print out (at least 2-3 minutes before the first page shows up and another 2-3 minutes until the last page is processed).

So anybody is welcome that could provide me with at least a usable SQL scripts that manages properly the multi-levels. I can workout the integration in either Excel, SSRS or Crystal myself. Thanks in advance for your suggestions.
Béat

*This post is locked for comments

All responses (22)

Answers (1)

Beat Bucher GP Gee... 28,054 Super User 2024 Season 1 on at

Like (0)

Report

RE: BOM multi-level reports for GP 10

Hi Babu,

Try the WayBack machine with Sanjay's link:

web.archive.org/.../

That worked for me.

You should then be able to grab one view after the other and get all together.
babubaskaran@outloo... on at

Like (0)

Report

RE: BOM multi-level reports for GP 10

Hi Sanjay,

This link doesn't work. Can you please share correct Link. Beat has provided the SP which I am trying, thank you very much for providing the same Beat.

Thanks,

babu
Community Member Microsoft Employee on at

Like (0)

Report

RE: BOM multi-level reports for GP 10

When I first attacked this problem the simplest starting point was to copy the SSRS BOM Detail Report under SSRS Manufacturing Reports, delete the report layout, and insert the data fields into basic table to allow rendering to Excel.

This provided us the ability to export the basic BOM data, either single or multi level, to XL.

From here, based upon the underlining SP see_MFG_BOM_DETAIL, we customized SP's to provide export reports with much more additional data of the BOM line items.

When I am in our system later I will either post additional info or email.
Beat Bucher GP Gee... 28,054 Super User 2024 Season 1 on at

Like (0)

Report

RE: BOM multi-level reports for GP 10

Hi Kevin,

Yup, the possibilities are almost endless in SQL once you got the core code to do the hierarchy level indentation. I prefer the Temp table method as I don't have to deal with additional SQL Security..

But good point to add to the thread.
Community Member Microsoft Employee on at

Like (0)

Report

RE: BOM multi-level reports for GP 10

You can modify the see_MFG_BASIC_BOM_COSTS proc and add code to move the data from a temp table to a real table inside the company database and point excel to that table. Below is what I added to the proc and saved as a new name. I grab data from other tables in the process. I use ado 2.8 to return the data to excel.

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'BOM_BASIC_COSTS') drop table TWO.dbo.BOM_BASIC_COSTS

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'BOM_BASIC_COSTS_DETAIL') drop table TWO.dbo.BOM_BASIC_COSTS_DETAIL

select * into TWO.dbo.BOM_BASIC_COSTS from #BOM_BASIC_COSTS

SELECT dbo.IV00101.ITEMDESC, dbo.BOM_BASIC_COSTS.BOMTYPE_I, dbo.BOM_BASIC_COSTS.EFFECTIVEDATE_I, dbo.BOM_BASIC_COSTS.REVISIONLEVEL_I, dbo.BOM_BASIC_COSTS.FNSHGOOD,

dbo.BOM_BASIC_COSTS.COMPONENT, dbo.BOM_BASIC_COSTS.CMPTDESC, dbo.BOM_BASIC_COSTS.COMPONENT_CHAIN, dbo.BOM_BASIC_COSTS.COMPONENT_OF,

dbo.BOM_BASIC_COSTS.BOMLEVEL, dbo.BOM_BASIC_COSTS.SUBCAT_I, dbo.BOM_BASIC_COSTS.POSITION_NUMBER, dbo.BOM_BASIC_COSTS.MY_POSITION_NUMBER,

dbo.BOM_BASIC_COSTS.QUANTITY_I, dbo.BOM_BASIC_COSTS.FIXEDQTY, dbo.BOM_BASIC_COSTS.UOFMQTY, dbo.BOM_BASIC_COSTS.UOFMFXDQTY, dbo.BOM_BASIC_COSTS.COMPLETE,

dbo.BOM_BASIC_COSTS.DECPLQTY, dbo.BOM_BASIC_COSTS.WCID_I, dbo.BOM_BASIC_COSTS.LOCNCODE, dbo.BOM_BASIC_COSTS.QTYBSUOM, dbo.BOM_BASIC_COSTS.STNDCOST,

dbo.BOM_BASIC_COSTS.UOMSCHDL, dbo.BOM_BASIC_COSTS.BASEUOFM, dbo.BOM_BASIC_COSTS.FIXEDQTYBSUOM, dbo.BOM_BASIC_COSTS.DECPLCUR,

dbo.BOM_BASIC_COSTS.LABCOSTI_1, dbo.BOM_BASIC_COSTS.LABFIXOHDCOSTI_1, dbo.BOM_BASIC_COSTS.LABVAROHDCOSTI_1, dbo.BOM_BASIC_COSTS.MATCOSTI_1,

dbo.BOM_BASIC_COSTS.MATFIXOHDCOSTI_1, dbo.BOM_BASIC_COSTS.MATVAROHDCOSTI_1, dbo.BOM_BASIC_COSTS.MACHCOSTI_1, dbo.BOM_BASIC_COSTS.MACHFIXOHDCOSTI_1,

dbo.BOM_BASIC_COSTS.MACHVAROHDCOSTI_1, dbo.BOM_BASIC_COSTS.LABCOSTI_3, dbo.BOM_BASIC_COSTS.LABFIXOHDCOSTI_3, dbo.BOM_BASIC_COSTS.LABVAROHDCOSTI_3,

dbo.BOM_BASIC_COSTS.MATCOSTI_3, dbo.BOM_BASIC_COSTS.MATFIXOHDCOSTI_3, dbo.BOM_BASIC_COSTS.MATVAROHDCOSTI_3, dbo.BOM_BASIC_COSTS.MACHCOSTI_3,

dbo.BOM_BASIC_COSTS.MACHFIXOHDCOSTI_3, dbo.BOM_BASIC_COSTS.MACHVAROHDCOSTI_3, dbo.BOM_BASIC_COSTS.LABCOSTI_5, dbo.BOM_BASIC_COSTS.LABFIXOHDCOSTI_5,

dbo.BOM_BASIC_COSTS.LABVAROHDCOSTI_5, dbo.BOM_BASIC_COSTS.MATCOSTI_5, dbo.BOM_BASIC_COSTS.MATFIXOHDCOSTI_5, dbo.BOM_BASIC_COSTS.MATVAROHDCOSTI_5,

dbo.BOM_BASIC_COSTS.MACHCOSTI_5, dbo.BOM_BASIC_COSTS.MACHFIXOHDCOSTI_5, dbo.BOM_BASIC_COSTS.MACHVAROHDCOSTI_5, dbo.BOM_BASIC_COSTS.LABCOSTI_7,

dbo.BOM_BASIC_COSTS.LABFIXOHDCOSTI_7, dbo.BOM_BASIC_COSTS.LABVAROHDCOSTI_7, dbo.BOM_BASIC_COSTS.MATCOSTI_7, dbo.BOM_BASIC_COSTS.MATFIXOHDCOSTI_7,

dbo.BOM_BASIC_COSTS.MATVAROHDCOSTI_7, dbo.BOM_BASIC_COSTS.MACHCOSTI_7, dbo.BOM_BASIC_COSTS.MACHFIXOHDCOSTI_7, dbo.BOM_BASIC_COSTS.MACHVAROHDCOSTI_7,

dbo.BOM_BASIC_COSTS.BOMCAT_I, dbo.ICIV0323.MATCOSTI_1 AS PPN_MATCOSTI_1, dbo.ICIV0323.MATCOSTI_3 AS PPN_MATCOSTI_3, dbo.ICIV0323.MATCOSTI_5 AS PPN_MATCOSTI_5,

dbo.ICIV0323.MATCOSTI_7 AS PPN_MATCOSTI_7, dbo.ICIV0323.LABCOSTI_1 AS PPN_LABCOSTI_1, dbo.ICIV0323.LABCOSTI_3 AS PPN_LABCOSTI_3,

dbo.ICIV0323.LABCOSTI_5 AS PPN_LABCOSTI_5, dbo.ICIV0323.LABCOSTI_7 AS PPN_LABCOSTI_7, dbo.ICIV0323.LABVAROHDCOSTI_1 AS PPN_LABVAROHCOSTI_1,

dbo.ICIV0323.LABVAROHDCOSTI_3 AS PPN_LABVAROHCOSTI_3, dbo.ICIV0323.LABVAROHDCOSTI_5 AS PPN_LABVAROHCOSTI_5, dbo.ICIV0323.LABVAROHDCOSTI_7 AS PPN_LABVAROHCOSTI_7,

dbo.ICIV0323.MACHCOSTI_1 AS PPN_MACHCOSTI_1, dbo.ICIV0323.MACHCOSTI_3 AS PPN_MACHCOSTI_3, dbo.ICIV0323.MACHCOSTI_5 AS PPN_MACHCOSTI_5,

dbo.ICIV0323.MACHCOSTI_7 AS PPN_MACHCOSTI_7, dbo.ICIV0323.TOTALCOSTI_1 AS PPN_TOTALCOSTI_1, dbo.ICIV0323.TOTALCOSTI_3 AS PPN_TOTALCOSTI_3,

dbo.ICIV0323.TOTALCOSTI_5 AS PPN_TOTALCOSTI_5, dbo.ICIV0323.TOTALCOSTI_7 AS PPN_TOTALCOSTI_7, dbo.IV00101.STNDCOST AS PPN_STNDCOST,

dbo.IV00101.CURRCOST AS PPN_CURRCOST, dbo.IVR10015.REVISIONLEVEL_I AS PPN_REVISIONLEVEL_I, dbo.IVR10015.EFFECTIVEDATE_I AS PPN_EFFECTIVEDATE_I

into TWO.dbo.BOM_BASIC_COSTS_DETAIL

FROM dbo.IVR10015 INNER JOIN

dbo.IV00101 INNER JOIN

dbo.BOM_BASIC_COSTS ON dbo.IV00101.ITEMNMBR = dbo.BOM_BASIC_COSTS.FNSHGOOD ON dbo.IVR10015.ITEMNMBR = dbo.BOM_BASIC_COSTS.FNSHGOOD INNER JOIN

dbo.ICIV0323 ON dbo.BOM_BASIC_COSTS.FNSHGOOD = dbo.ICIV0323.ITEMNMBR

grant select on TWO.dbo.BOM_BASIC_COSTS to [TRIBRIDGE\KevinLehoullier]

GO
Suggested answer

Beat Bucher GP Gee... 28,054 Super User 2024 Season 1 on at

Like (0)

Report

RE: BOM multi-level reports for GP 10

My apologize for this previous post...

I didn't recall the origin of the SQL script as this was almost 5 years ago, but had a PM discussion with the owner.

So the original credits goes to Tim Foster from Trudell Medical Ltd in London, ON. Tim based his work on the original post from the MSDN SQL library about expanding hierarchies.

Tim originally developed the script to make it work for SSRS and as we're more 'Excel' reporting type of users in our company, I just adapted it to my needs. I'm going to post an article on my blog about this soon to provide more details on how to make this work nicely with Excel and maybe SSRS too.

Sorry again Tim, and to all, enjoy the Summer.
Suggested answer

Beat Bucher GP Gee... 28,054 Super User 2024 Season 1 on at

Like (0)

Report

RE: BOM multi-level reports for GP 10

Hi Greg,

I used a slightly different approach then the one suggested by Tim in Mariano's blog post, which is basically a recursive CTE view... I long used a view that was provided by some MFG consultant I met several years ago, but due to copyright licenses, I couldn't spread this out in the wild, so I had to find another approach. One reason was also that I wanted the BOM view become a SQL stored procedure, so I could pass parameters along for the ITEM Number and the BOM Type (MFG, ENG, ARC, etc)... and get the data back in an Excel spreadsheet.

This way it becomes much more flexible and allows to look-up all kinds of other tables from GP that you want to see in the report (i.e. Internet linked infos from SY01200, Price costs, Std Costs, Vendors , etc.. you name it). The look-up in Excel can be very powerful, as you don't want to load the SP / View in SQL more than what's required.

The SQL script below creates an SP in the GP Company where you want to use it. Make sure you set proper permissions in SQL to allow users to run the SP.

/****** Object: StoredProcedure [dbo].[sp_BOM_Level_View] Script Date: 17/07/2015 09:51:23 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_BOM_Level_View] (@current char(31), @BOMType smallint) AS

/*

DROP TABLE #result;

Drop Table #stack;

*/

--DECLARE @current char(31) --> use the 2 lines here to test direct in SQL Studio Mgmt

--SET @current='TEST-BOM'

SET NOCOUNT ON

if @BOMType is null or @BOMTYPE not in (1,2,3,4) set @BOMTYPE = 1

DECLARE @lvl int, @line char(31)

CREATE TABLE #stack (item char(31), lvl int)

CREATE TABLE #result (lvl int, item char(31), ord int identity(1,1))

INSERT INTO #stack VALUES (@current, 1)

SELECT @lvl = 1

WHILE @lvl > 0

BEGIN

IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)

BEGIN

SELECT @current = item

FROM #stack

WHERE lvl = @lvl

-- SELECT @line = replicate('-',(@lvl - 1)) + ' ' + @current --> spacing by level

-- PRINT @line --> replace this print with an INSERT to another table like #BOM

INSERT #result SELECT @lvl,@current

DELETE FROM #stack

WHERE lvl = @lvl

AND item = @current

INSERT #stack

SELECT CPN_I, @lvl + 1

FROM BM010115 --> I edited this for GP

WHERE PPN_I = @current and BOMCAT_I = @BOMType --1 --> added BOMCAT for MFG(1) or ENG(2)

ORDER BY CPN_I DESC --Order by Part Number as in GP Reports

--ORDER BY POSITION_NUMBER ASC --Order by Position Number–other sequence in GP

IF @@ROWCOUNT > 0

SELECT @lvl = @lvl + 1

END

ELSE

SELECT @lvl = @lvl - 1

END -- WHILE

drop table #stack

SELECT T1.*

,T2.ITEMDESC

,T2.STNDCOST

,T2.CURRCOST

FROM

#result T1 INNER JOIN IV00101 T2

ON T2.ITEMNMBR = T1.item

ORDER BY ord;

DROP TABLE #result;

/*

Use the SQL statement below to return data with the SP.

Exec [sp_BOM_Level_View] 'TEST-BOM'

Go

*/

GO

In Excel you can call the SP by using MS-Query like a regular Data call (use SQL as type) by using 2 cells to enter the variable parameters (in this case ITEM & TYPE):

Exec [sp_BOM_Level_View] ?,?

I then use the conditional formatting to colour the LVL column with the number to get a sense of the deepness of the BOM.

Let me know if you need more details.

PS: the original BOM select script was found somewhere on the net, just can't remember where. I did some small modifications to adapt it to my needs, but mostly it remains original. If I can find the creator, I'll update my post.
Suggested answer

Tim Foster 8,515 on at

Like (0)

Report

RE: BOM multi-level reports for GP 10

Here:

dynamicsgpblogster.blogspot.ca/.../using-t-sql-and-recursive-cte-to.html

Tim
Community Member Microsoft Employee on at

Like (0)

Report

RE: BOM multi-level reports for GP 10

Hi Beat,

We are having similar issues in getting a good BOM export with costs out of GP. The canned one is useless. You mentioned a Stored Procedure you wrote. Can you share any details on that?

gregw@malibuboats.com

Thanks

Greg
Community Member Microsoft Employee on at

Like (0)

Report

RE: BOM multi-level reports for GP 10

Beat,

I came across your posting on multi-level BOM stored procedure for GP. Is it possible to send me a copy of the script? Thanks.

Stephen Nu

snu@rubbercraft.com