Software Management Group

How do I add a Filter picker to a report that is SQL Query based?

What I am trying to accomplish is write a report let's say Visio then run it against a filter.

Your best bet is to find a built in one with a filter picker and either clone that or at least copy the relevant bits. I'm afraid I don't have a console in front of me at the moment to find one.

I just posted an example report that shows how to limit a reports results to resource filter members; and by resource name. Hopefully it will be published soon. Search for: Example report: limit results to resource filter members.

I think [Reports > Discovery and Inventory > Inventory > Cross-platform > Operating System >] Computer Summary is quite a good one to clone and edit.

Try followiong and you can join to anything, Also you can use guid of your collection to be , a drop down Parameter. 

Select * from vcomputer where guid in (

Select Resourceguid from CollectionMembership where collectionguid = 'guid of your collection'

Here is the SQL I use.  In this example I'm using report paramers to fill in the software name and filter name but you could simply hard code that info into the SQL if you want. 

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT DISTINCT
   ARP.DisplayName AS 'Software Name',
   ARP.[Publisher] AS 'Publisher',
   ARP.[DisplayVersion] AS 'Version',
   COUNT(DISTINCT ARP.[_ResourceGuid]) AS 'Quantity'  
FROM
   [Inv_AddRemoveProgram] ARP
   LEFT JOIN [vComputer] VC ON ARP.[_ResourceGuid] = VC.[Guid]

WHERE ARP.[DisplayName] like '%ProductName%'  and ARP.InstallFlag = 1
 

  AND ([vc].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))

   AND vc.[Guid] IN
(
 select vc.[Guid]                    
 from vCollection vColl                    
 Left JOIN CollectionMembership cm ON vColl.[Guid] = cm.[CollectionGuid]                    
 Left JOIN vComputer vc ON vc.[Guid] = cm.[ResourceGuid]                        
 WHERE vColl.Guid = '%FilterName%')

GROUP BY
   ARP.[DisplayName],
   ARP.[Publisher],
   ARP.[DisplayVersion]
ORDER BY ARP.[DisplayName] asc, arp.DisplayVersion asc

I have done this a few times for some custom software inventory reports I've made.  

I've had to add this to the begining of my SQL...
DECLARE @v1_Reportfilter uniqueidentifier
   SET @v1_Reportfilter = 'FilterGuid'

Then under the Where statement, add:
 ([ajs5_CollectionMembership].[CollectionGuid] = @v1_Reportfilter)
 

Or do the best you can following this http://www.symantec.com/business/support/index?page=content&id=HOWTO21759 , then take what SQL code you need out of it.

i dont think it is posted yet, I tried searching for it. Do you mind sending it to me via email?:

This article has not been posted. See:

https://www-secure.symantec.com/connect/downloads/example-report-limit-results-resource-filter-members

Still no luck adding the filter picker, here is my query. could some one help?

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'

IF ('%Type%' = 'Managed Software')
SELECT DISTINCT comps.Guid [Guid]
       ,comps.Name [Computer Name]
    ,prods.[Name]
    ,spv.[Version]
    ,company.Name [Company]
FROM vRM_Software_Component sc
JOIN Inv_InstalledSoftware inst
   ON inst._SoftwareComponentGuid = sc.Guid
   AND inst.InstallFlag = 1
JOIN (SELECT ra.ChildResourceGuid, spi.Guid, spi.Name
      FROM vRM_Software_Product_Item spi
      JOIN ResourceAssociation ra
         ON ra.ParentResourceGuid = spi.Guid
   AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
  )prods
   ON prods.ChildResourceGuid = sc.Guid
JOIN dbo.Inv_Software_Product_State sps
    ON sps._ResourceGuid = prods.Guid
    AND sps.IsManaged = 1
JOIN dbo.Inv_Software_Product_Version spv
  ON spv._ResourceGuid = prods.Guid
JOIN (SELECT vci.Guid, vci.Name
      FROM vRM_Computer_Item vci
      LEFT JOIN ResourceAssociation resAssoc
         ON vci.Guid = resAssoc.ParentResourceGuid
      AND resAssoc.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' --Asset Status
      WHERE (resAssoc.ChildResourceGuid = '0A0203A5-D2B6-49f1-A53B-5EC31A89437C' OR resAssoc.ChildResourceGuid IS NULL) -- ONLY Active Computers
      AND vci.Guid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
      ) comps
   ON comps.Guid = inst._ResourceGuid

 
LEFT JOIN (SELECT ra.ParentResourceGuid AS softProdGuid, rc.Name
           FROM RM_ResourceCompany rc
           JOIN ResourceAssociation ra
              ON ra.ChildResourceGuid = rc.Guid
        AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D'
     ) company
   ON company.softProdGuid = spv._ResourceGuid
  
WHERE ('%Computer%' = '%' OR LOWER (comps.Name) LIKE LOWER ('%Computer%'))

ORDER BY prods.Name, comps.Name

To add a filter picker to a report.

1. Create a new SQL Report

2. Add a basic guid parameter to the report as shown below.

3. Save the changes. The parameter should look like this:

4. Export the report to XML

5. Edit the XML file, replacing this section

<valueProvider classGuid="aa1c2816-cbe6-47ae-a26c-a7fc6e7a3fec">
  <configuration readonly="False" hideOnError="False">
    <style multiline="False" multilinerows="2" labelVisible="True" />
  </configuration>
</valueProvider>

with this code:

<valueProvider classGuid="fd8b9293-29b1-42e2-bac5-7774df8143cc">
    <configuration readonly="False" hideOnError="False">
        <style labelVisible="True" linkInSummary="False" showNoControls="False" allowMultiSelect="False" />
    </configuration>
</valueProvider>

6. Save the xml and import it into the same folder.

7. You can now use this parameter to display only members of the selected filter by adding some code like this to your SQL query:

INNER JOIN (SELECT ResourceGuid FROM CollectionMembership WHERE CollectionGuid ='%Filter%') f
ON f.ResourceGuid = c.Guid