Report prompt help

[P@docIT]

I have a report that shows me the BIOS info for all Dell's in my environment, it works great.

 

SELECT CompName.Name0 as 'Computer Name', CompSys.Model0 as 'Model', BIOS.SMBIOSBIOSVERSION0 as 'BIOSVersion', CompSys.Manufacturer0 as 'OEM'

FROM V_R_System as Sys

LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid

LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid

LEFT JOIN V_GS_SYSTEM as CompName on Sys.resourceid = CompName.resourceid

WHERE

 

CompSys.Manufacturer0 LIKE '%dell%'

AND (CompSys.Model0 LIKE '%optiplex%'

OR CompSys.Model0 LIKE '%latitude%'

OR CompSys.Model0 LIKE '%precision%')

 

GROUP BY CompSys.Manufacturer0,CompSys.model0, BIOS.SMBIOSBIOSVersion0, CompName.Name0

ORDER BY CompSys.Model0, BIOS.SMBIOSBIOSVersion0

 

However I would like to create a prompt that will allow me to enter a model # and only see those models. So far i have tried the two examples below but they both still return all models instead of the model specified in the prompt.(screenshot) Actually I just did a test and typed some random string of letters in the prompt and it gives the whole report. So the prompt doesn't appear to be doing anything.

 

begin

if (@__filterwildcard = '')

SELECT DISTINCT CompSys.Model0 from v_GS_COMPUTER_SYSTEM as CompSys Order by CompSys.Model0

else

SELECT DISTINCT CompSys.Model0 from v_GS_COMPUTER_SYSTEM as CompSys

WHERE CompSys.Model0 like @__filterwildcard

Order by CompSys.Model0

end

 

Tried this with and without the distinct:

select distinct Model0 from v_GS_COMPUTER_SYSTEM

[P@docIT]

RESOLVED

 

Report SQL:

SELECT CompName.Name0 as 'Computer Name', CompSys.Model0 as 'Model', BIOS.SMBIOSBIOSVERSION0 as 'BIOSVersion', CompSys.Manufacturer0 as 'OEM'

FROM V_R_System as Sys

LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid

LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid

LEFT JOIN V_GS_SYSTEM as CompName on Sys.resourceid = CompName.resourceid

WHERE

 

CompSys.Manufacturer0 LIKE '%dell%'

AND CompSys.Model0 LIKE @variable

 

GROUP BY CompSys.Manufacturer0,CompSys.model0, BIOS.SMBIOSBIOSVersion0, CompName.Name0

ORDER BY CompSys.Model0, BIOS.SMBIOSBIOSVersion0

 

Prompt Properties:

Note: The name must be variable.

begin

if (@__filterwildcard = '')

SELECT DISTINCT CompSys.Model0 from v_GS_COMPUTER_SYSTEM as CompSys Order by CompSys.Model0

else

SELECT DISTINCT CompSys.Model0 from v_GS_COMPUTER_SYSTEM as CompSys

WHERE CompSys.Model0 like @__filterwildcard

Order by CompSys.Model0

end