< Blog

Wild Card Prompts

Q: 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:  

 

Select 
 R.Name0,
 R.User_Name0,
 ROU.System_OU_Name0
from 
 dbo.v_R_System R
 join dbo.v_RA_System_SystemOUName ROU on R.ResourceID = ROU.ResourceID
Where
 R.Name0 = @PC 
Order by 
 R.Name0

 

A basic prompt looks like this:  

 

Select Distinct 
 CS.Name0 as 'PC Name'
From 
 dbo.v_GS_COMPUTER_SYSTEM CS
Order by 
 CS.Name0

 

 

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.

 clip_image002

 

 

Results look like this

clip_image004

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

·        Keep the existing queried prompt 

·     Create a new prompt called Filter

·        Set the default values of the new prompt to %%

clip_image006

 

· Change the Prompt Query to add the following line:

Where

      cs.name0 like @filter

 

 

 

 

When you run the report you will get something like this.

clip_image008

 

 

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

clip_image010  

 

 

Option 3: Multi-Value

  • Change the main query’s where clause to:

Where

            R.Name0 in (@PC)

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

clip_image012

 

 

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

 

 

clip_image014