Using Collection Item Pickers to Filter Reports

The Item picker is a great tool for extending the functionality of reports. More specifically the collection picker allows you to dynamically manage the scope of your reports.

In this article we'll go over how to create and use a collection picker.

Click to view.

Go ahead and click finish and then open the edit windows for the report.

Once in the Edit window we'll need to create a couple parameters, so click the New Parameter button and create a basic string parameter called AppName be sure to click the box for to Prompt User, type a friendly name and set the default value to %.

Click to view.

Once the AppName parameter is created, we will need to create one more parameter for the collection picker. Give the new parameter the name of Collection, change the parameter type to Item picker and the class filter to Collections.

Click to view.

Ok, now we need to put in our Query so in the Level Query box click the edit pencil and paste the following SQL Query into the box and click Finish.

SELECT vc.Name as 'Computer Name', arp.name as 'Application Name'
from vComputer vc        
join Inv_AeX_OS_Add_Remove_Programs arp on arp._ResourceGuid = vc.Guid       
INNER JOIN dbo.CollectionMembership cm ON vc.Guid = cm.ResourceGuid     
where cm.CollectionGuid ='%Collection%'  
AND arp.Name LIKE '%AppName%'

Now let's save the changes to the report by clicking apply and then run the report.

Click to view.

So now we have our fancy new report that can filter based on collections. You can easily add this functionality to all your reports by copying the following lines into your existing reports and adding a Collection item picker parameter.

INNER JOIN dbo.CollectionMembership cm ON vc.Guid = cm.ResourceGuid    
Where cm.CollectionGuid ='%Collection%'

Do note that I am joining the collection table to the vComputer view under the alas of vc in this example so you will need to modify the query to fit your report.
For example we wanted to add a collection picker to the following SQL Query

Select * from Inv_AeX_AC_Identification

We would need to modify the query as follows:

Select * from Inv_AeX_AC_Identification
INNER JOIN dbo.CollectionMembership cm ON Inv_AeX_AC_Identification
._ResourceGuid = cm.ResourceGuid    
Where cm.CollectionGuid ='%Collection%'  

I have attached the completed example report for your viewing pleasure.