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

Invocie summary report (Total qty via web cat 1)

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

Invocie summary report (Total qty via web cat 1)

Post by gazmoz17 » 12 Aug 2022, 11:45

Hi Bruce,

Ive managed to add a Weight of the entire invoice via IF.

However, wish to break that weight down via webcateg1. So if web cat1 = UN 1866 then tell me the weight for those items only on said invoice.

Ive tried combining if and sumif with no joy. So then tried to return the weight for UN 1866 when Uom is "KG" (as a separate expression). Then another expression for when UoM in "EA"& then was going to add together....but cant get the 1866 KG expression to return correctly anyway (only returns correct if last inv item line is UN 1866 & UoM is "kg").
weight.report
If

Uom= "kg"?Ivitem qty
Uom= "ea"?Ivitem qty*unitweight

Any help much appreciated.
Login to access the files attached to this post.

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

Re: Invocie summary report (Total qty via web cat 1)

Post by brucedenney » 12 Aug 2022, 14:08

Can you please clarify

via IF ??

Categories are best used via the cat number rather than name, as names can change but the numbers remain.

the principle is pretty simple.

You have an expression in the detail section that calculates the weight this is then totalled to give you the total weight.

If you want to know for Category 1, then add another expression at the detail level that is

CATAGORY_NUMBER=1?weigth times quantity:0

So if the category is not 1 it has a value of 0, now add that up in the footer and you have it.
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: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Invocie summary report (Total qty via web cat 1)

Post by gazmoz17 » 23 Aug 2022, 08:49

Hi Bruce,

Apologies I'm so late getting back to you I did have a draft reply from the day after you responded but not had time revisit!

Thanks to your help I got it working :D

Your completely right about the cat no. rather than name....be handy if Sage had sub categs.

I still really struggle with how the sections work the sage literature for it is cr*p.

I'm trying to achieve the same thing (weights of diff item types) however instead of via invoice I'm trying to do it by Kit product. So we have set up roofing kit products and I want to add up the BoM via prod sub component type for each kit.

So I have tried to make a report that only looks at the roofing kit prods, export to excel as single line for each kit. Then want to export to excel and filter
on certain criteria (e.g. kit size) so I can quickly check kits of the same size have the same qty for certain prods etc. I've imported 360 kits with over 2000 indiv item lines, no way can check the import manually so want this report so can filter on kit size as a control.

Its key that each kit has all relevant info on my report as a single line.

I thought I'd done it and it worked when ran for one prod/kit but when ran for multiple its adding all the successive kit item weights.
Kit BOM report.report
Excel export eg.xlsx
Login to access the files attached to this post.

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

Re: Invocie summary report (Total qty via web cat 1)

Post by gazmoz17 » 12 Oct 2022, 14:55

Hi Bruce,

I've just tried calling you (left a voice mail) it's regarding my last post here. Just so you can make sense of my rambling voice mail 😂.

Might be easier just going off the excels at first.


Columns F:O are summing certain component weights. Report works correctly when ran on individual SKU but not when ran on a range of prods, its summing all the indiv columns as goes along.
Format is perfect, as can filter on Kit type (Col C:E) which are my kit variables....

....then after filtering on variables I can then filter on F:O to ensure all the same quantitys in each column per similar variable combos (C:E).

As I have 1000's of item lines across 100's of kits this is the quickest way of checking I have imported all these kit lines correctly per each kit type.

Any additional rules will be too complicated as there's diff *nuances. The variables, are'nt just "Size" & " Glass".

*CU Brand will have diff ratios of TC but same for everything else.
*TR Brands will have diff qauntities for all other Brands as they have smaller pack sizes for resin, topcoat & catalyst. But the same for CSM.

Thanks
Gareth

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

Re: Invocie summary report (Total qty via web cat 1)

Post by gazmoz17 » 13 Oct 2022, 07:08

Apologies, I had given it a lot of attempts before asking for help orig!...but think Ive cracked it (in wrong section and needs to have a reset section. Juts dont get tehse parts of report designer need to re-red the material instead of guessing stuff. Thanks

Post Reply

Who is online

Users browsing this forum: No registered users and 10 guests