Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

BOM Summary Excel export

Report design issues and solutions.
Post Reply
gazmoz17
User
User
Posts: 91
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

BOM Summary Excel export

Post by gazmoz17 » 20 Jan 2022, 21:51

Hi Bruce,

Been trying to do something for hours and hours!...should of just watched the footy!

Current report works summarizing when makeup cost is not equal to the assembly sku's last cost price standard.

However, I wish to add the sub components into my report (Col H:R). I can return the component codes and component qty. However, codes are a bit meaningless I want to return the component descriptions? So I can check quickly line by line that all the BOMs have been mapped correctly. My colleague was going to check the mapping tomorrow so thought would be easier on an excel with one line per each assembly. I can make my report work where the components are pulling through component descriptions, but they fall below each assembly across multiple lines. So it slows down scanning/checking and manipulating the data filtering on my assembly cols in excel e.g. Supplier.

Col A:F is my assembly fields.

Could get around it manipulating my excel by adding helper columns next to my component codes then doing a lookup but its a bit messyeven if make an excel vba macro try and speed it up.

Or is this best done that OBDC method you shown me in the past?
BOM Summary.xlsx
Many Thanks
You do not have the required permissions to view the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4466
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: BOM Summary Excel export

Post by brucedenney » 21 Jan 2022, 11:37

To add the assembly descriptions, you need to link the STOCK_COMP.ASSEMBLY_CODE to a second copy of the Stock Table.

When you add the second copy of the table, you can give it an alias eg Assembly this is the description you need to use in the report to show the component description.

If you look at the Joins in the Product Explosion Report in Product Explosion and BOM group in the Stock Reports you will see how it is done.

Bruce

PS If you want the data to be in excel at the end, then I would do it in Excel using ODBC at the start. (It is the same method in MS Query, you add the stock table in a second time and link Stock_Code to the Assembly_Code)
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

gazmoz17
User
User
Posts: 91
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: BOM Summary Excel export

Post by gazmoz17 » 09 Feb 2022, 11:07

Thats great thanks for this Bruce :D

Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests