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

Report with BOM

Report design issues and solutions.
Post Reply
evansandreid
User
User
Posts: 5
Joined: 08 Nov 2023, 16:05
Sage Version: v29 UK/Europe/Africa

Report with BOM

Post by evansandreid » 12 Apr 2024, 22:04

I am trying to create a "picking note" that on some occasions will have a product containing a BOM product code, the issue I am having is that when there is a BOM product the weight and quantity is incorrect, it seems to add up all the product codes as well as the BOM products also.

I did tag on to the following but did not workout how to get it to work viewtopic.php?t=4366

Is there a way to rectified this? or can someone point me in the right direction?

If have created a kind of work around by using a sub report, but ideally I would like to be able to use the BOM and multiply by the QTY.ORDER to work out the correct number of items and weight

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

Re: Report with BOM

Post by brucedenney » 14 Apr 2024, 09:16

Please attach your report so we can see what you have done.
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.

evansandreid
User
User
Posts: 5
Joined: 08 Nov 2023, 16:05
Sage Version: v29 UK/Europe/Africa

Re: Report with BOM

Post by evansandreid » 14 Apr 2024, 20:49

Sorry here it is. thought I had attached my layouts

The picking note with sub report shows the correct weight and number of items, but the sub report will not multiply the sop_item.qty_order by stock_comp.component_qty

the picking note with bom is the one we would like to use so we can multiply the sop_item.qty_order by stock_comp.component_qty to show the total number of components needed if the quantity ordered is greater than 1 and calculate the number items ordered

Since I sent the first messaged I've managed to get the weight to calculate correctly, not sure if I've done it the best way but the end result calculates correctly, I tried to apply the same method to the number of items hoping that would work but it did not.

ideally the report will show the weight and number of items ordered (ignoring the bom components) as in the pdf example
Login to access the files attached to this post.

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

Re: Report with BOM

Post by brucedenney » 15 Apr 2024, 09:46

You do not want to use a sub report.

Just to be clear you want the total quantity of items on the order (excluding BOM components).
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.

evansandreid
User
User
Posts: 5
Joined: 08 Nov 2023, 16:05
Sage Version: v29 UK/Europe/Africa

Re: Report with BOM

Post by evansandreid » 17 Apr 2024, 20:53

Yes, total quantity of items on the order (excluding BOM components).

If using a sub report is easier, I did manage calculate the total number of items when using a sub report. However I couldn't get the sub report to multiply qty order and the BOM components

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

Re: Report with BOM

Post by brucedenney » 18 Apr 2024, 11:11

You do not want to be using a sub report.

The problem you have is that it is adding up the Qty_Order for all the lines in the details section (where the BOM is printed)
You only want to add it up when the Sop_Item changes.

To do this select the QTY_ORDER expression in the Page footer (I would typically do this in an ORDER_NUMBER footer as if you have an order that goes over 2 pages you will get nonsense) then in the properties find the Evaluate Section and set that to the ITEM_NUMBER Footer, this means it will only add the number when it is in the footer, so the appearance of it in the details section will no longer have any effect.
Screenshot 2024-04-18 121107.jpg
Login to access the files attached to this post.
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.

evansandreid
User
User
Posts: 5
Joined: 08 Nov 2023, 16:05
Sage Version: v29 UK/Europe/Africa

Re: Report with BOM

Post by evansandreid » 10 Jun 2024, 21:43

Absolute legend, exactly what we need.

Thankyou so much

Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests