Widgets

A widget is one report: a query that reads your data, and one or more ways to show its rows (a chart, a grid, tiles, a single number, a gauge). Widgets are the building blocks of dashboards: a dashboard is a board of widgets, each in its own panel. The same widget can sit on several dashboards.

Open Setup > Reporting > Widgets. Only office users can create and change widgets.

The list

Each row is one widget, with its Name, Description and Created date. Double-click a row to open it.

To start from a complete set of ready-made widgets and a Warehouse performance dashboard instead, use Seed default reports on the Dashboards list.

The widget screen

The widget screen has a General tab, then one tab per presentation, then a + to add a presentation. Update saves the widget, the query and every presentation together; Refresh reads it again and drops unsaved changes.

General: the query

The query is written in LogisticaHQ's SQL editor, the same one as the Query builder and Scheduled reports: it can only read (a single SELECT, run on a read-only login, never passwords or API keys), colours SQL as you type, suggests tables after FROM and columns in the SELECT list (Ctrl+Space), and lists every table and column on the right. Click a name there to insert it at the cursor.

Execute (or Ctrl+Enter) runs the query and shows the rows under the editor in a Result card, with its own search, filters, columns and Excel export. The × on the card closes it. Look at the result before you set up a presentation: the presentations refer to its column names.

Parameters and filters

A filter's value is placed in the query as typed, so put text filters in quotes: WHERE w.WarehouseCode = '@{Warehouse}'; numbers and dates go without: WHERE DATEDIFF(day, pt.ShippedDate, GETDATE()) <= @{Days:number}. With the same widget on two panels, each panel keeps its own values, so one widget can show warehouse 1001 on one panel and 1002 on another.

Presentations

A presentation is one way of showing the query's rows. A widget can have several, for example a chart and a grid of the same numbers; each dashboard panel picks the one it shows. Click + and choose the kind:

Each presentation gets a tab. Its arrows move it left or right among the tabs, and its × removes it. Every presentation has a Name (required), which is what the dashboard designer offers when you pick a presentation for a panel.

Chart

Grid

Each column has:

Tile

Tiles show each row of the query as a coloured card, which suits a short list such as one card per warehouse or per zone.

Scalar

A single number in large type: the value in the first column of the first row. Write the query so it returns exactly that, for example SELECT COUNT(*) FROM PickTickets WHERE ShippedDate IS NULL. A scalar has only its Name.

Gauge

A dial or bar showing one value against a scale; like the scalar, it reads the first column of the first row.

Step by step: orders shipped per day, as a chart and a grid

1. In Setup > Reporting > Widgets, press New, name it Orders shipped per day and press OK.
2. On General, enter the query:

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

3. Press Execute and check the result has the columns Day and Orders.
4. Click + and choose Chart. Name it Chart, set Chart type to Column, Argument field to Day and Value field to Orders.
5. Click + and choose Grid. Name it Table and tick Allow export data.
6. Press Update. The widget is ready to be placed on a dashboard.

Troubleshooting