Unable to Add Collection Parameter to an SCCM Report.

[CripWalk]

Hi all.

I have sucessfully created an SCCM report using Report Builder. It is a report of all computers with Winamp installed. It looks like this:-

 

 

What I would like to do is add a collection parameter so that I can sort computers with Winamp installed by device collections. I tried to follow this link (https://prajwaldesai.com/how-to-add-collection-parameter-in-sccm-report/), but I encountered a SQL pop-up error when I add the two lines to the dataset properties.

 

(Lines added)

 

join v_FullCollectionMembership fcm on fcm.resourceid=SYS.resourceid

where fcm.Collectionid= @Collection

 

(SQL Pop-Up)

 

 

Below is the SQL query that I used to get the report.

 

select all SMS_R_System.Name0,__System_ADD_REMOVE_PROGRAMS0.DisplayName00,__System_ADD_REMOVE_PROGRAMS0.Publisher00,__System_ADD_REMOVE_PROGRAMS0.Version00 from vSMS_R_System AS SMS_R_System INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey INNER JOIN _RES_COLL_SMSDM003 AS SMS_CM_RES_COLL_SMSDM003 ON SMS_CM_RES_COLL_SMSDM003.MachineID = SMS_R_System.ItemKey where __System_ADD_REMOVE_PROGRAMS0.DisplayName00 = N'Winamp'

 

I admit that I am a not proficient with SQL. Can somebody help in figuring out what is it that I am missing?

 

I am using SCCM 1610 with SCCM SQL 2014.

Any help is greatly appreciated.

Thanks.

 

[GarthMJ]

First off, it is NOT supported to query the tables directly, you will need to fix your query.

Secondly, it is not recommend to access the reports from the console, instead access them from the web interface, plus it is faster from the web interface.

Thirdly, I don't see where in the query you tried to add the collection bit? Did you try to add it to your query? ; ay I see in the screenshot.. Move it up above the where section. Don't forget to fix your join statement.

 

Tip: Write your query in SQL Server Management Studio, first before using RB.

Tip: Stop using report build, instead use SQL Server data tools

Tip: There are at least two books on how to write report for CMCB, They both give you tips on SQL and on writing reports for CMCB.

[GarthMJ]

BTW, what is wrong with the build in report for this? Don't get me wrong I'm not saying that you shouldn't create a report.. (I love reporting but... why do the extra work??)

[CripWalk]

GarthMJ,

Thanks for your replies. I created a query first in CM and then used it to create the report using Report Builder. I will try your suggestions and give feedback. I am only trying to learn how to create reports just in case I need a custom one at work in the future. The only time that I created a report through SQL Management Studio is when I made the Computer and Hardware report. I guess I don't have a solid base on how and where to create them. I just search for tips online and go for it.

What books do you suggest to learn more about reports?

Thanks for your time.

[GarthMJ]

I love the first book. :-)

 

https://www.amazon.ca/System-Configuration-Manager-Reporting-Unleashed-ebook/dp/B01EJXNJXS/

 

https://www.amazon.ca/System-Center-2012-Configuration-Manager/dp/9187445174/