HaloPSA Custom SQL Report Errors (and the fix for each)

Halo wraps your query inside its own SQL before running it. Almost every confusing error comes from that.

"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries..."

Why: your query becomes a subquery, and SQL Server only allows ORDER BY in a subquery with TOP.
Fix: start with SELECT TOP 1000 (any number big enough).

"Invalid SQL Statement" with a query that runs fine elsewhere

Why 1: a trailing semicolon. Halo appends to your SQL, so ; ends it early.
Fix: delete the final ;.

Why 2: -- comments. Halo inlines the query, so everything after -- gets commented out, including Halo's own wrapper. Quotes inside the comment make it worse.
Fix: use /* block comments */ or none.

"Operand data type bit is invalid for sum operator"

Why: some columns with Total in the name are yes/no flags, not money. AChargeTotalAction on ACTIONS is the classic.
Fix: sum ActionChargeAmount for charge value.

Report runs but numbers look wrong

• Counts too high? You're including projects and opportunities. Filter REQUESTTYPE (see schema guide).
• Deleted tickets showing? Add ISNULL(FDeleted,0) = 0.
• Correlated subquery in SELECT failing? Rewrite it as a derived-table JOIN.