P4 Software
/
logisticaHQ-en
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.
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.(@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.CONVERT(char(10), date, 126), which gives YYYY-MM-DD: it sorts correctly and shows no time on a chart.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 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.
| Kind | Write | Gives |
|---|---|---|
| Number | ISNULL(TRY_CAST(NULLIF('@{Days:number}', '') AS int), 30) | The number typed on the panel, or 30. |
| Date | ISNULL(TRY_CAST(NULLIF('@{From:date}', '') AS date), CAST(DATEADD(day, -30, GETDATE()) AS date)) | The date picked on the panel, or 30 days ago. |
| Text | NULLIF(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.
59 examples in 11 groups:
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
| Presentation | Type | Settings |
|---|---|---|
| Open orders | Scalar | Shows the first column of the first row, in large type. |
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
| Presentation | Type | Settings |
|---|---|---|
| Newest orders | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Pie | Chart | Chart type: Pie Argument field: StateValue field: Orders |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Bar | Chart | Chart type: Bar Argument field: CustomerValue field: Orders |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Column | Chart | Chart type: Column Argument field: DayValue field: Orders |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Pie | Chart | Chart type: Pie Argument field: StatusValue field: Orders |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| On-time % | Scalar | Shows the first column of the first row, in large type. |
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
| Presentation | Type | Settings |
|---|---|---|
| Columns | Chart | Chart type: Column Argument field: WarehouseValue field: OpenOrders |
| Pie | Chart | Chart type: Pie Argument field: WarehouseValue field: OpenOrders |
| Table | Grid | Page size: 20 |
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
| Presentation | Type | Settings |
|---|---|---|
| Units received | Chart | Chart type: Column Argument field: DayValue field: Units |
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
| Presentation | Type | Settings |
|---|---|---|
| Top Products | Chart | Chart type: Bar Argument field: SkuValue field: UnitsPicked |
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
| Presentation | Type | Settings |
|---|---|---|
| Orders shipped | Chart | Chart type: Line Argument field: DayValue field: Orders |
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
| Presentation | Type | Settings |
|---|---|---|
| Activity | Chart | Chart type: Area Argument field: DayValue field: Transactions |
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
| Presentation | Type | Settings |
|---|---|---|
| Stock share | Chart | Chart type: Pie Argument field: WarehouseValue field: Units |
| Table | Grid | Page size: 20 |
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
| Presentation | Type | Settings |
|---|---|---|
| Pies | Chart | Chart type: Pie Argument field: StateValue field: OrdersSeries field: WarehouseValue format: n0 |
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
| Presentation | Type | Settings |
|---|---|---|
| Client share | Chart | Chart type: Pie Argument field: ClientValue field: ShareSeries field: WarehouseValue format: p1 |
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
| Presentation | Type | Settings |
|---|---|---|
| By Warehouse | Chart | Chart type: Line Argument field: DayValue field: OrdersSeries field: Warehouse |
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
| Presentation | Type | Settings |
|---|---|---|
| Movements | Chart | Chart type: Column Argument field: DayValue field: TransactionsSeries field: Movement |
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
| Presentation | Type | Settings |
|---|---|---|
| Orders per week | Chart | Chart type: Column Argument field: WeekStartingValue field: Orders |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Receipts | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Invoices | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Progress | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Bins to count | Grid | Page size: 20 |
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
| Presentation | Type | Settings |
|---|---|---|
| Latest movements | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Late orders | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Stock by bin | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Inventory | Grid | Page size: 50 · Allow export data |
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
| Presentation | Type | Settings |
|---|---|---|
| Leaderboard | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Warehouses | Tile | Tile size: 220 × 110 px |
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
| Presentation | Type | Settings |
|---|---|---|
| Late orders | Tile | Tile size: 220 × 110 px · Background Color Column: Color |
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
| Presentation | Type | Settings |
|---|---|---|
| Zones | Tile | Tile size: 220 × 130 px · Background Color Column: Color |
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
| Presentation | Type | Settings |
|---|---|---|
| Clients | Tile | Tile size: 240 × 130 px
|
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
| Presentation | Type | Settings |
|---|---|---|
| Today | Tile | Tile size: 260 × 150 px
|
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
| Presentation | Type | Settings |
|---|---|---|
| Units on hand | Scalar | Shows the first column of the first row, in large type. |
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
| Presentation | Type | Settings |
|---|---|---|
| On-time % | Gauge | Gauge type: Circular · Scale: 0 – 100 · Tick Interval: 20 Ranges: 0 – 85 #d9534f85 – 95 #f0ad4e95 – 100 #5cb85c |
| Number | Scalar | Shows the first column of the first row, in large type. |
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
| Presentation | Type | Settings |
|---|---|---|
| Bins in use % | Gauge | Gauge type: Circular · Scale: 0 – 100 · Tick Interval: 10 Ranges: 0 – 70 #5cb85c70 – 90 #f0ad4e90 – 100 #d9534f |
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
| Presentation | Type | Settings |
|---|---|---|
| Fill rate % | Gauge | Gauge type: Linear · Orientation: Horizontal · Scale: 0 – 100 · Tick Interval: 10 Ranges: 0 – 90 #d9534f90 – 98 #f0ad4e98 – 100 #5cb85c |
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
| Presentation | Type | Settings |
|---|---|---|
| Picked % | Gauge | Gauge type: Linear · Orientation: Vertical · Scale: 0 – 100 · Tick Interval: 25 Ranges: 0 – 50 #d9534f50 – 80 #f0ad4e80 – 100 #5cb85c |
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
| Presentation | Type | Settings |
|---|---|---|
| Blocked | Gauge | Gauge type: Circular · Scale: 0 – 50 · Tick Interval: 10 Ranges: 0 – 5 #5cb85c5 – 20 #f0ad4e20 – 50 #d9534f |
| Number | Scalar | Shows the first column of the first row, in large type. |
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
| Presentation | Type | Settings |
|---|---|---|
| Units by client | Chart | Chart type: Bar Argument field: ClientValue field: Units |
| Table | Grid | Page size: 20
|
@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
| Presentation | Type | Settings |
|---|---|---|
| Last 7 days | Chart | Chart type: Bar Argument field: OperatorValue field: Transactions |
| Table | Grid | Page size: 20
|
@{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
| Presentation | Type | Settings |
|---|---|---|
| Units by zone | Chart | Chart type: Column Argument field: ZoneValue field: UnitsSeries field: Warehouse |
| Table | Grid | Page size: 20 |
@{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
| Presentation | Type | Settings |
|---|---|---|
| Orders shipped | Chart | Chart type: Column Argument field: DayValue field: Orders |
@{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
| Presentation | Type | Settings |
|---|---|---|
| Receipts | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Orders shipped | Chart | Chart type: Line Argument field: DayValue field: OrdersSeries field: Warehouse |
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
| Presentation | Type | Settings |
|---|---|---|
| Products | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| By category | Chart | Chart type: Bar Argument field: CategoryValue field: Units |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Expiring lots | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Value by client | Chart | Chart type: Bar Argument field: ClientValue field: StockValue |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Pallet age | Chart | Chart type: Column Argument field: AgeValue field: Pallets |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Spread Products | Grid | Page size: 20 |
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
| Presentation | Type | Settings |
|---|---|---|
| Due Purchase orders | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Vendors | Chart | Chart type: Pie Argument field: VendorValue field: Units |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Hours to receive | Chart | Chart type: Line Argument field: WeekStartingValue field: AvgHours |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Late by customer | Chart | Chart type: Bar Argument field: CustomerValue field: LateOrders |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Destinations | Chart | Chart type: Bar Argument field: DestinationValue field: Orders |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| By hour | Chart | Chart type: Column Argument field: HourValue field: Transactions |
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
| Presentation | Type | Settings |
|---|---|---|
| Hold rate | Chart | Chart type: Line Argument field: WeekStartingValue field: HoldRatePercent |
| Table | Grid | Page size: 20
|
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
| Presentation | Type | Settings |
|---|---|---|
| Invoiced | Chart | Chart type: Bar Argument field: ClientValue field: Invoiced |
| Table | Grid | Page size: 20 |
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
| Presentation | Type | Settings |
|---|---|---|
| Clients | Grid | Page size: 20
|
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?