How to create an ConfigMgr/SCCM reports with prompts?

This is something simple to do and adds a lot more functionality SCCM report. I will walk you through the basics of creating a prompt for a SCCM report.

1) Go to Reporting hive and select Reports
2) Right Click and Reports and select New -> Report



3) Give your report a Name, Category and then click EDIT SQL Statement
4) Paste in your code for your SQL Statement.
 
5) Click on Prompts… button

A new window will appear. Clicking the Add button will bring up the Prompt Properties page. Complete the Name (used as @Name in your report SQL code), Prompt Text (the hint the user will see for the prompt), Default Value and if needs be click the Provide SQL Statement checkbox and provide the appropriate query. If a SQL statement is provided here, then when executing this report the user can click on "Values..." (see the last screenshot) and select a row from the output of this query. SCCM select the value of the first column of the row selcted by user and assignes that value to the variable defined in the Name field.   Your window should look something like this.

Click OK to close the window and repeat step 5 as many times as you need.
You have now successfully added prompts (variable(s)) to your SCCM report.
The following SQL query should list all the systems that are in a selected collection with IE7 installed. While this query is being executed the @Prompt variable will be replaced by the value that the end user provides or select by clicking "Values.." button.

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= @Prompt and v_Add_Remove_Programs.DisplayName0='Windows Internet Explorer 7'



2 comments:

Anonymous said...

Great help!!
Many Thanks
Nuno

Anonymous said...

I followed this to create SELECT Distinct
arp.DisplayName0 ,
Count(*) AS 'Count',
arp.Publisher0
-- @CollID as CollectionID,
FROM
v_Add_Remove_Programs arp
JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID
WHERE
fcm.CollectionID = @Prompt
AND ( ARP.DisplayName0 not like '%hotfix%'
AND ARP.DisplayName0 not like '%update%'
AND ARP.DisplayName0 not like '%Screen Saver%'
AND ARP.DisplayName0 not like '%silverlight%'
AND ARP.DisplayName0 not like '%Windows Live%')
AND (ARP.Publisher0 not like '%Intel%'
AND ARP.Publisher0 not like '%Hewlet%'
AND ARP.Publisher0 not like '%Apple%'
AND ARP.Publisher0 not like '%ati%'
AND ARP.Publisher0 not like '%eastman%'
AND ARP.Publisher0 not like '%google%')
OR ARP.Publisher0 is NULL
GROUP BY
DisplayName0,
Publisher0
ORDER BY
Publisher0


And then the prompt same as yours.

I pass the collection ID at the prompt and get the same report for all collection IDs for some reason ?

Post a Comment