3 Replies Latest reply on Nov 24, 2011 12:19 AM by Peter Lee

    Make a matrix report for a SW list and clients

    Peter Lee Apprentice

      Hi All,

       

      I wan to make a report to show a matrix table like below. Please anyone tellme how to do it. Thanks

       

      SW ASW BSW CSW DSW ESW FSW GSW H
      Client-1VVVVVVVV
      Client-2VVVVV
      Client-3VVVVV
      Client-4VVVVVV
      Client-5VVVVVVVV
      Client-6VVVVVV
      Client-7VVVVVV
      Client-8VVVVVVVV
      Client-9VVVVVVV
      Client-10VVVVV
      Client-11VVVVVV
      Client-12VVVVVV
      Client-13VVVVVVVV
      Client-14VVVVVVVV
      Client-15VVVV
      Client-16VVVVVVVV

        • 1. Re: Make a matrix report for a SW list and clients
          Peter Lee Apprentice

          I use the SQL scripts to this resolved.

          • 2. Re: Make a matrix report for a SW list and clients
            zman Master

            Can you share your scripts? If so please post them.

            • 3. Re: Make a matrix report for a SW list and clients
              Peter Lee Apprentice

              Yes, here are the SQL scripts I used in my report.

               

              -----------------------------------------

              SELECT A0.DISPLAYNAME AS "裝置名稱", A0.TYPE AS "類型", A2.OSTYPE AS "作業系統名稱",
              SUM(CASE WHEN A1.TITLE = 'Microsoft Office 2010 (14.0)' THEN 1 ELSE 0 END) AS "Microsoft Office 2010 (14.0)",
              SUM(CASE WHEN A1.TITLE = 'ESET NOD32 Antivirus (4.2 [2])' THEN 1 ELSE 0 END) AS "ESET NOD32 Antivirus (4.2)",
              SUM(CASE WHEN A1.TITLE = 'Microsoft SQL Server 2008 安裝程式支援檔案 (繁體中文) (10.0)' THEN 1 ELSE 0 END)
              AS "Microsoft SQL Server 2008 安裝程式支援檔案 (繁體中文) (10.0)"
              --A1.TITLE AS "標題" 
              FROM Computer A0 (nolock)
              LEFT OUTER JOIN ProductInstance A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn
              LEFT OUTER JOIN Operating_System A2 (nolock) ON A0.Computer_Idn = A2.Computer_Idn 
              WHERE
              (A1.TITLE = N'Microsoft Office 2010 (14.0)' OR A1.TITLE = N'ESET NOD32 Antivirus (4.2 [2])'
              OR A1.TITLE = 'Microsoft SQL Server 2008 安裝程式支援檔案 (繁體中文) (10.0)')
              GROUP BY A0.DISPLAYNAME ,  A0.TYPE, A2.OSTYPE
              ORDER BY  A0.DISPLAYNAME

              -----------------------------------------