Here is another, better way to produce the CIO's report...
If you recall, one of the requirements was that the report summarize the total install instances of each software title. In the earlier version of this report, I was summarizing the number of titles associated with the custom tag I had assigned. For example, when I looked at Count for 'Adobe Reader (All Versions)' I saw a count of 60 - that's how many different versions of Adobe Reader I'd grouped in the 'All Versions' tag. Unfortunately, that's not what the boss wanted. He wanted a count of all computers upon which all versions of Adobe Reader is installed. How the bloody hell do I do that???
select 'SMS00001' AS CollectionID,
[Label 2] As 'Software Group',
sum([Count of Computers]) As Count,
select Soft.NormalizedName [Product Name],
TG1.TagName [Label 1],
TG2.TagName [Label 2],
TG3.TagName [Label 3],
count(Soft.ResourceID) [Count of Computers],
Soft.SoftwareID [Software ID]
join v_FullCollectionMembership ON v_R_System_Valid.ResourceID = v_FullCollectionMembership.ResourceID
where v_FullCollectionMembership.CollectionID = 'sms00001'
)MEM ON MEM.ResourceID = Soft.ResourceID
inner join v_LU_SoftwareList_Local ls ON ls.SoftwareID = Soft.SoftwareID
left join v_LU_Tags TG1 ON TG1.TagID = ls.Tag1ID
left join v_LU_Tags TG2 ON TG2.TagID = ls.Tag2ID
left join v_LU_Tags TG3 ON TG3.TagID = ls.Tag3ID
where COALESCE (TG1.TagID, TG2.TagID, TG3.TagID) IS NOT NULL
and TG2.TagName IS NOT NULL
Soft.SoftwareID, Soft.NormalizedName , TG1.TagName, TG2.TagName, TG3.TagName
group by [Label 2]
order by [Label 2]
The report displays the tag names as well as a count of the instances upon which all included titles are installed. I can drill into each tag title to reveal all the individual titles/versions and the counts thereof, and I can drill into each individual software title and reveal each computer upon which it is installed (and I can drill into the individual computers to get the details).
Note: This report relies upon the same custom column integers I mentioned in Post 4.