Answered a question on Technet forum this morning and about a report to list all Windows 7 computers without Service Pack 1 installed.
I modified it slightly for this post to include the possibility to filter on a specific collectionID.
When creating the report create a prompt in the report called varcollection
Select SYS.Netbios_Name0, OPSYS.Caption0 as C054, OPSYS.Version0, @varcollection as Collection
from v_R_System SYS
join v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID join v_FullCollectionMembership FCM on FCM.ResourceID =SYS.ResourceID
where OPSYS.Caption0 like 'Microsoft Windows 7%' and OPSYS.Version0 <> '6.1.7601' and FCM.CollectionID = @varcollection
order by SYS.Netbios_Name0
SQL query for the varcollection prompt:
if (@__filterwildcard = '')
select distinct CollectionID, Name from v_Collection order by Name
select distinct CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name