Scheduled reports

A scheduled report runs a query on a schedule you set and emails the rows it returns as an Excel file. Use it for the reports people read every day, week or month without logging in: orders shipped yesterday, lots about to expire, stock on hand at month end. For a 3PL operation, one scheduled report can go to each of your clients separately, with every client receiving only its own rows.

Open Setup > Reporting > Scheduled reports. Only office users can open it; client portal users never see scheduled reports.

The list

Each row is one report, with its Name and Description. Double-click a row, or its name, to open it.

• New asks for a name and a description, creates the report, and opens it so you can write its query and set its recipients and schedule.
• Refresh reads the list again.
• Delete removes the ticked reports after you confirm. A deleted report stops sending at once and cannot be recovered.

The report screen

The report screen has two tabs, Query and Recipient, and four buttons at the top right:

The Query tab

Hourly reports run every hour and have no days or hours to set. Times are the warehouse's Eastern time.

No rows, no email

When the query returns no rows, no email is sent. That makes alert-style reports possible: "late orders", "lots expiring in 30 days" and "blocked stock" arrive only on the days there is something to act on.

The SQL editor

The query is written in LogisticaHQ's SQL editor, the same one the Query builder and Widgets use:

• Read-only. The query can only read. Anything that is not a single SELECT (UPDATE, DELETE, INSERT, EXEC and so on) is refused when you press Execute and when you save, and the report runs on a read-only database login inside a transaction that is always rolled back. Passwords, password hashes and API keys cannot be read.
• Colours and suggestions. SQL is coloured as you type. After FROM or JOIN it suggests only table names; in the SELECT list, only the columns of the tables in the query. Press Ctrl+Space to ask for suggestions.
• Tables panel. On the right, every table and its columns. Type in the search box to find one; click a name to insert it at the cursor.
• Parameters. @ClientId is the client the report is running for, or empty when no clients are chosen. @UtcNow is the current date and time in UTC.
• Execute (or Ctrl+Enter) previews the rows under the editor without sending anything. The status beside the editor shows how many rows came back and how long it took, or why the query was refused.

Ready-made reports: Examples

You rarely need to write the SQL yourself. Click Examples and pick a report; it replaces the query in the editor. Every example is written for the schedule its group names, reads well as a spreadsheet, and works for one client or for all of them.

The periods: yesterday is midnight to midnight; last 7 days ends at midnight this morning; last month is the whole previous calendar month, so a monthly report set for the 1st reads the complete month.

The reports marked last night and month end read the nightly inventory snapshot: LogisticaHQ records the stock of every bin, lot and LPN just after midnight and keeps 90 nights. The snapshot is taken only when the setting Capture a nightly inventory snapshot (90 day history), under Business > Inventory in Setup > Configuration, is switched on; without it these reports return no rows, and no email is sent.

Sending each client its own report

Choose clients in the Clients box and the report changes how it runs: at each scheduled time it runs once per chosen client, and each client's email carries only that client's rows. With no clients chosen it runs once, over every client, and one email goes out.

For this to work, the query must filter on the client with @ClientId. Every example already does, with a line like this:

AND (@ClientId IS NULL OR p.ClientId = @ClientId)

When the report runs for a client, @ClientId is that client and only its rows come back; when no clients are chosen, @ClientId is empty and the same line lets every row through.

LogisticaHQ will not let one client receive another client's data. A report with clients chosen whose query does not use @ClientId cannot be saved: Update answers This report is sent for chosen clients, so its query must filter on @ClientId. A @ClientId that only appears inside a comment does not count.

Preview for

When clients are chosen, a Preview for list appears above the editor. Pick a client and press Execute to see exactly the rows that client will receive. Nothing is sent.

The Recipient tab

Separate email addresses with a semicolon (;), for example ops@acme.com; boss@acme.com. The screen says so in red above the boxes.

Client variables

Do it in this order, as the green box at the top of the Recipient tab says: first choose the clients on the Query tab, then come to the Recipient tab and click the variables in, then press Update. Until clients are chosen, the variables are greyed out and a red line explains why, with a Choose clients button that takes you to the Query tab. Switching between the two tabs keeps everything you typed; nothing is saved until you press Update.

In a report sent per client, the To, CC, BCC, Subject and Body may use client variables. When the report goes out for a client, each variable is replaced with that client's value. With {Client.Email} in To and Weekly stock - {Client.Name} in Subject, Acme receives its report at Acme's address, titled Weekly stock - Acme.

To use one: click in the box it should go into (To, CC, BCC, Subject or Body), then click the variable. It is inserted where your cursor was. In an address box the semicolon is added for you, so clicking two variables gives {Client.Email}; {Client.PackslipToEmails}. With no box clicked, variables go into To.

Things to know:

• A variable whose field is empty on a client is replaced with nothing. If that leaves a client's To with no address at all, that client is skipped and the others still receive theirs; the skipped client is reported in the error log and, for Run, on screen.
• The variables only work on a report with clients chosen. On a report without clients, Update refuses them.
• A misspelt variable, such as {Client.Emial}, cannot be saved: Update names it and lists the correct ones. Only the variables above exist; a client's carrier and parcel-broker keys can never be put in an email.

When the email goes out

1. Every 15 minutes LogisticaHQ checks each report's next run time, counted from its Last executed.
2. When that time has come, the query runs (once per chosen client, or once for all) on the read-only login.
3. For each run that returned rows, an email goes to the recipients with the rows attached as <Report name>.xlsx (or <Report name> - <Client>.xlsx for a client's own report).
4. Last executed moves to now, whether or not anything was sent.

Email goes out through the tenant's email settings (Setup > Configuration, under Setup > Communication > Email), or through LogisticaHQ's own email service when no mail server is set there. A report that was not able to run for a while, for example after a long outage, runs once when it can and then keeps its schedule; it does not send one email for every run it missed.

Step by step: a weekly stock report for each client

1. In Setup > Reporting > Scheduled reports, press New, name it Weekly stock by SKU, and press OK.
2. On the Query tab, click Examples and choose Inventory > Stock by SKU - last night.
3. In Clients, choose the clients that should receive it.
4. Pick a client in Preview for and press Execute. Check that the rows are that client's.
5. Set Frequency to Weekly, press Add Day, choose Monday and 07:00.
6. On the Recipient tab, click in To and click {Client.Email}. In Subject, type Weekly stock - and click {Client.Name}.
7. Press Update, then Execution plan to check the next Mondays.

Troubleshooting