Edit report

General

Primary key. Read-only on edit.

Name

Code

Short code used as Excel worksheet name (max 31 chars).

Description

Active

Data source

SQL query

SELECT 
cast(I.SmartStreamInvoiceID as varchar(50)) AS ReceiptNumber,
cast(I.PaymentGatewayTransactionNumber as varchar(50)) as PaymentTransaction,
convert(varchar, I.InvoiceDate, 20) as InvoiceDate,
upper(I.PayerName) as PayerName,
upper(I.BehalfOfName) as BehalfOfName,
I.InvoiceTotal,
pl3.LedgerAlias,
II.Quantity,
II.Description,
II.Amount,
II.Total,
pl3.Site,
(SELECT SUBSTRING(
(SELECT ',' + DocumentReferenceValue.Value
FROM DocumentReferenceValue 
where DocumentReferenceValue.InvoiceItemID =II.InvoiceItemID
FOR XML PATH('')),2,200000)) AS ReferenceData
, COALESCE (I.InvoiceEmail,I.MobileNo) AS ContactDetails
FROM Invoice I
INNER JOIN InvoiceItem II ON I.InvoiceID = II.InvoiceID
INNER JOIN paymenttypelevel3 pl3 ON II.PaymentTypeLevel3ID = pl3.PaymentTypeLevel3ID
WHERE InvoiceStateID = 100 -- SUCCESS
AND pl3.LedgerAlias IN ('PREC','WPERM','MVDE','PML')
AND i.PaymentApplicationID = 4
AND i.InvoiceDate >=  dateadd(dd,-1, DATEADD(dd , DATEDIFF(dd, 0, getdate()), 0))
AND I.InvoiceDate <  DATEADD(dd , DATEDIFF(dd, 0, getdate()), 0)
ORDER BY I.InvoiceID, I.InvoiceDate

Connection string name

Must match a key in the worker's appsettings ConnectionStrings section.

Database type

PowerShell

PowerShell script (before)

Runs before the report SQL. A non-zero exit code fails the run.

PowerShell script (after)

Runs after the report email is sent. A non-zero exit code fails the run.

Schedule

Hour of day (0–23)

Missed-schedule threshold (minutes)

Days of week (leave all unchecked for every day)

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Days of month (CSV)

Output

Send as Excel attachment

Send as HTML in email body

Send empty report

Filename format

Filename date offset (days)

Email body HTML template

Use {TABLE} as a placeholder for the rendered HTML table.

Email subject

Defaults to report Name if blank.

Sender email

Recipients (CSV)

Cancel

Status (read-only)

Last attempted run: 2026-08-01 06:03:49
Last successful run: 2026-08-01 06:03:49
Failures since last success: 0
Last error message: —
Run on next poll: No