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

[StillGoing]

I realize this is a really old thread, but I haven't found anywhere that this item has been addressed since.

I have had success adding the following in place of the CASE statement proffered by @Hemoco:

  Case tblSoftwareUni.Approved
    When 0 Then 'dot_grey.png'
    When 1 Then 'tick.png'
    When 2 Then 'red_cross.png'
  End As icon,

The icon files themselves are all located in the website images directory (e.g. c:\Program Files (x86)\Lansweeper\Website\images). I chose to use the tick and cross icons, but you could just use the 'dot' icons (i.e. dot_grey.png, dot_green.png, dot_red.png).

The field name has to be icon; I haven't found any other combination that works. The icon will always show up to the left of all the other fields, no matter where you actually place it in your query.

Hopefully someone finds this useful.

[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