How to add Collection parameter in SCCM report
In this post we will see how to add collection parameter in SCCM report. In other words we are creating custom reports by adding parameters to the report. Report parameters enable you to control report data, connect related reports together and vary report presentation. Configuration Manager 2012 R2 comes with good number of built-in reports, but at some point there will be a need to create or modify an existing report to provide information which is not part of one of the core reports. In this post, as an example we will see the method to add collection parameter in one of the built-in reports. If you are looking to create your own report then you could do that with Report Builder.
How to add Collection parameter in SCCM report
As an example we will take a look at one of the report located under Software-Files > Computers with a specific file. This report displays a list of the computers where a specified file name appears in the software inventory, as well as information about inventoried files. We will first run this report to see how it looks. To run a report from the console select the required report, and either run the report using the Run icon, or right-click and select Run report . The report executes, and the results appear in a separate dialog. Now in the below screenshot you can see that report generates results of computers when specified file name appears in Software Inventory. It would be great if we can add the collection parameter here so that we can specify the file name and choose the collection so that results are filtered based on specified collection.
So before we modify report, lets make a copy of the original report. Edit the original report and in the report builder, click on the top left corner on the orange bubble. Click Save As.
Provide a name to the report such as Computers with a specific file with Collection. Click Save.
Now in the console, right click on the report that we just created, click on Edit.
Right click on the Datasets and click on Add Dataset. Let the Name of the dataset be default one. Click Use a dataset embedded in my report. Add the Data Source and under the query, add the line select CollectionID, Name from v_Collection Order by Name. Click OK. Just remember the dataset name as we will need in next steps.
Next we will add a new parameter to the report. Right click on Parameters and click Add Parameter. Specify a name to this parameter let’s call it “Collection“, Under the Prompt type the name as “Select a Collection”, the same name will be displayed in the reports. Select the Data type as Text, Set parameter viability to Visible.
Under Available Values, Select Dataset as “Dataset1” (this is the dataset that you just created in the previous step), set Value field as CollectionID and Label field as “Name” and click OK.
This is an important step. Right click on the Dataset0 and click Dataset Properties. You will see query at the bottom, you need to add the following lines to it.
join v_FullCollectionMembership fcm on fcm.resourceid=SYS.resourceid where fcm.Collectionid= @Collection
If you add the above 2 lines at the end of query you might see a query properties window which means the query is incorrect. So the hint here is add the JOIN statements first and then add the WHERE statements. Click on OK.
In my case the final query was –
select SYS.Netbios_Name0, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath From v_GS_SoftwareFile SF join fn_rbac_R_System(@UserSIDs) SYS on SYS.ResourceID = SF.ResourceID join v_FullCollectionMembership fcm on fcm.resourceid=SYS.ResourceID Where SF.FileName LIKE @variable and fcm.Collectionid= @Collection ORDER BY SYS.Netbios_Name0
Save the report. Now when you run the report you will see the option to select a collection and by clicking on Values you can specify a collection.
Hi .. I need a way to query (script or report) each Device Collection under a specific Folder in SCCM 2006 to extract the Maintenance Window (Name, Duration, Collection Members, Start Time, End Time)
I cannot figure a way to getting this done. Can you help me?
Hi, I need help add those 2 lines to my custom report. Please help, thanks.
Select distinct
v_R_System.Netbios_Name0 AS “Computer Name”,
v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS “Console User”,
v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS “Console Logons”,
v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS “Total Minutes on Console”,
v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS “Last Console Use”
from v_R_System INNER JOIN
(SELECT
ResourceID,
SystemConsoleUser0,
NumberOfConsoleLogons0,
TotalUserConsoleMinutes0,
LastConsoleUse0,
ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY LastConsoleUse0 DESC) AS Point
FROM
v_GS_SYSTEM_CONSOLE_USER) v_GS_SYSTEM_CONSOLE_USER
ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
AND v_GS_SYSTEM_CONSOLE_USER.Point = 1
Order by v_R_System.Netbios_Name0
I have it working, but i’m not getting the “Values” link to the right of the drop-down dialogue box. Do you have any ideas what i might have missed?
Good day Prajwal.
I was curious as to how to change the or add the option for choosing a specific collection.
It appears that the report automatically users the “All Desktop and Servers” collection.
I want to be able to use another collection. I have added another dataset for “CollectionID”
Although i get the option to choose a dataset, it makes no difference.
Kindly assist
Meant to say; “Although i get the option to choose a collection, it makes no difference.”
I tried this on a report but I am getting:
The multi-part identifier “SYS.resourceid” could not be bound.
Any ideas?
Full SQL below:
select distinct
Name.Name0 As “Name”,
ShipDate.ShipDate00 As “Ship Date”,
DATEDIFF(Month,ShipDate.ShipDate00,GETDATE()) As “Age in Months”
from vSMS_R_System AS SMS_R_System
join v_FullCollectionMembership fcm on fcm.resourceid=SYS.resourceid
INNER JOIN WARRANTY_INFO_DATA AS ShipDate ON ShipDate.MachineID = SMS_R_System.ItemKey
INNER JOIN vSMS_R_System AS Name ON Name.ItemKey = ShipDate.MachineID
INNER JOIN _RES_COLL_222001EC AS SMS_CM_RES_COLL_222001EC ON SMS_CM_RES_COLL_222001EC.MachineID = SMS_R_System.ItemKey
where fcm.Collectionid= @Collection
Hi, thanks for the guide I can add the parameter and its visible when I run the report, however due to my lack of sql skills I cannot get the dataset0 query to work so it sorts by the collection, the query was made by the wizard and I have not edited it aside from adding the join lines you listed but have not been able to get it working. Is it just not compatible if I create the query using the matrix/table wizard, please see the below dataset0 query:
SELECT
Network_DATA.DHCPEnabled00
,Network_DATA.DefaultIPGateway00
,Network_DATA.IPAddress00
,Network_DATA.IPSubnet00
,Network_DATA.MACAddress00
,Network_DATA.DHCPServer00
,Network_DATA.DNSServerSearchOrder00
,Network_DATA.IPXAddress00
,Network_DATA.DNSHostName00
,Network_DATA.DNSDomain00
,Network_DATA.DNSDomainSuffixSearchOrder00
FROM
Network_DATA
Yes it is fine when using embedded query added to a query. How do you do when the first Dataset is using a stored procedure and not a query? I am using the client push installation summary and would like to add a field “System_OU_Name0” to be displayed on the report and used as the first level of sorting.
Thanks
Dom
Hi Prawaj
Thanks for the post. I’m close, but after I’m having a little trouble. After I add those two lines to the bottom of the query, I’m first prompted with this dialogue, which I leave all on “Auto”:
https://uploads.disquscdn.com/images/69a397a6b3536852d55a54bacee55a1c2ed2a509856e09ff6f3126788063a3a5.jpg
Then, I’m hit with the next warning. I’m using the SCCM reporting services account, running as a windows account, storing the credentials. If I hit OK and run the report, it doesn’t return anything. Any help is appreciated. Thanks again!
https://uploads.disquscdn.com/images/1a81de8a85227488d442ffe9bc54083c74be4b855f83c503469b92b01e1962a7.jpg
This worked wonderfully and easily resolved the issue I was experiencing, without having to run multiple queries and compare using Excel Vlookup (which I was gearing up to do before I found this); so thank you. I have several of your helpful articles bookmarked.
One thing: When creating the parameter, I had to name it “collection” (lower case “c”). This is as opposed to “Collection”, which your article shows.
If I kept the parameter name to upper case “Collection”, attempting to save the report threw the error:
“The Value expression for the query parameter ‘@Collection’ refers to a non-existing report parameter ‘collection’. Letters in the names of parameters must use the correct case.”
Worked like a champ, appreciate the advice
Thank you 🙂
Hi Prajwal –
I need some assistance ASAP. I am try to run a report for our upcoming Windows 10 upgrade. I am using the SCCM Built-In Report “Operating System requirement status for computers in a specific collection” under the Upgrade Assessment folder. This will show me what computers are eligible to upgrade to Windows 10.
I need an additional 2 columns to the report that will show the User Name of the person using the computer and what department they fall under. How can I add this to the report results? Please someone help me!. Thank you so much
Hi Prajwal –
I need some assistance ASAP. I am try to run a report for our upcoming Windows 10 upgrade. I am using the SCCM Built-In Report “Operating System requirement status for computers in a specific collection” under the Upgrade Assessment folder. This will show me what computers are eligible to upgrade to Windows 10.
I need an additional 2 columns to the report that will show the User Name of the person using the computer and what department they fall under. How can I add this to the report results? Please someone help me!. Thank you so much
Hi Prajwal –
I need some assistance ASAP. I am try to run a report for our upcoming Windows 10 upgrade. I am using the SCCM Built-In Report “Operating System requirement status for computers in a specific collection” under the Upgrade Assessment folder. This will show me what computers are eligible to upgrade to Windows 10.
I need an additional 2 columns to the report that will show the User Name of the person using the computer and what department they fall under. How can I add this to the report results? Please someone help me!. Thank you so much
Hi Prajwal –
I need some assistance ASAP. I am try to run a report for our upcoming Windows 10 upgrade. I am using the SCCM Built-In Report “Operating System requirement status for computers in a specific collection” under the Upgrade Assessment folder. This will show me what computers are eligible to upgrade to Windows 10.
I need an additional 2 columns to the report that will show the User Name of the person using the computer and what department they fall under. How can I add this to the report results? Please someone help me!. Thank you so much
Hey All, I need some help. I am try to run a report for our upcoming Windows 10 upgrade. I am using the SCCM Built-In Report “Operating System requirement status for computers in a specific collection” under the Upgrade Assesment folder. This will show me what computers are eligable to upgrade to Windows 10.
I need to add 2 columns to the report that will show the User Name of the person using the computer and what department they belong to. How can I add this to the report?
Can somebody help me!?
hi nice job dude,i had a question.i have a customized report for my hardware inventory.i want to add a collection prompt like you.how can i do that?in simple word i want to restrict my result to specific collection
Nice post..
I browsing a reference to add “Collection Name” field into report? So it can present all computers with the members of each collection in one report. I have not found a good reference..