Custom Software Update Compliance Report for ConfigMgr 2007

Had a special request recently to create a custom compliance report based on the selection of a software update list and collection name.  Besides the columns shown below, other fields added to the report were security patch results, URL to the technical bulletin and collection id.
We named it 'Compliance 10 - Update list (per collection)'
It turns out that it was not a trivial undertaking, as it required a sub-query to get the results we needed. The results are shown here:



Base report query, with prompts:
-- final report SQL compliance report 10
declare @AuthListLocalID as int
select @AuthListLocalID=CI_ID from v_AuthListInfo where CI_UniqueID=@AuthListID
select CI_UniqueID as AuthorizationListID,
Title as AuthorizationListName
from v_AuthListInfo where CI_UniqueID=@AuthListID
select
m.Name0 as ComputerName0,
ui.ArticleID as ArticleID,
ui.BulletinID as BulletinID,
ui.Title as Title,
-- ucs.Status,
case ucs.Status
when 1 then 'NotRequired'
when 3 then 'Installed'
else 'Unknown'
END as Status,
ui.InfoURL,
@CollID as CollectionID
-- ui.CI_UniqueID
from v_Update_ComplianceStatusAll ucs
join v_UpdateInfo ui on ucs.CI_ID=ui.CI_ID
join v_ClientCollectionMembers cm on cm.ResourceID=ucs.ResourceID
join v_R_System m on m.ResourceType=5 and m.ResourceID=ucs.ResourceID and isnull(m.Obsolete0,0)<>1
left join v_RA_System_SMSAssignedSites asite on m.ResourceID=asite.ResourceID
where ui.CI_UniqueID IN
(
select UniqueUpdateID=ui.CI_UniqueID
from v_CIRelation cir
join v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
left join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID=@CollID
join v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID=@CollID
where cir.FromCIID=@AuthListLocalID and cir.RelationType=1
)
and cm.CollectionID=@CollID
order by m.Name0
-- End SQL query for primary report
Prompts:


begin
if (@__filterwildcard = '')
select distinct CI_UniqueID as AuthListID, Title as Title from v_AuthListInfo order by Title
else
select distinct CI_UniqueID as AuthListID, Title as Title from v_AuthListInfo
where ((CI_UniqueID like @__filterwildcard) or
(Title like @__filterwildcard))
order by Title
end

begin
if (@__filterwildcard = '')
select CollectionID as CollectionID, Name as CollectionName from v_Collection order by Name
else
select CollectionID as CollectionID, Name as CollectionName from v_Collection
WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard
order by Name
end