How to prompt Collection to a Report in SCCM 2012

 

SCCM 2012 reporting structure is lot more different than SCCM 2007. It uses SSRS, which means when we need to edit or create a report we need to use Report builder application that installs with SQL. When creating a report for general SQL query, you can type the query in report builder, save it and can run from SCCM console. On creating these kind of reports are explained in TechNet and some blogs. But I couldn’t find how to create a prompt, in example prompt for a collection or site name before running the report. So I have decided to publish my knowledge on creating these.

I will use this simple scenario

To find computers that installed Microsoft Silverlight filtered by Collection name.

This is query that I’m gonna use.

Select V_R_SYSTEM.Name0,v_Add_Remove_Programs.DisplayName0,v_Add_Remove_Programs.InstallDate0 from v_R_System left join v_Add_Remove_Programs on v_Add_Remove_Programs.ResourceID=V_R_SYSTEM.ResourceID left join v_ClientCollectionMembers on v_ClientCollectionMembers.ResourceID=V_R_SYSTEM.ResourceID where v_ClientCollectionMembers.CollectionID= @CollID and v_Add_Remove_Programs.DisplayName0=’Microsoft Silverlight’

Step 1

• Go to SCCM console – Reports – Create report

 

• After click next report will open up in Report Builder.

 

• Right click on Data sets and click Add Data set.
• Select Use dataset embedded in my report and select data source. Type the above query as follows.

 

• Click fields and click ok
• Right click on Data Sets and click Add Data Set again. Fill the details as follows and press OK.

 

SELECT
  v_Collection_Alias.CollectionID ,v_Collection_Alias.Name
FROM
fn_rbac_Collection(@UserSIDs) v_Collection_Alias
WHERE
 v_Collection_Alias.CollectionType = 2

• Right Click on report, where the empty area of report page and select properties. Go to reference tab, Click ADD on assemblies.
• Add following assemblie  –  SrsResources, culture=neutral
• And Click OK.

 

• Go to parameters and now you can see there are two parameters. CollID and UserSIDs. Right click on UserSIDs and select properties.
• Select parameter properties – Internal
• Default values, select specific value and ADD
  Value – =SrsResources.UserIdentity.GetUserSIDs(User!UserID)

 

• Click OK. Select CollID, right click and properties
• Add followings

Prompt                 – Collection
Available Values – Select get values from Query
Dataset                – Dataset2
Value Field          – CollectionID
Label Field           – Name

• Click OK

 

 

 

• Go to insert and select table
• Select dataset 1 and next
• Place the fields as follows

 

• Click next and table will generate
• Make sure that UserSIDs will first in parameters, because CollID will depend on its value. You can change its location by moving down and UP.
• Save the report and run the report from SCCM Console. You can get report like following

 

 

I have explained on how to prompt for a collection. Using same way you can adjust this report to prompt for other details, Product name, Site code ect

Hope this is helpful.