Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

BOM Summary Excel export

Report design issues and solutions.
Post Reply
gazmoz17
User
User
Posts: 101
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
Login to access the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4611
Joined: 28 Feb 2006, 09:56
Sage Version: v28 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: 101
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 1 guest