asked on

How to filter an SSRS query by the number of results

Hello,

I have been tasked with pulling information from our SCCM database on all applications installed in the environment. I have a query to do this but I would like to filter it further by limiting the results to only those applications with 25 or more instances.

I am using the below query to pull the ARP table entries and eliminating a variety of known items that I don't care about - mainly security patches.

I only need to see apps with 25 or more installations. Is there a statement I can add to the below query to accomplish this?

I am not a SQL expert by any means so any assistance would be greatly appreciated.

--------- SQL Query ----------

Select sys.Netbios_Name0, sys.User_Name0, sys.Operating_System_Name_and0,

arp.DisplayName0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
where Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6%'
and DisplayName0 not like 'Security Update %'
and DisplayName0 not like 'Update for Windows %'
and DisplayName0 not like 'Hotfix for %'
and DisplayName0 not like '% Security Update %'
and DisplayName0 not like '%(KB%'
and DisplayName0 not like '% MUI %'

regmigrant

without having a better understanding of the number of servers being polled and the locations its a bit difficult to be sure but you are looking for something along the following lines-

....
and DisplayName0 not like '% MUI %'
GROUP BY DisplayName
HAVING COUNT(DisplayName)>25

tneubauertocg

ASKER

It is pulling the information from our master SCCM SQL database which contains the information for 5000+ clients.

I receive the following error when adding the statements to the query:
GROUP BY DisplayName0
Having COUNT(DisplayName0)>25

--error
Column 'v_R_System.Netbios_Name0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

any ideas?

ASKER CERTIFIED SOLUTION

regmigrant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tneubauertocg

ASKER

Perfect - thank you very much!