Skip to content
P4 Software P4 Software / logisticaHQ-en

Add these docs to Claude

Ask Claude questions about these docs. Claude reads the published pages and links you back to them. Copy this address:

https://p4-software.opendocs.page/mcp

In claude.ai or Claude Desktop: open Settings, then Connectors, choose Add custom connector and paste the address.

In Claude Code, run:

claude mcp add --transport http docs https://p4-software.opendocs.page/mcp

Widget examples

Widget examples
← Back to Widgets

LogisticaHQ comes with 59 example Widgets, from a first COUNT(*) to filters and WITH. Each one is a complete widget: the query and the presentations that show it. Every example was run on a working LogisticaHQ company before it was published. Read them here, or load one into a widget and try it.

Load an example into a widget

  1. Open Setup > Reporting > Widgets and open a widget, or press New for a fresh one.
  2. On the General tab, press Examples above the SQL editor. The button shows how many examples there are. The list is grouped as on this page, and each entry names the presentations it sets up, for example Pie · Grid.
  3. Pick one. It replaces the query and the presentations; Name and Description stay as they are. A note Example loaded and an Undo button appear beside SQL Query: Undo brings back your query and presentations. Once you change the query, the example is yours and Undo goes away.
  4. Press Execute (or Ctrl+Enter) to see the rows, then open the presentation tabs to see how each one uses the column names.
  5. Press Update to save. Nothing is saved before that.

How the examples are written

  • Every column is named with AS [Name], and the presentations refer to those names exactly: argument, value and series fields, grid and tile columns, the background colour column and the @{Column} in a link mask.
  • One widget serves Warehouse staff and client Users. Wherever the table has a client, the query filters with (@ClientId IS NULL OR x.ClientId = @ClientId): @ClientId is empty for Warehouse staff, who see every client, and is the viewer's own client on the client portal. The few Warehouse-wide examples (Bins, Zones) say so; keep them on staff Dashboards.
  • Days are written with CONVERT(char(10), date, 126), which gives YYYY-MM-DD: it sorts correctly and shows no time on a chart.
  • Divisions use NULLIF(..., 0), so a quiet day Returns nothing instead of an error, and totals are rounded with CAST, because a scalar shows its number exactly as the query Returns it.
  • A gauge or a scalar reads the first column of the first row, so those queries return their number first.

Filters that also work in Execute

A filter such as @{Days:number} becomes a box on each dashboard panel, and the dashboard writes the typed value into the query in its place. Execute on the widget screen does not fill filters in, so the examples write every filter in quotes and read it with a default. The query then runs in Execute, on a panel whose box is left empty, and on a panel with a value.

KindWriteGives
NumberISNULL(TRY_CAST(NULLIF('@{Days:number}', '') AS int), 30)The number typed on the panel, or 30.
DateISNULL(TRY_CAST(NULLIF('@{From:date}', '') AS date), CAST(DATEADD(day, -30, GETDATE()) AS date))The date picked on the panel, or 30 days ago.
TextNULLIF(NULLIF('@{Warehouse}', ''), '@' + '{Warehouse}')The text typed on the panel, or nothing (no filter). The second value is split, '@' + '{Warehouse}', so the dashboard never replaces it: while the first still equals it, nothing was typed.

The shorter form on the Widgets page, @{Days:number} without quotes, also works on a dashboard. It only fails in Execute, which leaves the placeholder as it is typed.

The examples

59 examples in 11 groups:

  • Start here (8): 1. Open orders (one number); 2. Newest orders (a list); 3. Orders by state (GROUP BY); 4. Orders per customer (JOIN); 5. Orders created per day (dates); 6. Readable labels with CASE; 7. A percentage without dividing by zero; 8. One query, several presentations
  • Charts (10): 9. Column chart: units received per day; 10. Bar chart: top 10 Products picked; 11. Line chart: orders shipped per day; 12. Area chart: Warehouse activity per day; 13. Pie chart: stock by Warehouse; 14. Several pies: one per Warehouse; 15. Pies as percentages (window function); 16. Line chart: one line per Warehouse; 17. Columns split by movement type; 18. Weekly totals
  • Grids (9): 19. Number and date columns; 20. Currency column: 3PL Invoices; 21. Percentage column: open order progress; 22. Yes/no columns: Bins to count; 23. Date and time column: latest movements; 24. Link column: open the order; 25. Link columns: product and bin; 26. Search, filters and Excel export; 27. Ranked list (ROW_NUMBER)
  • Tiles (5): 28. One tile per Warehouse; 29. Traffic-light tiles: late orders; 30. Zone fullness tiles; 31. Coloured lines on a tile; 32. Today at a glance
  • Numbers and gauges (6): 33. Single number: units on hand; 34. Dial gauge: on-time shipping; 35. Dial gauge: how full the Warehouse is; 36. Horizontal bar gauge: order fill rate; 37. Upright bar gauge: picking progress; 38. Gauge on a count, not a percentage
  • Filters and parameters (7): 39. @ClientId: one widget for staff and Clients; 40. @UtcNow: who worked in the last 7 days; 41. Text filter: one Warehouse; 42. Number filter: the last N days; 43. Date filters: between two dates; 44. Two filters together: Warehouse and days; 45. Search filter: SKU contains
  • Inventory (5): 46. Stock by product category; 47. Lots expiring in the next 60 days; 48. Stock value by client; 49. Pallets by age; 50. Products spread over many Bins (HAVING)
  • Inbound (3): 51. Purchase orders due within 7 days or overdue; 52. Top Vendors by units received; 53. Hours to receive, per week
  • Outbound (2): 54. Late orders by customer; 55. Orders by destination
  • Labour and quality (2): 56. Busiest hours of the day (7 days); 57. QC hold rate per week
  • 3PL Clients (2): 58. Invoiced per client (6 months); 59. Client activity summary (WITH)

Start here

1. Open orders (one number)

Your first widget: COUNT(*) Returns one number, which a Scalar presentation shows in large type.

SELECT COUNT(*) AS [OpenOrders]
FROM PickTickets
WHERE ShippedDate IS NULL
  AND CancelDate IS NULL
  AND (@ClientId IS NULL OR ClientId = @ClientId)

Presentations

PresentationTypeSettings
Open ordersScalarShows the first column of the first row, in large type.

2. Newest orders (a list)

TOP 20 with ORDER BY gives a short list, newest first. Every column is named with AS, and the grid columns use those names.

SELECT TOP 20 pt.PickTicketNumber AS [Order],
       pt.PickTicketState AS [State],
       pt.DateCreated AS [Created],
       pt.TotalLines AS [Lines],
       pt.TotalQuantity AS [Units]
FROM PickTickets pt
WHERE (@ClientId IS NULL OR pt.ClientId = @ClientId)
ORDER BY pt.DateCreated DESC

Presentations

PresentationTypeSettings
Newest ordersGridPage size: 20
  • Order → Order · String · Left
  • State → State · String · Left
  • Created → Created · Date Time · Left
  • Lines → Lines · Number · Right
  • Units → Units · Number · Right

3. Orders by state (GROUP BY)

GROUP BY turns rows into one count per value. A pie shows the share of each state, and a grid lists the same numbers.

SELECT pt.PickTicketState AS [State],
       COUNT(*) AS [Orders]
FROM PickTickets pt
WHERE (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY pt.PickTicketState
ORDER BY COUNT(*) DESC

Presentations

PresentationTypeSettings
PieChartChart type: Pie
Argument field: State
Value field: Orders
TableGridPage size: 20
  • State → State · String · Left
  • Orders → Orders · Number · Right

4. Orders per customer (JOIN)

JOIN brings in the customer name from another table. LEFT JOIN keeps orders with no customer, and ISNULL gives them a label.

SELECT TOP 10 ISNULL(c.CompanyName, '(no customer)') AS [Customer],
       COUNT(*) AS [Orders]
FROM PickTickets pt
LEFT JOIN Customers c ON c.Id = pt.CustomerId
WHERE pt.DateCreated >= DATEADD(day, -90, GETDATE())
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY ISNULL(c.CompanyName, '(no customer)')
ORDER BY COUNT(*) DESC

Presentations

PresentationTypeSettings
BarChartChart type: Bar
Argument field: Customer
Value field: Orders
TableGridPage size: 20
  • Customer → Customer · String · Left
  • Orders → Orders · Number · Right

5. Orders created per day (dates)

Dates grouped by day. CONVERT(char(10), ..., 126) writes the day as YYYY-MM-DD, which sorts correctly and shows no time.

SELECT CONVERT(char(10), pt.DateCreated, 126) AS [Day],
       COUNT(*) AS [Orders]
FROM PickTickets pt
WHERE pt.DateCreated >= DATEADD(day, -30, GETDATE())
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY CONVERT(char(10), pt.DateCreated, 126)
ORDER BY [Day]

Presentations

PresentationTypeSettings
ColumnChartChart type: Column
Argument field: Day
Value field: Orders
TableGridPage size: 20
  • Day → Day · String · Left
  • Orders → Orders · Number · Right

6. Readable labels with CASE

CASE turns stored values into words people understand: here, how late each open order is. The inner SELECT labels each order and the outer one counts the labels.

SELECT x.Status AS [Status],
       COUNT(*) AS [Orders]
FROM (SELECT CASE WHEN pt.RequiredDate IS NULL THEN 'No required date'
                  WHEN pt.RequiredDate < CAST(GETDATE() AS date) THEN 'Late'
                  WHEN pt.RequiredDate < DATEADD(day, 2, CAST(GETDATE() AS date)) THEN 'Due today or tomorrow'
                  ELSE 'On time' END AS Status
      FROM PickTickets pt
      WHERE pt.ShippedDate IS NULL
        AND pt.CancelDate IS NULL
        AND (@ClientId IS NULL OR pt.ClientId = @ClientId)) x
GROUP BY x.Status
ORDER BY COUNT(*) DESC

Presentations

PresentationTypeSettings
PieChartChart type: Pie
Argument field: Status
Value field: Orders
TableGridPage size: 20
  • Status → Status · String · Left
  • Orders → Orders · Number · Right

7. A percentage without dividing by zero

NULLIF(COUNT(*), 0) stops a division by zero on a quiet day, and CAST(... AS decimal(5,1)) rounds to one decimal.

SELECT CAST(100.0 * SUM(CASE WHEN pt.ShippedDate <= pt.RequiredDate THEN 1 ELSE 0 END)
            / NULLIF(COUNT(*), 0) AS decimal(5,1)) AS [OnTimePercent]
FROM PickTickets pt
WHERE pt.ShippedDate >= DATEADD(day, -90, GETDATE())
  AND pt.RequiredDate IS NOT NULL
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)

Presentations

PresentationTypeSettings
On-time %ScalarShows the first column of the first row, in large type.

8. One query, several presentations

The same rows three ways: a column chart, a pie and a grid. Each dashboard panel picks the presentation it shows.

SELECT w.WarehouseCode AS [Warehouse],
       COUNT(*) AS [OpenOrders]
FROM PickTickets pt
JOIN Warehouses w ON w.Id = pt.WarehouseId
WHERE pt.ShippedDate IS NULL
  AND pt.CancelDate IS NULL
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY w.WarehouseCode
ORDER BY COUNT(*) DESC

Presentations

PresentationTypeSettings
ColumnsChartChart type: Column
Argument field: Warehouse
Value field: OpenOrders
PieChartChart type: Pie
Argument field: Warehouse
Value field: OpenOrders
TableGridPage size: 20

Charts

9. Column chart: units received per day

Upright columns, one per day. The argument field (Day) runs along the bottom and the value field (Units) sets each height.

SELECT CONVERT(char(10), a.Timestamp, 126) AS [Day],
       CAST(SUM(ABS(ISNULL(a.Quantity, 0))) AS bigint) AS [Units]
FROM AuditRecords a
WHERE a.SubType IN ('ReceivePo', 'NonPoReceive', 'ReceiveRma', 'NonRmaReceive')
  AND a.Timestamp >= DATEADD(day, -30, GETDATE())
  AND (@ClientId IS NULL OR a.ClientId = @ClientId)
GROUP BY CONVERT(char(10), a.Timestamp, 126)
ORDER BY [Day]

Presentations

PresentationTypeSettings
Units receivedChartChart type: Column
Argument field: Day
Value field: Units

10. Bar chart: top 10 Products picked

Bars run sideways, which leaves room for long names. TOP 10 with ORDER BY keeps only the biggest.

SELECT TOP 10 a.Sku AS [Sku],
       CAST(SUM(ABS(ISNULL(a.Quantity, 0))) AS bigint) AS [UnitsPicked]
FROM AuditRecords a
WHERE a.SubType = 'Pick'
  AND a.Timestamp >= DATEADD(day, -30, GETDATE())
  AND (@ClientId IS NULL OR a.ClientId = @ClientId)
GROUP BY a.Sku
ORDER BY SUM(ABS(ISNULL(a.Quantity, 0))) DESC

Presentations

PresentationTypeSettings
Top ProductsChartChart type: Bar
Argument field: Sku
Value field: UnitsPicked

11. Line chart: orders shipped per day

A line shows a trend over time. Only days with shipments return a row, so quiet days are simply skipped.

SELECT CONVERT(char(10), pt.ShippedDate, 126) AS [Day],
       COUNT(*) AS [Orders]
FROM PickTickets pt
WHERE pt.ShippedDate >= DATEADD(day, -90, GETDATE())
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY CONVERT(char(10), pt.ShippedDate, 126)
ORDER BY [Day]

Presentations

PresentationTypeSettings
Orders shippedChartChart type: Line
Argument field: Day
Value field: Orders

12. Area chart: Warehouse activity per day

An area chart is a filled line, good for volumes. Every movement in the Audit trail counts, except screen actions (UserAction).

SELECT CONVERT(char(10), a.Timestamp, 126) AS [Day],
       COUNT(*) AS [Transactions]
FROM AuditRecords a
WHERE a.Timestamp >= DATEADD(day, -30, GETDATE())
  AND a.Type <> 'UserAction'
  AND (@ClientId IS NULL OR a.ClientId = @ClientId)
GROUP BY CONVERT(char(10), a.Timestamp, 126)
ORDER BY [Day]

Presentations

PresentationTypeSettings
ActivityChartChart type: Area
Argument field: Day
Value field: Transactions

13. Pie chart: stock by Warehouse

Stock sits in a bin or on a licence plate, so the Warehouse comes from the bin's zone or from the plate: ISNULL(z.WarehouseId, lp.WarehouseId).

SELECT w.WarehouseCode AS [Warehouse],
       CAST(SUM(il.Quantity) AS bigint) AS [Units]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
LEFT JOIN Bins b ON b.Id = il.BinId
LEFT JOIN Zones z ON z.Id = b.ZoneId
LEFT JOIN LicensePlates lp ON lp.Id = il.LicensePlateId
JOIN Warehouses w ON w.Id = ISNULL(z.WarehouseId, lp.WarehouseId)
WHERE il.Quantity > 0
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY w.WarehouseCode
ORDER BY SUM(il.Quantity) DESC

Presentations

PresentationTypeSettings
Stock shareChartChart type: Pie
Argument field: Warehouse
Value field: Units
TableGridPage size: 20

14. Several pies: one per Warehouse

A series field on a pie draws one small pie per value: order states, one pie per Warehouse. Value format n0 writes whole numbers on the slices.

SELECT pt.PickTicketState AS [State],
       w.WarehouseCode AS [Warehouse],
       COUNT(*) AS [Orders]
FROM PickTickets pt
JOIN Warehouses w ON w.Id = pt.WarehouseId
WHERE pt.ShippedDate IS NULL
  AND pt.CancelDate IS NULL
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY pt.PickTicketState, w.WarehouseCode
ORDER BY w.WarehouseCode, COUNT(*) DESC

Presentations

PresentationTypeSettings
PiesChartChart type: Pie
Argument field: State
Value field: Orders
Series field: Warehouse
Value format: n0

15. Pies as percentages (window function)

Each client's share of each Warehouse. SUM(...) OVER (PARTITION BY ...) totals every Warehouse, and value format p1 writes 0.25 as 25.0%.

SELECT w.WarehouseCode AS [Warehouse],
       ISNULL(cl.Name, '(no client)') AS [Client],
       CAST(SUM(il.Quantity) / NULLIF(SUM(SUM(il.Quantity)) OVER (PARTITION BY w.WarehouseCode), 0) AS decimal(9,4)) AS [Share]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
LEFT JOIN Clients cl ON cl.Id = p.ClientId
LEFT JOIN Bins b ON b.Id = il.BinId
LEFT JOIN Zones z ON z.Id = b.ZoneId
LEFT JOIN LicensePlates lp ON lp.Id = il.LicensePlateId
JOIN Warehouses w ON w.Id = ISNULL(z.WarehouseId, lp.WarehouseId)
WHERE il.Quantity > 0
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY w.WarehouseCode, ISNULL(cl.Name, '(no client)')
ORDER BY w.WarehouseCode, [Share] DESC

Presentations

PresentationTypeSettings
Client shareChartChart type: Pie
Argument field: Client
Value field: Share
Series field: Warehouse
Value format: p1

16. Line chart: one line per Warehouse

With a series field the chart draws one line per value. The query Returns one row per day and Warehouse.

SELECT CONVERT(char(10), pt.ShippedDate, 126) AS [Day],
       w.WarehouseCode AS [Warehouse],
       COUNT(*) AS [Orders]
FROM PickTickets pt
JOIN Warehouses w ON w.Id = pt.WarehouseId
WHERE pt.ShippedDate >= DATEADD(day, -90, GETDATE())
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY CONVERT(char(10), pt.ShippedDate, 126), w.WarehouseCode
ORDER BY [Day], [Warehouse]

Presentations

PresentationTypeSettings
By WarehouseChartChart type: Line
Argument field: Day
Value field: Orders
Series field: Warehouse

17. Columns split by movement type

One coloured column per movement type each day, from a series field. IN (...) keeps only the movement types you want to compare.

SELECT CONVERT(char(10), a.Timestamp, 126) AS [Day],
       a.SubType AS [Movement],
       COUNT(*) AS [Transactions]
FROM AuditRecords a
WHERE a.Timestamp >= DATEADD(day, -14, GETDATE())
  AND a.SubType IN ('Pick', 'ReceivePo', 'ProductMove', 'AdjustIn', 'AdjustOut')
  AND (@ClientId IS NULL OR a.ClientId = @ClientId)
GROUP BY CONVERT(char(10), a.Timestamp, 126), a.SubType
ORDER BY [Day], [Movement]

Presentations

PresentationTypeSettings
MovementsChartChart type: Column
Argument field: Day
Value field: Transactions
Series field: Movement

18. Weekly totals

DATEDIFF from a known Monday (1 January 1900) puts every date in its week, whatever the server's first day of the week.

SELECT CONVERT(char(10), DATEADD(day, DATEDIFF(day, '19000101', CAST(pt.ShippedDate AS date)) / 7 * 7, CAST('19000101' AS date)), 126) AS [WeekStarting],
       COUNT(*) AS [Orders],
       CAST(SUM(pt.TotalQuantity) AS bigint) AS [Units]
FROM PickTickets pt
WHERE pt.ShippedDate >= DATEADD(week, -12, GETDATE())
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY DATEADD(day, DATEDIFF(day, '19000101', CAST(pt.ShippedDate AS date)) / 7 * 7, CAST('19000101' AS date))
ORDER BY [WeekStarting]

Presentations

PresentationTypeSettings
Orders per weekChartChart type: Column
Argument field: WeekStarting
Value field: Orders
TableGridPage size: 20
  • WeekStarting → Week starting · String · Left
  • Orders → Orders · Number · Right
  • Units → Units · Number · Right

Grids

19. Number and date columns

The value type decides how a column is written and sorted: Number aligns right, Date shows the day without the time.

SELECT TOP 100 po.PurchaseOrderNumber AS [PO],
       ISNULL(v.CompanyName, '(no vendor)') AS [Vendor],
       po.ReceivingCompleted AS [Received],
       po.TotalQuantity AS [Units]
FROM PurchaseOrders po
LEFT JOIN Vendors v ON v.Id = po.VendorId
WHERE po.ReceivingCompleted IS NOT NULL
  AND (@ClientId IS NULL OR po.ClientId = @ClientId)
ORDER BY po.ReceivingCompleted DESC

Presentations

PresentationTypeSettings
ReceiptsGridPage size: 20
  • PO → PO · String · Left
  • Vendor → Vendor · String · Left
  • Received → Received · Date · Left
  • Units → Units · Number · Right

20. Currency column: 3PL Invoices

Currency writes the amount with two decimals and thousands separators. StartPeriod and EndPeriod are the billed period.

SELECT TOP 100 i.InvoiceNumber AS [Invoice],
       ISNULL(cl.Name, '(no client)') AS [Client],
       i.ClientInvoiceState AS [State],
       i.StartPeriod AS [PeriodFrom],
       i.EndPeriod AS [PeriodTo],
       i.Total AS [Total]
FROM ClientInvoices i
LEFT JOIN Clients cl ON cl.Id = i.ClientId
WHERE (@ClientId IS NULL OR i.ClientId = @ClientId)
ORDER BY i.DateCreated DESC

Presentations

PresentationTypeSettings
InvoicesGridPage size: 20
  • Invoice → Invoice · String · Left
  • Client → Client · String · Left
  • State → State · String · Left
  • PeriodFrom → From · Date · Left
  • PeriodTo → To · Date · Left
  • Total → Total · Currency · Right

21. Percentage column: open order progress

Percentage expects 0 to 100 and adds the % sign. PercentageAllocated and PercentagePicked are stored from 0 to 1, so the query multiplies by 100.

SELECT TOP 100 pt.PickTicketNumber AS [Order],
       pt.PickTicketState AS [State],
       CAST(pt.PercentageAllocated * 100 AS decimal(5,1)) AS [Allocated],
       CAST(pt.PercentagePicked * 100 AS decimal(5,1)) AS [Picked]
FROM PickTickets pt
WHERE pt.ShippedDate IS NULL
  AND pt.CancelDate IS NULL
  AND pt.ReleasedToFloorDate IS NOT NULL
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
ORDER BY pt.PercentagePicked DESC

Presentations

PresentationTypeSettings
ProgressGridPage size: 20
  • Order → Order · String · Left
  • State → State · String · Left
  • Allocated → Allocated · Percentage · Right
  • Picked → Picked · Percentage · Right

22. Yes/no columns: Bins to count

Boolean shows yes/no columns as ticks. Bins flagged for a count, never counted, or not counted in 90 days. Warehouse-wide: keep it on staff Dashboards.

SELECT TOP 200 w.WarehouseCode AS [Warehouse],
       z.ZoneCode AS [Zone],
       b.BinCode AS [Bin],
       b.CountRequired AS [CountRequired],
       ISNULL(b.IsQuarantine, CAST(0 AS bit)) AS [Quarantine],
       b.LastCountTimestamp AS [LastCounted]
FROM Bins b
JOIN Zones z ON z.Id = b.ZoneId
JOIN Warehouses w ON w.Id = z.WarehouseId
WHERE b.CountRequired = 1
   OR b.LastCountTimestamp IS NULL
   OR b.LastCountTimestamp < DATEADD(day, -90, GETDATE())
ORDER BY b.LastCountTimestamp, w.WarehouseCode, b.BinCode

Presentations

PresentationTypeSettings
Bins to countGridPage size: 20
  • Warehouse → Warehouse · String · Left
  • Zone → Zone · String · Left
  • Bin → Bin · String · Left
  • CountRequired → Count required · Boolean · Center
  • Quarantine → Quarantine · Boolean · Center
  • LastCounted → Last counted · Date Time · Left

23. Date and time column: latest movements

DateTime shows the date and the time. The 50 most recent movements, with who did them and from where to where.

SELECT TOP 50 a.Timestamp AS [When],
       ISNULL(a.Username, '(System)') AS [User],
       a.SubType AS [Movement],
       a.Sku AS [Sku],
       ISNULL(a.FromBin, '') AS [FromBin],
       ISNULL(a.ToBin, '') AS [ToBin],
       a.Quantity AS [Quantity]
FROM AuditRecords a
WHERE a.Type <> 'UserAction'
  AND a.Sku IS NOT NULL
  AND (@ClientId IS NULL OR a.ClientId = @ClientId)
ORDER BY a.Timestamp DESC

Presentations

PresentationTypeSettings
Latest movementsGridPage size: 20
  • When → When · Date Time · Left
  • User → User · String · Left
  • Movement → Movement · String · Left
  • Sku → SKU · String · Left
  • FromBin → From · String · Left
  • ToBin → To · String · Left
  • Quantity → Quantity · Number · Right

24. Link column: open the order

A Link column opens a screen. Its mask pickTicketDetails/@{Id} puts the row's Id column into the address; the Id need not be shown.

SELECT TOP 100 pt.Id AS [Id],
       pt.PickTicketNumber AS [Order],
       ISNULL(c.CompanyName, '(no customer)') AS [Customer],
       pt.PickTicketState AS [State],
       pt.RequiredDate AS [Required],
       DATEDIFF(day, pt.RequiredDate, GETDATE()) AS [DaysLate]
FROM PickTickets pt
LEFT JOIN Customers c ON c.Id = pt.CustomerId
WHERE pt.ShippedDate IS NULL
  AND pt.CancelDate IS NULL
  AND pt.RequiredDate < CAST(GETDATE() AS date)
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
ORDER BY [DaysLate] DESC

Presentations

PresentationTypeSettings
Late ordersGridPage size: 20
  • Order → Order · Link · Left · Mask pickTicketDetails/@{Id}
  • Customer → Customer · String · Left
  • State → State · String · Left
  • Required → Required · Date · Left
  • DaysLate → Days late · Number · Right

25. Link columns: product and bin

Two links on one row: the SKU opens the product (productDetails/@{ProductId}) and the bin opens the bin (binDetails/@{BinId}).

SELECT TOP 200 p.Id AS [ProductId],
       p.Sku AS [Sku],
       p.Description AS [Description],
       b.Id AS [BinId],
       b.BinCode AS [Bin],
       il.Quantity AS [Quantity]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
JOIN Bins b ON b.Id = il.BinId
WHERE il.Quantity > 0
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
ORDER BY p.Sku, b.BinCode

Presentations

PresentationTypeSettings
Stock by binGridPage size: 20
  • Sku → SKU · Link · Left · Mask productDetails/@{ProductId}
  • Description → Description · String · Left
  • Bin → Bin · Link · Left · Mask binDetails/@{BinId}
  • Quantity → Quantity · Number · Right

26. Search, filters and Excel export

Allow export data gives the grid its toolbar: search, filters, columns and Excel. Allow Filtering adds a filter per column; Page size sets rows per page.

SELECT p.Sku AS [Sku],
       p.Description AS [Description],
       ISNULL(p.Category, '') AS [Category],
       ISNULL(cl.Name, '(no client)') AS [Client],
       CAST(SUM(il.Quantity) AS decimal(18,2)) AS [OnHand],
       COUNT(DISTINCT il.BinId) AS [Bins]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
LEFT JOIN Clients cl ON cl.Id = p.ClientId
WHERE il.Quantity > 0
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY p.Sku, p.Description, p.Category, cl.Name
ORDER BY p.Sku

Presentations

PresentationTypeSettings
InventoryGridPage size: 50 · Allow export data
  • Sku → SKU · String · Left · Allow Filtering
  • Description → Description · String · Left
  • Category → Category · String · Left · Allow Filtering
  • Client → Client · String · Left · Allow Filtering
  • OnHand → On hand · Number · Right
  • Bins → Bins · Number · Right

27. Ranked list (ROW_NUMBER)

ROW_NUMBER() OVER (ORDER BY ...) numbers the rows: an operator leaderboard for the last 7 days.

SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS [Rank],
       ISNULL(a.Username, '(System)') AS [Operator],
       COUNT(*) AS [Transactions],
       CAST(SUM(ABS(ISNULL(a.Quantity, 0))) AS bigint) AS [Units]
FROM AuditRecords a
WHERE a.Timestamp >= DATEADD(day, -7, GETDATE())
  AND a.Type <> 'UserAction'
  AND (@ClientId IS NULL OR a.ClientId = @ClientId)
GROUP BY ISNULL(a.Username, '(System)')
ORDER BY [Rank]

Presentations

PresentationTypeSettings
LeaderboardGridPage size: 20
  • Rank → # · Number · Center
  • Operator → Operator · String · Left
  • Transactions → Transactions · Number · Right
  • Units → Units · Number · Right

Tiles

28. One tile per Warehouse

A Tile presentation draws one card per row, which suits a short list. Each column becomes a line on the card.

SELECT w.WarehouseCode AS [Warehouse],
       COUNT(DISTINCT il.ProductId) AS [Skus],
       CAST(SUM(il.Quantity) AS bigint) AS [Units]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
LEFT JOIN Bins b ON b.Id = il.BinId
LEFT JOIN Zones z ON z.Id = b.ZoneId
LEFT JOIN LicensePlates lp ON lp.Id = il.LicensePlateId
JOIN Warehouses w ON w.Id = ISNULL(z.WarehouseId, lp.WarehouseId)
WHERE il.Quantity > 0
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY w.WarehouseCode
ORDER BY w.WarehouseCode

Presentations

PresentationTypeSettings
WarehousesTileTile size: 220 × 110 px
  • Warehouse → Warehouse · String · Left · Bold
  • Skus → SKUs · Number · Right
  • Units → Units · Number · Right

29. Traffic-light tiles: late orders

Background Color Column names a query column that holds a colour. The CASE picks green, amber or red for each Warehouse, so each tile colours itself.

SELECT x.Warehouse AS [Warehouse],
       x.OpenOrders AS [OpenOrders],
       x.Late AS [Late],
       CASE WHEN x.Late = 0 THEN '#dff6dd'
            WHEN x.Late <= 5 THEN '#fff4ce'
            ELSE '#fde7e9' END AS [Color]
FROM (SELECT w.WarehouseCode AS Warehouse,
             COUNT(*) AS OpenOrders,
             SUM(CASE WHEN pt.RequiredDate < CAST(GETDATE() AS date) THEN 1 ELSE 0 END) AS Late
      FROM PickTickets pt
      JOIN Warehouses w ON w.Id = pt.WarehouseId
      WHERE pt.ShippedDate IS NULL
        AND pt.CancelDate IS NULL
        AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
      GROUP BY w.WarehouseCode) x
ORDER BY x.Late DESC

Presentations

PresentationTypeSettings
Late ordersTileTile size: 220 × 110 px · Background Color Column: Color
  • Warehouse → Warehouse · String · Left · Text Color #242424 · Bold
  • OpenOrders → Open orders · Number · Right · Text Color #242424
  • Late → Late · Number · Right · Text Color #242424 · Bold

30. Zone fullness tiles

How full each zone is, coloured red from 90%, amber from 70%, green below. Warehouse-wide (no client filter): keep it on staff Dashboards.

SELECT TOP 12 x.Zone AS [Zone],
       x.Bins AS [Bins],
       x.Occupied AS [Occupied],
       x.OccupancyPercent AS [OccupancyPercent],
       CASE WHEN x.OccupancyPercent >= 90 THEN '#fde7e9'
            WHEN x.OccupancyPercent >= 70 THEN '#fff4ce'
            ELSE '#dff6dd' END AS [Color]
FROM (SELECT w.WarehouseCode + ' / ' + z.ZoneCode AS Zone,
             COUNT(DISTINCT b.Id) AS Bins,
             COUNT(DISTINCT CASE WHEN il.Quantity > 0 THEN b.Id END) AS Occupied,
             CAST(100.0 * COUNT(DISTINCT CASE WHEN il.Quantity > 0 THEN b.Id END) / NULLIF(COUNT(DISTINCT b.Id), 0) AS decimal(5,1)) AS OccupancyPercent
      FROM Bins b
      JOIN Zones z ON z.Id = b.ZoneId
      JOIN Warehouses w ON w.Id = z.WarehouseId
      LEFT JOIN InventoryLocations il ON il.BinId = b.Id
      GROUP BY w.WarehouseCode, z.ZoneCode) x
ORDER BY x.OccupancyPercent DESC

Presentations

PresentationTypeSettings
ZonesTileTile size: 220 × 130 px · Background Color Column: Color
  • Zone → Zone · String · Left · Text Color #242424 · Bold
  • Bins → Bins · Number · Right · Text Color #242424
  • Occupied → Occupied · Number · Right · Text Color #242424
  • OccupancyPercent → Full · Percentage · Right · Text Color #242424 · Bold

31. Coloured lines on a tile

Each line of a tile can have its own text colour, background and bold, set per column and the same on every tile.

SELECT TOP 8 ISNULL(cl.Name, '(no client)') AS [Client],
       COUNT(*) AS [OpenOrders],
       SUM(CASE WHEN pt.RequiredDate < CAST(GETDATE() AS date) THEN 1 ELSE 0 END) AS [Late],
       CAST(SUM(pt.TotalQuantity) AS bigint) AS [Units]
FROM PickTickets pt
LEFT JOIN Clients cl ON cl.Id = pt.ClientId
WHERE pt.ShippedDate IS NULL
  AND pt.CancelDate IS NULL
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY ISNULL(cl.Name, '(no client)')
ORDER BY COUNT(*) DESC

Presentations

PresentationTypeSettings
ClientsTileTile size: 240 × 130 px
  • Client → Client · String · Left · Bold
  • OpenOrders → Open orders · Number · Right
  • Late → Late · Number · Right · Text Color #b10e1c · Bold
  • Units → Units · Number · Right · Text Color #0f548c · Background Color #ebf3fc

32. Today at a glance

One row with several numbers makes one tile with several lines. Each number is its own small SELECT in brackets.

SELECT (SELECT COUNT(*) FROM PickTickets pt
        WHERE pt.ShippedDate >= CAST(GETDATE() AS date)
          AND (@ClientId IS NULL OR pt.ClientId = @ClientId)) AS [ShippedToday],
       (SELECT COUNT(*) FROM PurchaseOrders po
        WHERE po.ReceivingCompleted >= CAST(GETDATE() AS date)
          AND (@ClientId IS NULL OR po.ClientId = @ClientId)) AS [ReceivedToday],
       (SELECT COUNT(*) FROM PickTickets pt
        WHERE pt.ShippedDate IS NULL AND pt.CancelDate IS NULL
          AND pt.RequiredDate < CAST(GETDATE() AS date)
          AND (@ClientId IS NULL OR pt.ClientId = @ClientId)) AS [LateOrders],
       (SELECT COUNT(*) FROM AuditRecords a
        WHERE a.Timestamp >= CAST(GETDATE() AS date) AND a.Type <> 'UserAction'
          AND (@ClientId IS NULL OR a.ClientId = @ClientId)) AS [MovementsToday]

Presentations

PresentationTypeSettings
TodayTileTile size: 260 × 150 px
  • ShippedToday → Orders shipped · Number · Right
  • ReceivedToday → POs received · Number · Right
  • LateOrders → Late orders · Number · Right · Bold
  • MovementsToday → Movements · Number · Right

Numbers and gauges

33. Single number: units on hand

A Scalar shows the first column of the first row exactly as returned, so round it in the query: CAST(... AS bigint).

SELECT CAST(ISNULL(SUM(il.Quantity), 0) AS bigint) AS [UnitsOnHand]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
WHERE il.Quantity > 0
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)

Presentations

PresentationTypeSettings
Units on handScalarShows the first column of the first row, in large type.

34. Dial gauge: on-time shipping

A Gauge reads the first column of the first row. Its ranges colour the scale: red up to 85, amber to 95, green to 100.

SELECT ISNULL(CAST(100.0 * SUM(CASE WHEN pt.ShippedDate <= pt.RequiredDate THEN 1 ELSE 0 END)
            / NULLIF(COUNT(*), 0) AS decimal(5,1)), 0) AS [OnTimePercent]
FROM PickTickets pt
WHERE pt.ShippedDate >= DATEADD(day, -90, GETDATE())
  AND pt.RequiredDate IS NOT NULL
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)

Presentations

PresentationTypeSettings
On-time %GaugeGauge type: Circular · Scale: 0 – 100 · Tick Interval: 20
Ranges:
0 – 85 #d9534f
85 – 95 #f0ad4e
95 – 100 #5cb85c
NumberScalarShows the first column of the first row, in large type.

35. Dial gauge: how full the Warehouse is

Ranges can run the other way: green while there is room, red when nearly every bin holds stock. Warehouse-wide.

SELECT ISNULL(CAST(100.0 * COUNT(DISTINCT CASE WHEN il.Quantity > 0 THEN b.Id END)
            / NULLIF(COUNT(DISTINCT b.Id), 0) AS decimal(5,1)), 0) AS [OccupancyPercent]
FROM Bins b
LEFT JOIN InventoryLocations il ON il.BinId = b.Id

Presentations

PresentationTypeSettings
Bins in use %GaugeGauge type: Circular · Scale: 0 – 100 · Tick Interval: 10
Ranges:
0 – 70 #5cb85c
70 – 90 #f0ad4e
90 – 100 #d9534f

36. Horizontal bar gauge: order fill rate

A Linear gauge drawn as a horizontal bar: units shipped against units ordered, for orders shipped in the last 90 days.

SELECT ISNULL(CAST(100.0 * SUM(ISNULL(l.ShippedQuantity, 0))
            / NULLIF(SUM(ISNULL(l.OrderedQuantity, 0)), 0) AS decimal(5,1)), 0) AS [FillRatePercent]
FROM PickTicketLines l
JOIN PickTickets pt ON pt.Id = l.PickTicketId
WHERE pt.ShippedDate >= DATEADD(day, -90, GETDATE())
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)

Presentations

PresentationTypeSettings
Fill rate %GaugeGauge type: Linear · Orientation: Horizontal · Scale: 0 – 100 · Tick Interval: 10
Ranges:
0 – 90 #d9534f
90 – 98 #f0ad4e
98 – 100 #5cb85c

37. Upright bar gauge: picking progress

The same Linear gauge standing up (Vertical): the share of ordered units already picked on open orders released to the floor.

SELECT ISNULL(CAST(100.0 * SUM(ISNULL(l.PickedQuantity, 0))
            / NULLIF(SUM(ISNULL(l.OrderedQuantity, 0)), 0) AS decimal(5,1)), 0) AS [PickedPercent]
FROM PickTicketLines l
JOIN PickTickets pt ON pt.Id = l.PickTicketId
WHERE pt.ShippedDate IS NULL
  AND pt.CancelDate IS NULL
  AND pt.ReleasedToFloorDate IS NOT NULL
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)

Presentations

PresentationTypeSettings
Picked %GaugeGauge type: Linear · Orientation: Vertical · Scale: 0 – 100 · Tick Interval: 25
Ranges:
0 – 50 #d9534f
50 – 80 #f0ad4e
80 – 100 #5cb85c

38. Gauge on a count, not a percentage

The scale need not run to 100. Start value, End value and Tick Interval fit it to the number: blocked stock locations against a limit of 50.

SELECT COUNT(*) AS [BlockedLocations]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
WHERE il.BlockedDate IS NOT NULL
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)

Presentations

PresentationTypeSettings
BlockedGaugeGauge type: Circular · Scale: 0 – 50 · Tick Interval: 10
Ranges:
0 – 5 #5cb85c
5 – 20 #f0ad4e
20 – 50 #d9534f
NumberScalarShows the first column of the first row, in large type.

Filters and parameters

39. @ClientId: one widget for staff and Clients

For a client user @ClientId is their client; for Warehouse staff it is empty. (@ClientId IS NULL OR p.ClientId = @ClientId) is right for both.

SELECT ISNULL(cl.Name, '(no client)') AS [Client],
       COUNT(DISTINCT il.ProductId) AS [Skus],
       CAST(SUM(il.Quantity) AS bigint) AS [Units]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
LEFT JOIN Clients cl ON cl.Id = p.ClientId
WHERE il.Quantity > 0
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY ISNULL(cl.Name, '(no client)')
ORDER BY SUM(il.Quantity) DESC

Presentations

PresentationTypeSettings
Units by clientChartChart type: Bar
Argument field: Client
Value field: Units
TableGridPage size: 20
  • Client → Client · String · Left
  • Skus → SKUs · Number · Right
  • Units → Units · Number · Right

40. @UtcNow: who worked in the last 7 days

@UtcNow is the current date and time in UTC, supplied whenever the widget runs. DATEADD(day, -7, @UtcNow) is a week ago; DATEADD(hour, -1, @UtcNow) would be an hour ago.

SELECT ISNULL(a.Username, '(System)') AS [Operator],
       COUNT(*) AS [Transactions]
FROM AuditRecords a
WHERE a.Timestamp >= DATEADD(day, -7, @UtcNow)
  AND a.Type <> 'UserAction'
  AND (@ClientId IS NULL OR a.ClientId = @ClientId)
GROUP BY ISNULL(a.Username, '(System)')
ORDER BY COUNT(*) DESC

Presentations

PresentationTypeSettings
Last 7 daysChartChart type: Bar
Argument field: Operator
Value field: Transactions
TableGridPage size: 20
  • Operator → Operator · String · Left
  • Transactions → Transactions · Number · Right

41. Text filter: one Warehouse

@{Warehouse} becomes a text box on each dashboard panel. Read with NULLIF as here, every Warehouse shows until a code is typed, and Execute still runs.

SELECT w.WarehouseCode AS [Warehouse],
       z.ZoneCode AS [Zone],
       CAST(SUM(il.Quantity) AS bigint) AS [Units]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
JOIN Bins b ON b.Id = il.BinId
JOIN Zones z ON z.Id = b.ZoneId
JOIN Warehouses w ON w.Id = z.WarehouseId
CROSS JOIN (SELECT NULLIF(NULLIF('@{Warehouse}', ''), '@' + '{Warehouse}') AS WarehouseCode) f
WHERE il.Quantity > 0
  AND (f.WarehouseCode IS NULL OR w.WarehouseCode = f.WarehouseCode)
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY w.WarehouseCode, z.ZoneCode
ORDER BY w.WarehouseCode, z.ZoneCode

Presentations

PresentationTypeSettings
Units by zoneChartChart type: Column
Argument field: Zone
Value field: Units
Series field: Warehouse
TableGridPage size: 20
  • Warehouse → Warehouse · String · Left
  • Zone → Zone · String · Left
  • Units → Units · Number · Right

42. Number filter: the last N days

@{Days:number} becomes a number box. ISNULL(TRY_CAST(NULLIF('@{Days:number}', '') AS int), 30) reads it, and uses 30 until a number is entered.

SELECT CONVERT(char(10), pt.ShippedDate, 126) AS [Day],
       COUNT(*) AS [Orders]
FROM PickTickets pt
CROSS JOIN (SELECT ISNULL(TRY_CAST(NULLIF('@{Days:number}', '') AS int), 30) AS Days) f
WHERE pt.ShippedDate >= DATEADD(day, -f.Days, GETDATE())
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY CONVERT(char(10), pt.ShippedDate, 126)
ORDER BY [Day]

Presentations

PresentationTypeSettings
Orders shippedChartChart type: Column
Argument field: Day
Value field: Orders

43. Date filters: between two dates

@{From:date} and @{To:date} become date pickers. TRY_CAST(... AS date) reads them; with no dates the query shows the last 30 days.

SELECT po.PurchaseOrderNumber AS [PO],
       ISNULL(v.CompanyName, '(no vendor)') AS [Vendor],
       po.ReceivingCompleted AS [Received],
       po.TotalQuantity AS [Units]
FROM PurchaseOrders po
LEFT JOIN Vendors v ON v.Id = po.VendorId
CROSS JOIN (SELECT ISNULL(TRY_CAST(NULLIF('@{From:date}', '') AS date), CAST(DATEADD(day, -30, GETDATE()) AS date)) AS FromDate,
                   ISNULL(TRY_CAST(NULLIF('@{To:date}', '') AS date), CAST(GETDATE() AS date)) AS ToDate) f
WHERE po.ReceivingCompleted >= f.FromDate
  AND po.ReceivingCompleted < DATEADD(day, 1, f.ToDate)
  AND (@ClientId IS NULL OR po.ClientId = @ClientId)
ORDER BY po.ReceivingCompleted DESC

Presentations

PresentationTypeSettings
ReceiptsGridPage size: 20
  • PO → PO · String · Left
  • Vendor → Vendor · String · Left
  • Received → Received · Date · Left
  • Units → Units · Number · Right

44. Two filters together: Warehouse and days

Filters combine: the Warehouse box and the number of days, each with its own default, in one CROSS JOIN.

SELECT CONVERT(char(10), pt.ShippedDate, 126) AS [Day],
       w.WarehouseCode AS [Warehouse],
       COUNT(*) AS [Orders]
FROM PickTickets pt
JOIN Warehouses w ON w.Id = pt.WarehouseId
CROSS JOIN (SELECT NULLIF(NULLIF('@{Warehouse}', ''), '@' + '{Warehouse}') AS WarehouseCode,
                   ISNULL(TRY_CAST(NULLIF('@{Days:number}', '') AS int), 30) AS Days) f
WHERE pt.ShippedDate >= DATEADD(day, -f.Days, GETDATE())
  AND (f.WarehouseCode IS NULL OR w.WarehouseCode = f.WarehouseCode)
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY CONVERT(char(10), pt.ShippedDate, 126), w.WarehouseCode
ORDER BY [Day], [Warehouse]

Presentations

PresentationTypeSettings
Orders shippedChartChart type: Line
Argument field: Day
Value field: Orders
Series field: Warehouse

45. Search filter: SKU contains

LIKE '%' + ... + '%' finds part of a value: type part of a SKU in the panel's @{Sku} box to narrow the list.

SELECT TOP 200 p.Sku AS [Sku],
       p.Description AS [Description],
       CAST(ISNULL(SUM(il.Quantity), 0) AS decimal(18,2)) AS [OnHand]
FROM Products p
LEFT JOIN InventoryLocations il ON il.ProductId = p.Id AND il.Quantity > 0
CROSS JOIN (SELECT NULLIF(NULLIF('@{Sku}', ''), '@' + '{Sku}') AS Search) f
WHERE (f.Search IS NULL OR p.Sku LIKE '%' + f.Search + '%')
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY p.Sku, p.Description
ORDER BY p.Sku

Presentations

PresentationTypeSettings
ProductsGridPage size: 20
  • Sku → SKU · String · Left
  • Description → Description · String · Left
  • OnHand → On hand · Number · Right

Inventory

46. Stock by product category

Units and SKUs per category. NULLIF(p.Category, '') treats a blank category like a missing one, so both land in "(no category)".

SELECT ISNULL(NULLIF(p.Category, ''), '(no category)') AS [Category],
       COUNT(DISTINCT p.Id) AS [Skus],
       CAST(SUM(il.Quantity) AS bigint) AS [Units]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
WHERE il.Quantity > 0
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY ISNULL(NULLIF(p.Category, ''), '(no category)')
ORDER BY SUM(il.Quantity) DESC

Presentations

PresentationTypeSettings
By categoryChartChart type: Bar
Argument field: Category
Value field: Units
TableGridPage size: 20
  • Category → Category · String · Left
  • Skus → SKUs · Number · Right
  • Units → Units · Number · Right

47. Lots expiring in the next 60 days

Lot and expiry live on InventoryLocationDetails. Already expired lots come first (negative days left), and the SKU opens the product.

SELECT p.Id AS [ProductId],
       p.Sku AS [Sku],
       d.LotNumber AS [Lot],
       d.ExpiryDate AS [Expires],
       DATEDIFF(day, GETDATE(), d.ExpiryDate) AS [DaysLeft],
       CAST(SUM(d.Quantity) AS decimal(18,2)) AS [Quantity]
FROM InventoryLocationDetails d
JOIN InventoryLocations il ON il.Id = d.InventoryLocationId
JOIN Products p ON p.Id = il.ProductId
WHERE d.ExpiryDate IS NOT NULL
  AND d.Quantity > 0
  AND d.ExpiryDate < DATEADD(day, 60, GETDATE())
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY p.Id, p.Sku, d.LotNumber, d.ExpiryDate
ORDER BY d.ExpiryDate

Presentations

PresentationTypeSettings
Expiring lotsGridPage size: 20
  • Sku → SKU · Link · Left · Mask productDetails/@{ProductId}
  • Lot → Lot · String · Left
  • Expires → Expires · Date · Left
  • DaysLeft → Days left · Number · Right
  • Quantity → Quantity · Number · Right

48. Stock value by client

Quantity times the product's unit cost, per client. A product with no cost counts as 0 rather than hiding the client.

SELECT ISNULL(cl.Name, '(no client)') AS [Client],
       CAST(SUM(il.Quantity * ISNULL(p.UnitCost, 0)) AS decimal(18,2)) AS [StockValue],
       CAST(SUM(il.Quantity) AS bigint) AS [Units]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
LEFT JOIN Clients cl ON cl.Id = p.ClientId
WHERE il.Quantity > 0
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY ISNULL(cl.Name, '(no client)')
ORDER BY [StockValue] DESC

Presentations

PresentationTypeSettings
Value by clientChartChart type: Bar
Argument field: Client
Value field: StockValue
TableGridPage size: 20
  • Client → Client · String · Left
  • StockValue → Stock value · Currency · Right
  • Units → Units · Number · Right

49. Pallets by age

Licence plates holding stock, bucketed by days since received. The number in front of each bucket keeps them in order.

SELECT x.AgeBucket AS [Age],
       COUNT(*) AS [Pallets]
FROM (SELECT CASE WHEN DATEDIFF(day, ISNULL(lp.ReceivedDate, lp.DateCreated), GETDATE()) <= 30 THEN '1 - 0 to 30 days'
                  WHEN DATEDIFF(day, ISNULL(lp.ReceivedDate, lp.DateCreated), GETDATE()) <= 90 THEN '2 - 31 to 90 days'
                  WHEN DATEDIFF(day, ISNULL(lp.ReceivedDate, lp.DateCreated), GETDATE()) <= 180 THEN '3 - 91 to 180 days'
                  ELSE '4 - over 180 days' END AS AgeBucket
      FROM LicensePlates lp
      WHERE EXISTS (SELECT 1 FROM InventoryLocations il WHERE il.LicensePlateId = lp.Id AND il.Quantity > 0)
        AND (@ClientId IS NULL OR lp.ClientId = @ClientId)) x
GROUP BY x.AgeBucket
ORDER BY x.AgeBucket

Presentations

PresentationTypeSettings
Pallet ageChartChart type: Column
Argument field: Age
Value field: Pallets
TableGridPage size: 20
  • Age → Age · String · Left
  • Pallets → Pallets · Number · Right

50. Products spread over many Bins (HAVING)

HAVING filters after Grouping: only Products in more than three Bins, candidates for consolidation.

SELECT TOP 100 p.Sku AS [Sku],
       p.Description AS [Description],
       COUNT(DISTINCT il.BinId) AS [Bins],
       CAST(SUM(il.Quantity) AS decimal(18,2)) AS [Quantity]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
WHERE il.Quantity > 0
  AND il.BinId IS NOT NULL
  AND (@ClientId IS NULL OR p.ClientId = @ClientId)
GROUP BY p.Sku, p.Description
HAVING COUNT(DISTINCT il.BinId) > 3
ORDER BY COUNT(DISTINCT il.BinId) DESC

Presentations

PresentationTypeSettings
Spread ProductsGridPage size: 20
  • Sku → SKU · String · Left
  • Description → Description · String · Left
  • Bins → Bins · Number · Right
  • Quantity → Quantity · Number · Right

Inbound

51. Purchase orders due within 7 days or overdue

Purchase orders not yet received whose required date is within a week or already past. The PO number opens the order.

SELECT po.Id AS [Id],
       po.PurchaseOrderNumber AS [PO],
       ISNULL(v.CompanyName, '(no vendor)') AS [Vendor],
       po.PurchaseOrderState AS [State],
       po.RequiredDate AS [Required],
       po.TotalQuantity AS [Units]
FROM PurchaseOrders po
LEFT JOIN Vendors v ON v.Id = po.VendorId
WHERE po.PurchaseOrderState IN ('NotReceived', 'PartiallyReceived')
  AND po.RequiredDate < DATEADD(day, 7, CAST(GETDATE() AS date))
  AND (@ClientId IS NULL OR po.ClientId = @ClientId)
ORDER BY po.RequiredDate

Presentations

PresentationTypeSettings
Due Purchase ordersGridPage size: 20
  • PO → PO · Link · Left · Mask purchaseOrderDetails/@{Id}
  • Vendor → Vendor · String · Left
  • State → State · String · Left
  • Required → Required · Date · Left
  • Units → Units · Number · Right

52. Top Vendors by units received

Units received per vendor in the last 90 days, from the purchase order lines. TOP 8 keeps the pie readable.

SELECT TOP 8 ISNULL(v.CompanyName, '(no vendor)') AS [Vendor],
       CAST(SUM(ISNULL(l.ReceivedQuantity, 0)) AS bigint) AS [Units]
FROM PurchaseOrders po
JOIN PurchaseOrderLines l ON l.PurchaseOrderId = po.Id
LEFT JOIN Vendors v ON v.Id = po.VendorId
WHERE po.ReceivingCompleted >= DATEADD(day, -90, GETDATE())
  AND (@ClientId IS NULL OR po.ClientId = @ClientId)
GROUP BY ISNULL(v.CompanyName, '(no vendor)')
ORDER BY SUM(ISNULL(l.ReceivedQuantity, 0)) DESC

Presentations

PresentationTypeSettings
VendorsChartChart type: Pie
Argument field: Vendor
Value field: Units
TableGridPage size: 20
  • Vendor → Vendor · String · Left
  • Units → Units · Number · Right

53. Hours to receive, per week

Average hours from Receiving started to completed, per week. Orders whose times are out of order are left out.

SELECT CONVERT(char(10), DATEADD(day, DATEDIFF(day, '19000101', CAST(po.ReceivingCompleted AS date)) / 7 * 7, CAST('19000101' AS date)), 126) AS [WeekStarting],
       COUNT(*) AS [Receipts],
       CAST(AVG(DATEDIFF(minute, po.ReceivingStarted, po.ReceivingCompleted) / 60.0) AS decimal(10,1)) AS [AvgHours]
FROM PurchaseOrders po
WHERE po.ReceivingStarted IS NOT NULL
  AND po.ReceivingCompleted >= DATEADD(week, -12, GETDATE())
  AND po.ReceivingCompleted >= po.ReceivingStarted
  AND (@ClientId IS NULL OR po.ClientId = @ClientId)
GROUP BY DATEADD(day, DATEDIFF(day, '19000101', CAST(po.ReceivingCompleted AS date)) / 7 * 7, CAST('19000101' AS date))
ORDER BY [WeekStarting]

Presentations

PresentationTypeSettings
Hours to receiveChartChart type: Line
Argument field: WeekStarting
Value field: AvgHours
TableGridPage size: 20
  • WeekStarting → Week starting · String · Left
  • Receipts → Receipts · Number · Right
  • AvgHours → Avg hours · Number · Right

Outbound

54. Late orders by customer

Open orders past their required date, per customer, with how late the oldest one is.

SELECT TOP 10 ISNULL(c.CompanyName, '(no customer)') AS [Customer],
       COUNT(*) AS [LateOrders],
       MAX(DATEDIFF(day, pt.RequiredDate, GETDATE())) AS [OldestDaysLate]
FROM PickTickets pt
LEFT JOIN Customers c ON c.Id = pt.CustomerId
WHERE pt.ShippedDate IS NULL
  AND pt.CancelDate IS NULL
  AND pt.RequiredDate < CAST(GETDATE() AS date)
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY ISNULL(c.CompanyName, '(no customer)')
ORDER BY COUNT(*) DESC

Presentations

PresentationTypeSettings
Late by customerChartChart type: Bar
Argument field: Customer
Value field: LateOrders
TableGridPage size: 20
  • Customer → Customer · String · Left
  • LateOrders → Late orders · Number · Right
  • OldestDaysLate → Oldest (days) · Number · Right

55. Orders by destination

Orders shipped in the last 90 days per ship-to state or province, the top 15.

SELECT TOP 15 ISNULL(NULLIF(pt.ShipToStateProvince, ''), '(none)') AS [Destination],
       COUNT(*) AS [Orders]
FROM PickTickets pt
WHERE pt.ShippedDate >= DATEADD(day, -90, GETDATE())
  AND (@ClientId IS NULL OR pt.ClientId = @ClientId)
GROUP BY ISNULL(NULLIF(pt.ShipToStateProvince, ''), '(none)')
ORDER BY COUNT(*) DESC

Presentations

PresentationTypeSettings
DestinationsChartChart type: Bar
Argument field: Destination
Value field: Orders
TableGridPage size: 20
  • Destination → Destination · String · Left
  • Orders → Orders · Number · Right

Labour and quality

56. Busiest hours of the day (7 days)

Movements of the last 7 days by hour of the day, to see the peaks. RIGHT('0' + ..., 2) + ':00' writes 7 as 07:00 so the hours sort.

SELECT RIGHT('0' + CAST(DATEPART(hour, a.Timestamp) AS varchar(2)), 2) + ':00' AS [Hour],
       COUNT(*) AS [Transactions]
FROM AuditRecords a
WHERE a.Timestamp >= DATEADD(day, -7, GETDATE())
  AND a.Type <> 'UserAction'
  AND (@ClientId IS NULL OR a.ClientId = @ClientId)
GROUP BY DATEPART(hour, a.Timestamp)
ORDER BY DATEPART(hour, a.Timestamp)

Presentations

PresentationTypeSettings
By hourChartChart type: Column
Argument field: Hour
Value field: Transactions

57. QC hold rate per week

Outbound QC inspections per week and the share put on hold, from ToteQcInspections.

SELECT CONVERT(char(10), DATEADD(day, DATEDIFF(day, '19000101', CAST(i.StartedDate AS date)) / 7 * 7, CAST('19000101' AS date)), 126) AS [WeekStarting],
       COUNT(*) AS [Inspections],
       CAST(100.0 * SUM(CASE WHEN i.HoldDate IS NOT NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) AS decimal(5,1)) AS [HoldRatePercent]
FROM ToteQcInspections i
WHERE i.StartedDate >= DATEADD(week, -26, GETDATE())
  AND (@ClientId IS NULL OR i.ClientId = @ClientId)
GROUP BY DATEADD(day, DATEDIFF(day, '19000101', CAST(i.StartedDate AS date)) / 7 * 7, CAST('19000101' AS date))
ORDER BY [WeekStarting]

Presentations

PresentationTypeSettings
Hold rateChartChart type: Line
Argument field: WeekStarting
Value field: HoldRatePercent
TableGridPage size: 20
  • WeekStarting → Week starting · String · Left
  • Inspections → Inspections · Number · Right
  • HoldRatePercent → Hold rate · Percentage · Right

3PL Clients

58. Invoiced per client (6 months)

3PL Invoices whose period ended in the last six months, totalled per client.

SELECT ISNULL(cl.Name, '(no client)') AS [Client],
       COUNT(*) AS [Invoices],
       CAST(SUM(ISNULL(i.Total, 0)) AS decimal(18,2)) AS [Invoiced]
FROM ClientInvoices i
LEFT JOIN Clients cl ON cl.Id = i.ClientId
WHERE i.EndPeriod >= DATEADD(month, -6, GETDATE())
  AND (@ClientId IS NULL OR i.ClientId = @ClientId)
GROUP BY ISNULL(cl.Name, '(no client)')
ORDER BY [Invoiced] DESC

Presentations

PresentationTypeSettings
InvoicedChartChart type: Bar
Argument field: Client
Value field: Invoiced
TableGridPage size: 20
  • Client → Client · String · Left
  • Invoices → Invoices · Number · Right
  • Invoiced → Invoiced · Currency · Right

59. Client activity summary (WITH)

WITH names small results before the main SELECT: orders shipped, receipts and stock, each per client, joined into one row per client.

WITH Shipped AS (
    SELECT pt.ClientId, COUNT(*) AS Orders
    FROM PickTickets pt
    WHERE pt.ShippedDate >= DATEADD(day, -30, GETDATE())
    GROUP BY pt.ClientId),
Received AS (
    SELECT po.ClientId, COUNT(*) AS Receipts
    FROM PurchaseOrders po
    WHERE po.ReceivingCompleted >= DATEADD(day, -30, GETDATE())
    GROUP BY po.ClientId),
Stock AS (
    SELECT p.ClientId, SUM(il.Quantity) AS Units
    FROM InventoryLocations il
    JOIN Products p ON p.Id = il.ProductId
    WHERE il.Quantity > 0
    GROUP BY p.ClientId)
SELECT cl.Name AS [Client],
       ISNULL(s.Orders, 0) AS [OrdersShipped],
       ISNULL(r.Receipts, 0) AS [Receipts],
       CAST(ISNULL(st.Units, 0) AS bigint) AS [UnitsOnHand]
FROM Clients cl
LEFT JOIN Shipped s ON s.ClientId = cl.Id
LEFT JOIN Received r ON r.ClientId = cl.Id
LEFT JOIN Stock st ON st.ClientId = cl.Id
WHERE (@ClientId IS NULL OR cl.Id = @ClientId)
ORDER BY cl.Name

Presentations

PresentationTypeSettings
ClientsGridPage size: 20
  • Client → Client · String · Left
  • OrdersShipped → Orders shipped (30 days) · Number · Right
  • Receipts → Receipts (30 days) · Number · Right
  • UnitsOnHand → Units on hand · Number · Right

Every example passes the read-only check that guards widget queries, and was run before publishing as Warehouse staff and as a client user. A query that Returns no rows on your company is not broken: it simply found nothing in its period.

Was this page helpful?