Objective: Pull report usage statistics from MS SSRS ReportServer database. Then, link to another table in a separate database (same server) to pull in employee names (Report Server only lists someone's Windows login name).

Issue: I have written a query that runs without errors, but I can confirm it's pulling in far too many records. I think this is because the employee file has multiple records per employee and there is not a unique column to go off of. I think it could do with the join but I have not been able to figure it out, so hoping someone can help.

Table 1 - Catalog (ReportServer DB)

Table 2 - ExecutionLogStorage (ReportServer DB) | TimeStart | TimeDate\aRetrieval| TimeProcessing | RowCount | ByteCount | UserName|ReportID| |:--------- |:----------------:|:--------------:|:--------:| :--------:|:--------:|---------:| | 2022-03-16 07:00:16:767| 11000| 500| 250| 150| Domain\UserName | Random sequence of numbers |

The only unique column on this table is a LogEntryId column (a number for each row).

Table 3 - Users (ReportServer DB) | UserName| |:----------------:| | Domain\UserName |

UserName is unique (one row exists for each UserName)

Table 4 - Employees (Custom DB) | WindowsLogIn|EmployeeName| Position| |:--------- |:----------------:|---------:| | Domain\UserName |John Smith | Director | | Domain\UserName | Mary Allen| Manager |

Current Query (resulting in erroneous counts (duplicated rows, much higher counts than expected)

SELECT
   EmployeeName, 
   Position,
   sum(ExecCount) AS ExecCount,
   sum(UniqueReportCount) AS UniqueReportCount

FROM
   (SELECT
      RE.EmployeeName,
      RE.Position,
      COUNT(*) AS ExecCount,
      COUNT (DISTINCT RptName) AS UniqueReportCount

      FROM
       (SELECT
         EX.TimeStart, 
         CAT.Type,
         CAT.Name AS RptName,
         EX.TimeDataRetrieval,
         EX.TimeProcessing, 
         EX.TimeRendering,
         EX.ByteCount,
         Ex.[RowCount],
         EX.UserName AS LogUser,
         Users.UsernName,
         Employees.WindowsLogIn,
         Employees.EmployeeName,
         Employees.Position

         FROM [ReportServer].Catalog AS CAT
         INNER JOIN [ReportServer].[ExecutionLogStorage] AS EX ON CAT.ItemID = EX.Report.ID
         LEFT OUTER JOIN [ReportServer].[Users]. AS Users ON Users.UserName = EX.UserName
         LEFT OUTER JOIN [CustomDB].[Employees] AS Employees ON Users.User = Employees.WindowsLogIn collate SQL_Latin1_General_CP1_CI_AS
         
          WHERE Type = 2
          AND EX.TimeStart BETWEEN @DateFrom AND @DateTo
          ) AS RE
   
    GROUP BY
      EmployeeName,
      Position,
      RptName
    ) AS UserLog

GROUP BY EmployeeName, Position
ORDER BY ExecCount DESC

Current Output Currently I'm getting the intended format in the report I built in SSRS. It's just the data that is duplicated. I have tried changing the Group By without much luck so I am wondering if it has to do with the last join? But how would I pull in the Employee Names from the Employees table if there are multiple records for each person on that table?

The format of the current output is like this: | Employee Name | Position | Report Executions| Unique Reports| |:------------- |:--------:|:----------------:| -------------:| | John Smith | Director | 1000 | 50 | | Mary Allen | Manager| 500| 10|

CodePudding user response：

If your duplicates are in [CustomDB].[Employees] then just select distinct values from this table.

So change this

 LEFT OUTER JOIN [CustomDB].[Employees] AS Employees ON Users.User = Employees.WindowsLogIn collate SQL_Latin1_General_CP1_CI_AS

to this

LEFT OUTER JOIN (SELECT DISTINCT WindowsLogIn, EmployeeName, Position FROM [CustomDB].[Employees]) AS Employees ON Users.User = Employees.WindowsLogIn collate SQL_Latin1_General_CP1_CI_AS