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'