Adding a Collection Parameter to a Default SCCM 2012 Report

Many of the reports that come with SCCM 2012 include a parameter for device collections, but not all of them.  This is a quick way to filter a report based on groups of devices you have already created.

Here are a couple bits of SQL you can use to add a collection parameter to a report in Report Builder.

NOTE: I strongly recommend you save a copy of the default report and modify the copy.  That way you will always have the original report to go back to if something goes wrong.

First, you will need to add a dataset to your report with the following query:

Select CollectionID as [Collection ID], Name as [Collection Name]

From v_Collection

Where CollectionType = 2

Order By Name

Next, add a parameter with the following properties.

Lastly, add the following SQL to the Where clause of the main dataset for the report.

And SYS.ResourceID in (select ResourceID from v_FullCollectionMembership_Valid where CollectionID = @CollectionID)

NOTE: The SYS prefix is used in most, but not all reports.  You may need to change this prefix for the specific report you are updating.

NOTE: The report you are modifying uses a stored procedure, you will need to incorporate this part into a new stored procedure or convert the store procedure to a query.

Then go ahead and test your report.