cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mwrobo09
Champion Sweeper
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
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

7 REPLIES 7
Susan_A
Lansweeper Alumni
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
Champion Sweeper
thank you
Hemoco
Lansweeper Alumni
We have added this feature request to our customer wish list.
mwrobo09
Champion Sweeper
Can this be added to the wishlist?
Hemoco
Lansweeper Alumni
No, this is not currently possible unfortunately. You can't change text formatting within the report builder.
mwrobo09
Champion Sweeper
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
Lansweeper Alumni
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