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, '*', '%')
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.
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:-)
Here I've just created a simple tablix with result just to show you that it works:-)
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?
ReplyDeleteI'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?
ReplyDeleteSELECT 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
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.
DeleteHey Garistotle,
ReplyDeleteWithin 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
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..
ReplyDeleteExcellent information MSBI Online Training Bangalore
ReplyDeletekayseriescortu.com - alacam.org - xescortun.com
ReplyDeleteEn son çıkan perde modelleri
ReplyDeletesms onay
mobil ödeme bozdurma
nft nasıl alınır
ankara evden eve nakliyat
Trafik Sigortası
dedektör
web sitesi kurma
Ask romanlari
SMM PANEL
ReplyDeletesmm panel
İŞ İLANLARI
instagram takipçi satın al
hirdavatciburada.com
Https://www.beyazesyateknikservisi.com.tr
Servis
Jeton hile indir
maltepe vestel klima servisi
ReplyDeletependik samsung klima servisi
pendik mitsubishi klima servisi
kadıköy vestel klima servisi
kartal mitsubishi klima servisi
ümraniye mitsubishi klima servisi
beykoz vestel klima servisi
üsküdar vestel klima servisi
beykoz bosch klima servisi
nft nasıl alınır
ReplyDeleteminecraft premium
uc satın al
en son çıkan perde modelleri
en son çıkan perde modelleri
lisans satın al
yurtdışı kargo
özel ambulans