Software report with icons for approval

[mwrobo09]

I created the following query to give me a software report by Asset Group location. What I was looking to see if possible is to show the red/yellow/green icon for approved software in the report, is this possible?

Select Top 1000000 tblAssetGroups.AssetGroup As Site,
tblSoftwareUni.softwareName As Software,
tblSoftwareUni.SoftwarePublisher As Publisher,
Count(tblSoftware.AssetID) As Total
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 tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetCustom.State = 1
Group By tblAssetGroups.AssetGroup,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher
Order By Site,
Total Desc

[Susan_A]

FYI for everyone: from Lansweeper 5.2 onwards, you can change the foreground color and background color of report results. See this forum topic.

[mwrobo09]

thank you

[Hemoco]

We have added this feature request to our customer wish list.

[mwrobo09]

Can this be added to the wishlist?

[Hemoco]

No, this is not currently possible unfortunately. You can't change text formatting within the report builder.

[mwrobo09]

This worked great, thanks. Just one other question, is there a way to change the text color in the report for the authorized field? For example Green for authorized, yellow for unrated, and red for unauthorized.

[Hemoco · Accepted solution]

You can't add an icon, but you can add an extra column for the software authorization status, as shown below.

Select Top 1000000 tblAssetGroups.AssetGroup As Site,
tblSoftwareUni.softwareName As Software,
tblSoftwareUni.SoftwarePublisher As Publisher,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'authorized'
When 2 Then 'unauthorized' End As [Authorization],
Count(tblSoftware.AssetID) As Total
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 tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetCustom.State = 1
Group By tblAssetGroups.AssetGroup,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'authorized'
When 2 Then 'unauthorized' End
Order By Site,
Total Desc