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 .

Let's begin by creating a new report:

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 %

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

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 lets  save the changes to the report by clicking apply and then run the report.

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%'  

And that is how it's done. I have attached the example report file for your viewing pleasure.