Report works From SQL Management studio, but not from Lansweeper

[tlonde]

Hi folks,
I'm having a bit of a frustrating issue that I'm hoping somebody can help me get around. My boss is wanting me to develop a chart that shows our progress on replacing computers for the current fiscal year. The idea I came up with is having 2 data field:

• First one shows how many computers we have left to replace this FY. I have a custom field built and get this using the count(tblAssets.AssetID) command with a where statement that says my custom field must equal 20
  
• Second one shows how many computers we have replaced manually entering the starting year's number and subtracting the previously-mentioned count command (115 - count(tblAssets.AssetID))

By doing this, I can then create a pie chart that at the beginning of the year is 100% filled with the computers that need to be replaced. As those machines go away, the need to replace number ticks down while the replaced number increases. After working with some folks on Stack Overflow, I was able to further refine my code so that it would format the outputs in a chartable fashion using a CTE and a Union All. The code in question is...

WITH CTE AS (
Select Count(tblAssets.AssetID) As [Computers to be Replaced],
115 - Count(tblAssets.AssetID) As [Computers Replaced]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetCustom.Custom1 = '20' And tblAssetCustom.State = 1 And
tblAssetGroups.AssetGroup = 'Computers'
Group By tblAssetCustom.Custom1
)
SELECT 'Computers to be Replaced' AS name, [Computers to be Replaced] AS value
FROM CTE
UNION ALL
SELECT 'Computers Replaced', [Computers Replaced]
FROM CTE
ORDER BY name DESC

The code inside the CTE gives me an output of something like...

Computers to be Replaced___|_Computers Replaced
53____________________________|_ 62

...which obviously isn't usefully chartable. By putting that code into a CTE and then running the Union All command, it should give a result of:

name________________________|_Value
Computers to be Replaced___|_53
Computers Replaced_________|_62

...which can be used to create a meaningful chart.

The problem I'm now running into is that the code is good, but Lansweeper doesn't like it. I am able to take this code and successfully run a query from the SQL Studio Management Console. But when I try to run it from within Lansweeper, I get errors. When I run it as-is, I stay in the editing screen and it claims that I can't use the ORDER BY clause without specifying TOP, OFFSET, or FOR XML. So, for laughs, I add TOP 10000 to the end of the last two SELECT statements. When I then hit Save and Run, it claims that "Name" is an invalid column name. So I take the ORDER BY command out completely. This finally takes me to the report screen, but gives a red error message of "Error: Incorrect syntax near ')'." instead of any data.

Does anyone have any suggestions on this? Either an idea of what's causing the issues or how to accomplish what I'm trying to do in a way that doesn't make Lansweeper cranky? Thanks for any suggestions you might have!