Can anyone help me tweak this report for Desktop data custum script. I need to add a column to show if an order is an Amazon B2B business order because the tax is being reported wrong otherwise.
I know the column exists in a .net export so I know it can be done but it doesn't have a field for 'Country Tax Rate' and I need both.
This is the report that works but without the extra 'isbusinessorder' column that I need (with true/false output). Without the business order column the other option I tried was adding notes because it does add a note to each business order but I just got 'The multi-part identifier could not be bound'
@FromDate as datetime,
@ToDate as datetime
SET @FromDate = '2013.01.01 00:00:00.000'
SET @ToDate = '2200.12.31 23:59:59.999'
o.nOrderId AS 'OrderId',
OrderItemSKU = isnull(si.ItemNumber,oi.ChannelSKU),
si.ItemTitle AS 'Title',
o.fPostageCost AS 'PostageCost',
oi.CostIncTax AS 'UnitCost',
oi.Taxrate AS 'VATRate',
oi.CostIncTax - oi.Cost AS 'Tax amount',
Total = o.fTotalCharge,
oi.nQty AS 'Quantity',
pc.CategoryName AS 'ProductCategory',
c.cCountry AS 'Country',
o.cCurrency AS 'Currency',
c.TaxRate AS 'CountryTaxRate',
o.cFullName AS 'Customer Name',
o.Source AS 'Source',
o.SubSource AS 'SubSource',
o.dProcessedOn AS 'ProcessedDate'
FROM [Order] o
LEFT OUTER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID
LEFT OUTER JOIN StockItem si on si.pkStockItemID = oi.fkStockItemId_processed
LEFT OUTER JOIN ProductCategories pc on pc.CategoryId = si.CategoryId
INNER JOIN ListCountries c on o.fkCountryId = c.pkCountryId
o.bProcessed = 1
AND (o.dProcessedOn BETWEEN '07/01/2019' AND '08/01/2019')
I have taken the liberty and went into your system and added Custom Query called Order Details With Amazon B2B, its the same query as Order Details but with additional column IsB2B. You can see the syntax of the query by Editing the query.
Please use Linnworks.net to run such queries, it has proper schema separation and controls the load on the queries properly. Linnworks Desktop doesn't and the query might not run.