Page 1 of 1

Sales Ledger analysis 1

Posted: 12 Nov 2021, 15:23
by gazmoz17

Extremely stumped by this price list report. It will pull through most of my customers SALES_LEDGER.ANALYSIS_1 field (which is market type)....but not all of them. Cant see any filter/conditional formatting on that field, cant find any common denominator with all the customers that this field doesn't pull through for?
correct double
extract 2.JPG

Re: Sales Ledger analysis 1

Posted: 12 Nov 2021, 15:56
by brucedenney
Where is this report kept?

Re: Sales Ledger analysis 1

Posted: 12 Nov 2021, 15:59
by gazmoz17
Hi mate,

Its kept: C:\ProgramData\Sage\Accounts\2021\Company.000\REPORTS\Products\My Products Reports


Re: Sales Ledger analysis 1

Posted: 15 Nov 2021, 11:40
by brucedenney
Why have you got a filter of (PRICE.TYPE = "P") AND PRICE.TYPE=PRICE_LIST.TYPE

You have the Sales Ledger added twice?

The joins seem strange and mixed up.

I can't even work out what you are trying to do.

Can you explain what you are trying to do?

It looks like a profit report for products, but only for things sold by the KG

Unit weight seems to not be used as a weight or is it?

I suspect it needs a lot of explanation.

Re: Sales Ledger analysis 1

Posted: 16 Nov 2021, 09:43
by gazmoz17
Hi Bruce,

Thanks for reply. Its a long time ago I made this report, I cant remember If I edited the joins ( maybe/prob I did hence why there 2 sales ledger joins if this is what your referring to). Its still guesswork for me the join editor (think sage literature is cr*p prob on purpose to encourage you to use there report service). Also not too familiar with price list reports, think this is the only one Ive ever edited from an existing, which is prob why maybe the filter doesn't make sense....

...But I know the report works bang on for our use (prob not most efficient way maybe) other than not pulling through the mkt type/analysis 1 for every product line.

Our Price list setup & use

We don't create a price list by customer type or product grouping.... I think would become untenable. Each of our Price lists is just called the customers name and a/c reference with all there unique prod prices under said price list. So if I want to see the price for Joe Bloggs Ltd we go into the price list called "Joe Bloggs Ltd".

I don't like the way Sage refers to "List Price" as customers own unique price. Internally we always refer to List price as standard sales price e.g. none a/c customer who doesn't have there own price just normal customer through the door. So the Blue header in my report "List price" Is just STOCK.SALES_PRICE normal price.

The blue columns are showing the profit of the product @ "List Price (Ours)/standard" against the purple which is the profit based on the customers own price. I have a supplier column which is STOCK.PURCHASE_REF & a product category column so I can filter the report in excel based upon these 2 variables. Reason is if I receive new Supplier excel price lists I can run this report, filter on said supplier & pass on there increase and see how this affects margin & then decide whether to just pass on the cost price increase per kg eg 10p per kg....see how this affects margin or decide just passing on the 10p per kg increase onto sale price isn't enough and we want to maintain margin. So then just makes sales price x or y to achieve/maintain desired margin. Same if I know there's a general increase across a certain prod category I'll filter on category and then forecast.

Even if prices remain the same reports handy to see what customers pay for Product Z filter on Z and compare what our entire customer list (who have own prices) pay for Product Z. Are customers getting a better price compared to others, where its not justified due to customer turnover etc...then whats the reason for this. Or I'll then pivot based on Rank of "List Prices Sages"/ My Purple Sales Price.

Unit Weight

I do use this again to filter on in excel, so might want to filter on weight (20-25), Unit of Sale (Kg) & then category have a look at all kegs of resin, comparison between "List price" and ranking of customers own price (purples).


In the industry a lot of items are priced per kg. Its just the way it is, certainly on the purchasing side for ourselves, as we buy from large volume manufactures. B2B sales to fellow distributors or end uses of a certain scale they will also expect supply per kg pricing, so on our sales side. However, I think we should be sales led and a lot of our business is becoming to the end user, who is understandably confused by per kg pricing. Therefore, more and more product codes are sold as Unit of sale "EA" which is each, however will often be bought from our supplier as Per kg and have to be converted a EA cost price.

Conditional Formatting

90% of our price lists are set to "Fixed". Some are "Standard" but I want to discourage this as it adds a step when I'm increasing Price List prices with an import tool...I increase Standard Sales price first on product module and then go in and manipulate this report to then import Customer unique price increase. Have to strip out standard sales price from this second import as Ive already increased these prices on my initial report and can end up doubling up on a price increase. Have green conditional formatting just to highlight who has a "Standard Sales" price setup just to highlight when analyzing or as a signal to strip out when importing increase on customer price lists after Ive already done Sage "List Prices (ours)". Conditional format when Customers own price is below 20% as that's our KPI as a flag for low margin & why?




This column we want to see the profit per item/unit even if we buy/sell Per Kg. e.g. so we can see what we make per keg (20-25kg) Drum (200kg-220k). We have KPI's (loose granted) for what we want to make per keg of gelcoat etc. This per unit KPI sometimes easier than looking at per kg profit, particularly as cost prices so volatile at the moment margin significance has become a bit blurred distorted so we want to look at absolute unit profit.

Summing Up

There's prob a better more efficient way of achieving my aims than this report, but it does work very well other than Mkt analysis field 1 issue which I cant solve.

Reason why I need Mkt field instead of using Stock categories to filter and establish diff between ROOFING & GRP products/customers....

Many products will be sold into both Mkt types e.g. chopped strand matt will be sold to roofers & GRP trade a/c's the very same prod SKU etc. So cant just sort on Categ without duplicating the prod across diff skus & sku categ which is untenable. I have an invoice profit report (youve helped with before) ran once a month that established whos a new customers and by what they have bought the report flags what Mkt type I should allocate the customer to then do an import run. After import run if they become a Price list customer the Mkt column P should populate so I can just look at ROOFING customers/ products etc even if only have there own price for chopped strand matt they will be correctly classified as a roofing customer rather than a GRP customer & vice versa if a GRP customer only has there own price for chopped strand matt.

As always any help much appreciated.


Re: Sales Ledger analysis 1

Posted: 16 Nov 2021, 15:35
by brucedenney
Okay so would you say the objective of the report is to

compare the profitability of products based on list prices and pristlist prices to find products that need prices changing?

Re: Sales Ledger analysis 1

Posted: 16 Nov 2021, 15:38
by gazmoz17
Yeah thats right mate. Send it to excel so easier to manipulate and compare.

Re: Sales Ledger analysis 1

Posted: 17 Nov 2021, 10:45
by brucedenney
try this out

Re: Sales Ledger analysis 1

Posted: 17 Nov 2021, 15:58
by gazmoz17
Wow awesome mate 🤗🤩. So is this ODBC you've mentioned before? (read about it but was over my head) could connect live data to report anything within the data?

If I made worksheet 1 "Sage Inv" as source data for pivot tables does that mean they would auto update all the pivot tables & associated charts once I clicked your "GO Update the sheet" button & Refresh data on PivotTable Analyze.

Re: Sales Ledger analysis 1

Posted: 17 Nov 2021, 16:24
by gazmoz17
Do I need to put full Version ref or just 27?
Do I need to create new data source or just click the bottom option there: Machine Data Source/ SageLine50v27 System
Data source.JPG
Ive prob set it up a bit wrong as sometimes crashing or struggling to update regardless of what Machine Data Source I'm choosing?


Re: Sales Ledger analysis 1

Posted: 18 Nov 2021, 12:03
by brucedenney
Just the major version eg 27

I use pivot tables a lot with these sheets, so much so there is an extra for them.

The GO button does 2 things, it looks for sheets in the workbook that start with "sage" and on those sheets it refreshes the data via odbc with Sage. It also looks for any sheets that start with "piv" and refreshes the pivot table.

So name your pivot table sheets piv---- and they will get auto refreshed along with the data.