Report from SQL to LS

[chads]

So we are trying to make a SQL query into a LS report but the LS report editor doesn't like anything we throw at it.

This is the SQL code

;with spie as
(
select
tos.Image As icon,
ta.AssetID,
ta.AssetName,
ta.Domain,
ta.Username,
ta.Userdomain,
ta.IPAddress,
ta.Firstseen,
ta.Lastseen,
ta.Lasttried,
tqfu.ServicePackInEffect,
1 as count
from tblAssets ta
inner join tblAssetCustom tac on ta.AssetID = tac.AssetID
inner join tsysOS tos on tos.OScode = ta.OScode
inner join tblQuickFixEngineering tqf on ta.AssetID = tqf.AssetID
inner join tblQuickFixEngineeringUni tqfu on tqf.QFEID = tqfu.QFEID
where tac.state = 1
)

select
*
from spie q
pivot (sum(count) for ServicePackInEffect in ([KB2443105],[KB924667-v2],[KB925902-v2],[KB926122],[KB927891],[KB929123],[KB930178],[KB931784],[KB932168],[KB933729],[KB933854],[KB935839])) p

Which looks like the image attached.

[Daniel_B · Accepted solution]

Functions like Pivot which are available on SQL server can't be processed by the ReportBuilder, as it is designed as a GUI tool to select data from existing tables and help all Lansweeper users to build custom reports according to their needs. If you would like to use more complex queries, you need to execute them from SQL server Management studio.