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:
SelectR.Name0,R.User_Name0,ROU.System_OU_Name0fromdbo.v_R_System Rjoin dbo.v_RA_System_SystemOUName ROU on R.ResourceID = ROU.ResourceIDWhereR.Name0 = @PCOrder byR.Name0
A basic prompt looks like this:
Select DistinctCS.Name0 as 'PC Name'Fromdbo.v_GS_COMPUTER_SYSTEM CSOrder byCS.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.
Results look like this
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 %%
· 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.
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.
Here are the results. Notice that I combined both Options 2 and 3!