SSRS Search Filter

Hi

This is just a quick post to show how you can create a search filter to limit a long list ,in this case software info collected by Configmgr.

Combine this with multi valued parameters and you have a good method to show all software versions, even if the vendor don't keep the publisher name consistant. 

In my case, if i didn't have the search filter the Publisher list would be 3338 entries long ,not very user friendly...

So what we do is to add a search filter that we use in the query that populates the Publisher Parameter. We take the input from the searchfilter and use it in the query for the next Publisher list with a LIKE statement (We use a replace statement to translate the wildcard to "%" )

SELECT DISTINCT publisher0 AS Publisher
FROM   v_add_remove_programs
WHERE  publisher0 LIKE Replace(@SearchFilter, '*', '%') 

With the use of multi valued parameters we can select the Publishers we want. Just remember with multi valued parameters you have to use the IN operator.

So to get the the values for the application list parameter we do the following.

SELECT DISTINCT displayname0 AS Displayname
FROM   v_add_remove_programs
WHERE  publisher0 IN ( @Publisher ) 

So now we can just create the main query with two IN Operators. Where Publisher is in the publisher list and Displayname in the Sofware List.

SELECT name0,
       B.*
FROM   v_r_system A
       JOIN v_add_remove_programs B
         ON A.resourceid = B.resourceid
WHERE  B.publisher0 IN ( @Publisher )
       AND B.displayname0 IN ( @Software ) 

Last ,build your report.
Here I've just created a simple tablix with result just to show you that it works:-)