I'm working on a Custom Query to give me a sales report by brand (a simple report that seems missing in Linnworks) so far I think I have managed to get pretty close. Without a conclusive list of table names, I've had to do a lot of detective work to get the final query below. 

As such, I'm not 100% sure the data called is what I need, please can somebody sense check that I have the right tables for 'Item Cost Price', 'Item Sold Price' and 'Item Sold Quantity'? 

We have a lot of composite products that sell so ideally I need sale data from individual child items from within a sold composite and not count the parent product itself. We have an Extended Property for 'Brand' so that's why I'm using that in the query, also it will allow me to run the same report on other Extended Properties we use allowing more insights.  

I'm not a SQL dev so most of this script has been 'cut-and-shut' from other scripts found on this forum.

SET DATEFORMAT YMD;

DECLARE

@FromDate AS DATETIME,

@ToDate AS DATETIME

--Change the number at the end to the number of days from, e.g ‘-365’ for year to date.

SET @FromDate = GETDATE()-365

--

SET @ToDate = GETDATE()

SELECT

SUM(oi.nqty) AS 'Total Units Sold', 

COUNT(o.norderID) AS 'Total Orders', 

SUM(oi.CostIncTax) AS 'Revenue', 

SUM(oi.fPricePerUnit) AS 'Purchase Price', 

SUM(oi.CostIncTax - oi.fPricePerUnit) AS 'Profit', 

-- This is the column title, change this to the Item Extended Property name want to query, e.g. ‘Brand’.

e.ProperyValue AS 'Brand' 

--

FROM [Order] o

INNER JOIN [OrderItem] oi ON oi.fkOrderId = o.pkOrderId

INNER JOIN [StockItem_ExtendedProperties] e ON e.fkStockItemId = oi.fkStockItemId_processed

WHERE o.dProcessedOn BETWEEN @FromDate AND @ToDate

-- Change this to the Item Extended Property name to want to query, e.g. ‘Brand’.

AND e.ProperyName = 'Brand'

--

GROUP BY e.ProperyValue

Linnworks query for brand sold by month

SET DATEFORMAT  YMD;

DECLARE
@FromDate AS DATETIME,
@ToDate AS DATETIME


--Change the number at the end to the number of days from, e.g ‘-365’ for year to date.
SET @FromDate = GETDATE()-365
--

SET @ToDate = GETDATE()

SELECT
SUM(oi.nqty) AS 'Total Units Sold', 
COUNT(o.norderID) AS 'Total Orders', 
SUM(oi.CostIncTax)  AS 'Revenue', 
SUM(oi.fPricePerUnit)  AS 'Purchase Price', 
SUM(oi.CostIncTax - oi.fPricePerUnit)  AS 'Profit', 

-- This is the column title, change this to the Item Extended Property name want to query, e.g. ‘Brand’.
e.ProperyValue AS 'Brand' 
--

FROM [Order] o
INNER JOIN [OrderItem] oi ON oi.fkOrderId = o.pkOrderId
INNER JOIN [StockItem_ExtendedProperties] e ON e.fkStockItemId = oi.fkStockItemId_processed
WHERE o.dProcessedOn BETWEEN @FromDate AND @ToDate

-- Change this to the Item Extended Property name to want to query, e.g. ‘Brand’.
AND e.ProperyName = 'Brand'
--

GROUP BY e.ProperyValue