0 Replies Latest reply on Nov 24, 2010 7:22 AM by Robbster

    [SQL DB] Reporting with SSRS on Pattern Distribution

      Hello everybody,

       

      I was trying to write a SQL Query for the EPO DB to get some data for a special tailor made report by myself. I am working with SQL Server Reporting Service and the Microsoft Business Intelligence Development Studio. I want the total amount of computers with the actual pattern and the 3 former versions of the pattern with computer name and branch node name. But I have problems to get the total amount for each branch. Does anyone have an idea how to design the sql query in this case? I post it for you here:

       

      SELECT     EPOLeafNode.NodeName AS ComputerName, EPOBranchNode.NodeName,
                            EPOProductProperties.DATVer,
                    (SELECT COUNT(EPOProductProperties.DATVer)
                                  FROM         EPOLeafNode INNER JOIN
                                  EPOBranchNode ON EPOLeafNode.ParentID = EPOBranchNode.AutoID INNER JOIN
                                  EPOProductProperties ON EPOLeafNode.AutoID = EPOProductProperties.ParentID
                                  WHERE      (EPOProductProperties.ProductCode = 'VIRUSCAN8700') AND EPOBranchNode.NodeName = 'AR01'
                                  GROUP BY EPOProductProperties.ProductCode) AS TotalDat,
                            (SELECT     MAX(DATVer)
                                  FROM          EPOProductProperties
                                  WHERE      (ProductCode = 'VIRUSCAN8700')
                                  GROUP BY ProductCode) AS MaxDat
      FROM         EPOLeafNode INNER JOIN
                            EPOBranchNode ON EPOLeafNode.ParentID = EPOBranchNode.AutoID INNER JOIN
                            EPOProductProperties ON EPOLeafNode.AutoID = EPOProductProperties.ParentID
      WHERE     (EPOProductProperties.ProductCode LIKE 'VIRUS%')
          AND EPOBranchNode.NodeName = 'AR01'
          OR EPOBranchNode.NodeName = 'AT01'
          OR EPOBranchNode.NodeName = 'AU01'
          OR EPOBranchNode.NodeName = 'BE01'
          OR EPOBranchNode.NodeName = 'BR00'
          OR EPOBranchNode.NodeName = 'BG01'
          OR EPOBranchNode.NodeName = 'CA01'
          OR EPOBranchNode.NodeName = 'CL01'
          OR EPOBranchNode.NodeName = 'CN00'
          OR EPOBranchNode.NodeName = 'CO01'
          OR EPOBranchNode.NodeName = 'HR01'
          OR EPOBranchNode.NodeName = 'CZ00'
          OR EPOBranchNode.NodeName = 'DK01'
          OR EPOBranchNode.NodeName = 'DO01'
          OR EPOBranchNode.NodeName = 'EC01'
          OR EPOBranchNode.NodeName = 'FI01'
          OR EPOBranchNode.NodeName = 'FR00'
          OR EPOBranchNode.NodeName = 'DE00'
          OR EPOBranchNode.NodeName = 'HU00'
          OR EPOBranchNode.NodeName = 'IN01'
          OR EPOBranchNode.NodeName = 'ID01'
          OR EPOBranchNode.NodeName = 'IE01'
          OR EPOBranchNode.NodeName = 'IT01'
          OR EPOBranchNode.NodeName = 'JP01'
          OR EPOBranchNode.NodeName = 'KR01'
          OR EPOBranchNode.NodeName = 'MY01'
          OR EPOBranchNode.NodeName = 'MY02'
          OR EPOBranchNode.NodeName = 'MX01'
          OR EPOBranchNode.NodeName = 'NL01'
          OR EPOBranchNode.NodeName = 'NO01'
          OR EPOBranchNode.NodeName = 'PK01'
          OR EPOBranchNode.NodeName = 'PE01'
          OR EPOBranchNode.NodeName = 'PH01'
          OR EPOBranchNode.NodeName = 'PL01'
          OR EPOBranchNode.NodeName = 'PT01'
          OR EPOBranchNode.NodeName = 'RO01'
          OR EPOBranchNode.NodeName = 'RU01'
          OR EPOBranchNode.NodeName = 'SG01'
          OR EPOBranchNode.NodeName = 'ZA01'
          OR EPOBranchNode.NodeName = 'ES00'
          OR EPOBranchNode.NodeName = 'SE02'
          OR EPOBranchNode.NodeName = 'CH00'
          OR EPOBranchNode.NodeName = 'TW01'
          OR EPOBranchNode.NodeName = 'TH01'
          OR EPOBranchNode.NodeName = 'TR01'
          OR EPOBranchNode.NodeName = 'UK01'
          OR EPOBranchNode.NodeName = 'US00'
          OR EPOBranchNode.NodeName = 'VN01'
      GROUP BY EPOBranchNode.NodeName, EPOLeafNode.NodeName, EPOProductProperties.DATVer
      ORDER BY NodeName, ComputerName

       

      If anyone has an idea, please tell me. Posted it here because noone should know the database architecture better than someone in here.


      Regards

      robbster