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

Image 

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

Image 

  • 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.

Image 

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

Image 

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.

Image 

  • 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)

Image 

  • 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

 

Image 

 

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

Image 

  • 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

 Image

 

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.

Advertisements

10 thoughts on “How to prompt Collection to a Report in SCCM 2012

  1. Hi Asitha,

    ist very helpful many thanks !!!
    I have created a Report for Project Pro but I don’t get the install date back. Any idea what this can be?

    THX
    Markus

    • hi Markus
      did u add this “Add_Remove_Programs.InstallDate0” to your first query ?,if so just check with a another product with same query , i think if the database is having the install date, it should return it,
      most probably your sql query may have errors.

  2. Why SrsResources.UserIdentity.GetUserSIDs(User!UserID) always return ‘disabled’

    P.S. SCCM 2012 SP1 CU2

    Alexey.

  3. System.Web.Services.Protocols.SoapException: The report parameter ‘CollID’ has a DefaultValue or a ValidValue that depends on the report parameter “UserSIDs”. Forward dependencies are not valid.
    at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)
    at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)
    at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateCatalogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)

    while i am saving the report above error is coming. please help

  4. You say:

    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.

    But where are you getting UseSIDs from? You are getting it from anywhere..

  5. Pingback: Having your reports prompt for a collection | System Center Stuff

  6. Hey asitha, I’m getting the below error. Any help would be appreciated

    Conversion failed when converting the nvarchar value ‘S-1-5-21-876548081-161828567-355810188-74430’ to data type int.
    —————————-
    Cannot read the next data row for the dataset DataSet2. (rsErrorReadingNextDataRow)
    —————————-
    An error has occurred during report processing. (rsProcessingAborted)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s