How-to add a collection selection to almost any report.

This seems to be a common question so i desired to make a quick blog post a about it.
There are three parts to this ,first the part that you add to your query:

....AND ResourceID in (select ResourceID from dbo.v_FullCollectionMembership Where CollectionID = @CollectionID)

Notice: If your query have a where clause ,then you just tack it on with the AND operator. If your query does not have a where clause you just replace the AND with a WHERE and paste it in your query.

Next you need a query to get a list of collections ,this is needed to create a dropdown list with the collection names. Without this you just get a textbox where you need to type in the collection ID ,not very user friendly.

Create a new dataset called "Collections" using the following query:

SELECT     Name, CollectionID
FROM         v_Collection where CollectionType = '2'
Order by Name asc

TIP: Prefix the collections that you want to use in your reports with something ,then you can filter the collection list by adding a "WHERE Name like '%Something%' " to the query above.

Last you need a Parameter called "CollectionID" that gets it's available values from the query you just created.

Now you have the a working CollectionParameter in your report with a nice dropdown list.

If you're new to report building ,here is an example:

Lets say you have a simple query in your report to show all "Dell" computers in your environment.

Select A.Name0,A.User_Name0,B.Manufacturer0,B.Model0
from v_R_System A
join v_GS_COMPUTER_SYSTEM B on A.ResourceID = B.ResourceID
Where B.Manufacturer0 Like '%DELL%' 

To limit this query to only return computers from a specific collections you simply add the query snippet at the top of this post to your existing query.

Select A.Name0,A.User_Name0,B.Manufacturer0,B.Model0
from v_R_System A
join v_GS_COMPUTER_SYSTEM B on A.ResourceID = B.ResourceID
Where B.Manufacturer0 Like '%DELL%' AND ResourceID in (select ResourceID from dbo.v_FullCollectionMembership Where CollectionID = @CollectionID)

Add the parameter(CollectionID) and it's dataset and your done!

Hope this helps.