Wild Card Prompts

by | Mar 23, 2012 | ConfigMgr

Last updated on July 29th, 2022 at 08:37 am

Question: Can the input variable that is query based be searchable similar to the native SCCM Web Reporting interface? i.e. Can the Collection ID be searched using a wild card rather than having to select one from a drop down only?

Background: Let us assume that we want to mimic the old ASP report prompts as best as we can. Let‘s also assume that we want a list of PCs and User Name OS for the PCs. There are a few ways to do this depending on exactly what you are trying to do.

A basic SQL query looks like this:

      dbo.v_R_System R
      join dbo.v_RA_System_SystemOUName ROU on R.ResourceID = ROU.ResourceID
      R.Name0 = @PC
Order by


A basic prompt looks like this:

Select Distinct
      CS.Name0 as 'PC Name'
Order by


Option 1: Free from Text Box

Go to the prompt and change the available values to Non-queried. This makes the prompt a free form text box.


The results look like this:



Option 2: Use a Prompt to Filter the Results of Another Prompt

  • Keep the existing queried prompt
  • Create a new prompt called Filter
  • Set the default values of the new prompt to %%




  • Change the Prompt Query to add the following line:


      cs.name0 like @filter


When you run the report, you will get something like this:



Notice that when I enter the data in the Filter section of the Prompts, it filters out the results in the drop down.

Wild Card Prompts - Drop-Down 


Option 3: Multi-Value

  • Change the main query’s where clause to:


            R.Name0 in (@PC)

  • On the prompt for PC select the Multi-value option.

Wild Card Prompts - Report Parameters


Here are the results. Notice that I combined both Option 2 and 3!

Wild Card Prompts - Option 2 and 3 Results 


If you have any questions about this blog post and wild card prompts, please contact me @GarthMJ or leave a note in the comment section below.