Client Management Suite

 View Only

  • 1.  Dropdown filter for report

    Posted Feb 01, 2012 02:21 PM

    Hi,

     

    I have the following report which is built from a SQL query:

     

     

    SELECT w1.Name as 'Name',
     
    t1.[Device ID] as 'Drive',
     
    t1.[Description] as 'Description',
     
    t1.[Size (Bytes)] as 'Disk Size Bytes',
     
    (t1.[Size (Bytes)])/1024/1024/1024 as 'Disk Size GB',
     
    t1.[Free Space (Bytes)] as 'Free Space Bytes',
     
    (t1.[Free Space (Bytes)])/1024/1024 as 'Free Space MB',
     
    CONVERT(DECIMAL(5,2),100.0 * t1.[Free Space (Bytes)] / t1.[Size (Bytes)]) AS [Free Space(%)], --this is the percentage field
     
    w1.Guid as 'ResourceGuid',
     
    u1.[User] as 'Primary User'
     
    FROM vComputer w1
     
    INNER JOIN vHWLogicalDisk t1
     
    ON w1.Guid = t1._ResourceGuid
     
    LEFT JOIN Inv_AeX_AC_Primary_User u1
     
    ON w1.Guid = u1._ResourceGuid
     
    WHERE u1.[Month] = DATENAME(mm, GETDATE())
     
    AND LOWER(t1.[Description]) LIKE '%local%'
     
    AND t1.[Size (Bytes)] > '2000'
     
    and CONVERT(DECIMAL(5,2),100.0 * t1.[Free Space (Bytes)] / t1.[Size (Bytes)]) < '50' ---this is the percentage parameter
     
    ORDER BY w1.[Name]
     
    I have tried following the guidelines on this article:
     
     
    However, this only applies to computer reports, not SQL reports.
     
    Any idea on how I can filter this report results against a specific filter that I have the GUID for, or better yet to have a dropdown so I can select the filter?

    Thanks!


  • 2.  RE: Dropdown filter for report
    Best Answer

    Posted Feb 01, 2012 03:13 PM

    Build this according to the KB, confirm it works, convert to a SQL query, and then add in your query above.



  • 3.  RE: Dropdown filter for report

    Posted Feb 01, 2012 03:40 PM

    It doesn't work.  Once I convert it and add the query, it just lists all computers.  Something gets broken in the process.  It gives me the ability to select a filter, but it ignores the input.



  • 4.  RE: Dropdown filter for report

    Posted Feb 02, 2012 09:53 AM

    Thanks for your help.  I'll post the final query in a bit.



  • 5.  RE: Dropdown filter for report

    Posted Feb 02, 2012 11:27 AM

     

    DECLARE @v1_TrusteeScope nvarchar(max)
       SET @v1_TrusteeScope = N'%TrusteeScope%'
    DECLARE @v2_ReportFilter uniqueidentifier
       SET @v2_ReportFilter = '%Report Filter%'
    SELECT
       w1.Name as 'Name',
       t1.[Device ID] as 'Drive',
       t1.[Description] as 'Description',
       t1.[Size (Bytes)] as 'Disk Size Bytes',
       (t1.[Size (Bytes)])/1024/1024/1024 as 'Disk Size GB',
       t1.[Free Space (Bytes)] as 'Free Space Bytes',
       (t1.[Free Space (Bytes)])/1024/1024 as 'Free Space MB',
       CONVERT(DECIMAL(5,2),100.0 * t1.[Free Space (Bytes)] / t1.[Size (Bytes)]) AS [Free Space(%)], --this is the percentage field
       w1.Guid as 'ResourceGuid',
       u1.[User] as 'Primary User'
       
       FROM 
       
       vComputer w1
       
       INNER JOIN vHWLogicalDisk t1
     
    ON w1.Guid = t1._ResourceGuid
     
    LEFT JOIN Inv_AeX_AC_Primary_User u1
     
    ON w1.Guid = u1._ResourceGuid
         
          INNER JOIN [CollectionMembership] AS [ajs7_CollectionMembership]
             ON ([w1].[Guid] = [ajs7_CollectionMembership].[ResourceGuid])
                AND
                ([ajs7_CollectionMembership].[CollectionGuid] = @v2_ReportFilter)
    WHERE
       (
          ([w1].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
       )
     
    AND u1.[Month] = DATENAME(mm, GETDATE())
     
    AND LOWER(t1.[Description]) LIKE '%local%'
     
    AND t1.[Size (Bytes)] > '2000'
     
    and CONVERT(DECIMAL(5,2),100.0 * t1.[Free Space (Bytes)] / t1.[Size (Bytes)]) < '50' ---this is the percentage parameter
     
    ORDER BY w1.[Name]