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, 
isnull(cast(I.PaymentGatewayTransactionNumber as varchar(50)),i.PaymentResponse) 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
AND i.PaymentApplicationID = 4  AND i.InvoiceDate >=   cast(dateadd(day,-7, getdate()) as date) and  i.InvoiceDate <   cast(getdate() as date)   ORDER BY I.InvoiceID desc , 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-07-27 06:02:56
Last successful run: 2026-07-27 06:02:56
Failures since last success: 0
Last error message: —
Run on next poll: No