The Custom Script report is a powerful method of giving direct read access to your database. In order to use this function, you must be familiar with both the Linnworks database structure, see here, and creating SQL queries. Please note that you will only be able to use SELECT statements with Query Data Custom Scripts.
Linnworks can offer bespoke report creation as a chargeable service. For more information, terms and conditions, please see click here.
- Go to Dashboards > Query Data.
- Select Custom Script from the drop-down menu.
- Click button +New.
- Add script name and description.
- Paste your script in Configuration.
- Add parameters (if required).
- Enable paging (if required).
- Save the custom script and click Generate Report or Download (CSV File).
Once the query is saved, you can edit it or delete by clicking the respective button.
To make your custom script more specific you can add various parameters, for example:
SELECT o.nOrderId as OrderID, o.Source, o.SubSource FROM [order] o WHERE ( o.[Source] = @Source or @Source = 'All') and o.dReceievedDate between @StartDate and @EndDate
@Source @StartDate and @EndDate are parametres and you would need to specify additional details for them. These details should be added for each parameter that you are going to use in your custom script:
- Query Parameter Name: same name as in the script but without @.
- Display Name: how you would like it to be displayed in the Query Data screen.
- Display Type:
- Default Value: optional, if added it can be edited in the Query Data screen
- Sort Order: parameter order number in the Query Data screen
- Database Type:
Select Parameter query for Source in Dropdown Query
SELECT 'All' as [Value], 'All' as [Text] UNION ALL SELECT DISTINCT Source as [Value], Source as [Text] FROM [System_Channel_Setting]
Once parameters are added you can save the script and use it to generate the report and download a CSV file. Below you can see the result of the parameters added in the example script:
If you expect custom query result to have many rows it is strongly recommended to use paging, otherwise, it can slow down the system performance and as a result, the query will not be executed.
Example of the paged script:
SELECT o.nOrderId as OrderID, o.Source, o.SubSource, TotalRows = COUNT_BIG(*) OVER() FROM [order] o WHERE ( o.[Source] = @Source or @Source = 'All') and o.dReceievedDate between @StartDate and @EndDate ORDER BY o.nOrderId OFFSET @EntriesPerPage * (@PageNumber - 1) ROWS FETCH NEXT @EntriesPerPage ROWS ONLY;
Please Note! SQL query for Select parameter type must contain 2 columns: Text and Value.
SELECT pkStockLocationId as [Value], Location as [Text] FROM [StockLocation] WHERE bLogicalDelete = 0
Below are some more common examples of custom queries that may be useful. Further examples can be found here.
The script below shows the stock level in all Locations.
SELECT si.ItemNumber, si.ItemTitle, sLoc.Location, sl.MinimumLevel, sl.InOrderBook, sl.OnOrder, il.BinRackNumber FROM StockItem si LEFT OUTER JOIN StockLevel sl on si.pkStockItemId = sl.fkStockItemId LEFT OUTER JOIN StockLocation sLoc on sl.fkStockLocationid = sLoc.pkStockLocationId LEFT OUTER JOIN ItemLocation il on il.fkStockItemId = si.pkStockItemID AND il.fkLocationId = sLoc.pkStockLocationId WHERE si.bLogicalDelete = 0 ORDER BY si.ItemNumber, sLoc.Location
Below are the commonly requested relationships for Linnworks orders
- Retrieve inventory items from Open Orders (current Open Orders received today)
SELECT 'Channel Order ID' = o.ReferenceNum, 'Channel Order Date' = o.dReceievedDate, 'Channel' = o.Source + '_' + o.SubSource, 'Channel SKU' = oi.ChannelSKU, 'Qty ordered' = oi.nqty, 'LW Order ID' = o.norderID, 'LW SKU' = si.ItemNumber, 'Lw Title' = si.ItemTitle FROM [Open_Order] o INNER JOIN [Open_OrderItem] oi on o.pkOrderID = oi.fkOrderID LEFT OUTER JOIN [StockItem] si on si.pkstockItemId = fkStockItemID WHERE Convert (DATETIME, FLOOR(CONVERT(FLOAT, o.dReceievedDate))) = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GetDate()))) ORDER BY o.dReceievedDate ASC, o.norderID ASC
- Retrieve inventory items from Processed Orders (orders processed today)
SELECT 'Channel Order ID' = o.ReferenceNum, 'Channel' = o.Source + '_' + o.SubSource, 'Channel SKU' = oi.ChannelSKU, 'Qty ordered' = oi.nqty, 'LW Order ID' = o.norderID, 'LW SKU' = si.ItemNumber, 'Lw Title' = si.ItemTitle, 'Lw Processed Date' = o.dReceievedDate FROM [Order] o INNER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID LEFT OUTER JOIN [StockItem] si on si.pkstockItemId = oi.fkStockItemID_processed WHERE Convert (DATETIME, FLOOR(CONVERT(FLOAT, o.dProcessedOn))) = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) ORDER BY o.dProcessedOn ASC, o.norderID ASC