Some built-in reports cannot be edited due to the usage of table alias 'at'

[#1]

Administrators using Lansweeper's built-in reports may encounter issues when editing certain reports. These reports may load and function as expected regarding data output, but any attempt to open them in the report builder and saving them after editing triggers an error, making modifications impossible. This is particularly frustrating when slight adjustments or customizations are needed for auditing or compliance tasks. 

What is going on?

The core of the problem lies in the use of [at] as a table alias—commonly used to represent the tsysAssetTypes table in Lansweeper's SQL queries. Report builder behavior changed after a recent Lansweeper update, likely due to updated SQL parsing rules. The string [at] has become problematic because [at] is now recognized as a reserved keyword in Transact-SQL (T-SQL), the language underpinning the Lansweeper database engine. Reserved keywords have special meaning in SQL syntax and cannot be used as object names or aliases unless bracketed—something the report builder doesn’t tolerate well.

For a full reference, Microsoft maintains a comprehensive list of Reserved Keywords (Transact-SQL).

How can I fix this?

To resolve this issue, you’ll need to replace every instance of the alias [at] in the report's SQL with an alternative that does not conflict with SQL reserved words. A commonly accepted replacement is atype, which clearly indicates its relationship to the tsysAssetTypes table and avoids naming conflicts.

To open the report builder, simply edit a report in your Lansweeper Classic instance, and change [at] to atype (or another meaningful alias that suits your environment). Once done, you’ll find that the report builder can successfully parse and load the query without errors, restoring full editability to your built-in reports.

See the below code snippet as an example:

SELECT a.AssetID, a.AssetName, atype.AssetTypename
FROM tblAssets AS a
INNER JOIN tsysAssetTypes AS atype ON [at].AssetType = a.Assettype

Replace it as mentioned here:

SELECT a.AssetID, a.AssetName, atype.AssetTypename
FROM tblAssets AS a
INNER JOIN tsysAssetTypes AS atype ON atype.AssetType = a.Assettype