Report of Service Packs instead of Version Numbers

[abustraan]

I have this report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tsysOS.Image As icon
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblSoftwareUni.softwareName Like 'Autodesk Revit Architecture 2014' And
tblAssetCustom.State = 1) Or
(tblSoftwareUni.softwareName Like 'Autodesk Revit MEP 2014') Or
(tblSoftwareUni.softwareName Like 'Autodesk Revit 2014')
Order By software,
tblAssets.AssetName,
version

It runs great, but I'd like to substitute SP1, SP2, SP3 for the version numbers that are captured. Is there anyway to do that, I will I have to manually substitute those externally via Excel?

[Susan_A]

The problem is that you changed the alias of the expression from Version to [Service Pack] and are sorting the Version expression, which doesn't exist anymore. Rename Version in the last line of the report to [Service Pack]

[abustraan]

thank you for the reply, and moving my question to the right forum.

When I add the suggested line and replace the custom values, I get an error "Ambiguous column name 'version".

Any thoughts?

I just augmented your suggestion:

Case tblSoftware.softwareVersion When '13.11.00004' Then 'SP3'
When '13.07.09211' Then 'SP1' When '13.10.24211' Then 'SP2' Else 'Original'
End As [Service Pack],

I end up with another column called 'Service Pack' and it corresponds to the version column.

Thank you.

[Susan_A · Accepted solution]

Moved this from the report center to Report Requests & Questions. Please note that the report center is for posting finished reports, *not* questions.

In regards to your question: the Lansweeper report builder uses standard SQL queries. You can use a Case to replace the version numbers in the report output. See sample report below.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName As software,
Case tblSoftware.softwareVersion When '13.07.09211' Then 'Your Custom Value 1' When '13.10.24211' Then 'Your Custom Value 2' End As version,
tsysOS.Image As icon
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblSoftwareUni.softwareName Like 'Autodesk Revit Architecture 2014' And
tblAssetCustom.State = 1) Or
(tblSoftwareUni.softwareName Like 'Autodesk Revit MEP 2014' And
tblAssetCustom.State = 1) Or
(tblSoftwareUni.softwareName Like 'Autodesk Revit 2014' And
tblAssetCustom.State = 1)
Order By software,
tblAssets.AssetName,
version