Hi,
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

Report Query:

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

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:


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