How to add Collection parameter in SCCM report

In this post we will see how to add collection parameter in SCCM report. In other words we are creating custom reports by adding parameters to the report. Report parameters enable you to control report data, connect related reports together and vary report presentation. Configuration Manager 2012 R2 comes with good number of built-in reports, but at some point there will be a need to create or modify an existing report to provide information which is not part of one of the core reports. In this post, as an example we will see the method to add collection parameter in one of the built-in reports. If you are looking to create your own report then you could do that with Report Builder.

How to add Collection parameter in SCCM report

As an example we will take a look at one of the report located under Software-Files > Computers with a specific file. This report displays a list of the computers where a specified file name appears in the software inventory, as well as information about inventoried files. We will first run this report to see how it looks. To run a report from the console select the required report, and either run the report using the Run icon, or right-click and select Run report . The report executes, and the results appear in a separate dialog. Now in the below screenshot you can see that report generates results of computers when specified file name appears in Software Inventory. It would be great if we can add the collection parameter here so that we can specify the file name and choose the collection so that results are filtered based on specified collection.

So before we modify report, lets make a copy of the original report. Edit the original report and in the report builder, click on the top left corner on the orange bubble. Click Save As.

Provide a name to the report such as Computers with a specific file with Collection. Click Save.

Now in the console, right click on the report that we just created, click on Edit.

Right click on the Datasets and click on Add Dataset. Let the Name of the dataset be default one. Click Use a dataset embedded in my report. Add the Data Source and under the query, add the line select CollectionID, Name from v_Collection Order by Name. Click OK. Just remember the dataset name as we will need in next steps.

Next we will add a new parameter to the report. Right click on Parameters and click Add Parameter. Specify a name to this parameter let’s call it “Collection“, Under the Prompt type the name as “Select a Collection”, the same name will be displayed in the reports. Select the Data type as Text, Set parameter viability to Visible.

Under Available Values, Select Dataset as “Dataset1” (this is the dataset that you just created in the previous step), set Value field as CollectionID and Label field as “Name” and click OK.

This is an important step. Right click on the Dataset0 and click Dataset Properties. You will see query at the bottom, you need to add the following lines to it.

join v_FullCollectionMembership fcm on fcm.resourceid=SYS.resourceid
where fcm.Collectionid= @Collection

If you add the above 2 lines at the end of query you might see a query properties window which means the query is incorrect. So the hint here is add the JOIN statements first and then add the WHERE statements. Click on OK.

In my case the final query was –

select SYS.Netbios_Name0, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath 
From v_GS_SoftwareFile  SF 
join fn_rbac_R_System(@UserSIDs)  SYS on SYS.ResourceID = SF.ResourceID
join v_FullCollectionMembership fcm on fcm.resourceid=SYS.ResourceID
Where SF.FileName LIKE @variable
and fcm.Collectionid= @Collection
ORDER BY SYS.Netbios_Name0

Save the report. Now when you run the report you will see the option to select a collection and by clicking on Values you can specify a collection.