Thursday, January 7, 2016

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



11 comments:

  1. Hey Larsen this is close to what I am wanting to do. https://www.windows-noob.com/forums/topic/14685-all-multiple-collection-to-be-selected-how-to/ Thoughts?

    ReplyDelete
  2. I'd like to edit the built-in "Count of all instances of software registered with Add or Remove Programs" report to include these options to get better results. Would I be editing DataSet0 to accomplish this? If it is DataSet0, and it's easy enough, could you show me how the query below would look if edited to show these great selection menus?

    SELECT DisplayName0, Count(DISTINCT arp.ResourceID) AS 'Count', Publisher0, Version0, @CollID as CollectionID
    FROM fn_rbac_Add_Remove_Programs(@UserSIDs) arp
    JOIN fn_rbac_FullCollectionMembership(@UserSIDs) fcm on arp.ResourceID=fcm.ResourceID
    WHERE fcm.CollectionID = @CollID
    GROUP BY DisplayName0, Publisher0, Version0
    ORDER BY Publisher0, Version0

    ReplyDelete
    Replies
    1. I would also love to get rid of "Version" as well so, for example, I could get a count of how many instances of Acrobat XI there are, rather than a count of each version of Acrobat XI.

      Delete
  3. Hey Garistotle,

    Within SSRS add another column for the resourceid field, update the expression value to:

    =Count(Fields!ResourceID.Value)

    remove/delete the name0 column and then update the row groups Group expression to group by publisher0, displayname0, and version0

    ReplyDelete
  4. Startup companies need to keep track of their employees and their companies overall efficiency in that sense we need to look into efficient concepts like report management software, to be implemented..Which will not only help us to improve the overall efficiency but also we can track the individual output of each and every employee..In that sense it will be a great value addition..

    ReplyDelete