title: Discrepancy between T-SQL and SSRS results
body: <p>I created the following code in T-SQL(see below)
  It is working good in SSMS environment, but, when I post it into SSRS (Reporting Services) the numbers for <strong>[Total]</strong> and <strong>[Completed]</strong> fields are very different in SSRS than it is in SSMS - much higher than in SSMS</p>
  
  <p>This is my code:</p>
  
  <pre><code>     USE [WH]
       GO
  
         DECLARE @MonthBOP date = '05-01-2019'
         DECLARE @MonthEOP date = '05-01-2019'
         DECLARE @EHRRegionsWHID int = -1
         DECLARE @EHRProgramsWHID int = -1
         DECLARE @EHRProgramTypesWHID int = -1
  
  
      SELECT        
            ehrrgn.WHID AS [EHRRegionWHID] 
          , ehrrgn.EHRRegion 
          , ehrprgtype.WHID AS [EHRProgramTypeWHID] 
          , ehrprg.WHID AS [EHRProgramWHID]
          , ehrprg.EHRProgramCode
          , ehrprg.EHRProgramName 
          , poswh.WHID AS [PositionWHWHID]
          , poswh.PositionNameWH
          , posfamwh.WHID AS [PositionFamilyWHWHID]
          , posfamwh.PositionFamilyNameWH
          , pos.WHID AS [PositionWHID]
          , empl.EmployeeCode
          , empl.EmployeeName
          , ct.ServiceDate
          , dd.YearMonthSort
          , ct.StaffFK
          , COUNT(ct.ServiceFK) AS [Completed]
          , tt.Total
  
     FROM          
         Fact.EHRClinicalTransaction ct
         INNER JOIN Dimension.EHRProgram ehrprg ON 
                         ct.ProgramFK = ehrprg.WHID
  
         INNER JOIN Dimension.EHRRegion ehrrgn ON 
                         ehrprg.EHRRegionFK = ehrrgn.WHID
  
         INNER JOIN Dimension.EHRProgramType ehrprgtype ON 
                         ehrprg.EHRProgramTypeFK = ehrprgtype.WHID
  
         INNER JOIN Dimension.EHRStaff ehrstf ON ct.StaffFK = ehrstf.WHID 
  
         INNER JOIN Dimension.PaycomEmployee empl ON 
                         ehrstf.PaycomEmployeeFK = empl.WHID
  
         INNER JOIN Dimension.PaycomPosition pos ON 
                         empl.PaycomPositionFK = pos.WHID
  
         INNER JOIN Dimension.PaycomPositionWH poswh ON 
                         pos.PositionWHFK = poswh.WHID
  
         INNER JOIN Dimension.PaycomPositionFamilyWH posfamwh ON 
                         pos.PositionFamilyWHFK = posfamwh.WHID
  
         INNER JOIN Dimension.[Date] dd ON ct.ServiceDate = dd.[Date]
  
         INNER JOIN 
                  ( 
                   SELECT 
                         ct.[StaffFK] 
                        ,COUNT(ct.[ServiceFK]) AS [Total]
  
                   FROM 
                       Fact.EHRClinicalTransaction ct
                       INNER JOIN Dimension.EHRProgram ehrprg ON 
                                        ct.ProgramFK = ehrprg.WHID 
                       INNER JOIN Dimension.EHRRegion ehrrgn ON 
                                      ehrprg.EHRRegionFK = ehrrgn.WHID
                       INNER JOIN Dimension.EHRProgramType ehrprgtype ON 
                               ehrprg.EHRProgramTypeFK = ehrprgtype.WHID
                       INNER JOIN Dimension.EHRStaff ehrstf ON 
                              ct.StaffFK = ehrstf.WHID 
                       INNER JOIN Dimension.PaycomEmployee empl ON 
                              ehrstf.PaycomEmployeeFK = empl.WHID
                       INNER JOIN Dimension.PaycomPosition pos ON 
                              empl.PaycomPositionFK = pos.WHID
                       INNER JOIN Dimension.PaycomPositionWH poswh ON 
                              pos.PositionWHFK = poswh.WHID
                   INNER JOIN Dimension.PaycomPositionFamilyWH posfamwh ON 
                              pos.PositionFamilyWHFK = posfamwh.WHID
                   INNER JOIN Dimension.[Date] dd ON 
                              ct.ServiceDate = dd.[Date]
  
                WHERE
                      (ct.[ServiceDate] BETWEEN @MonthBOP AND @MonthEOP) 
                 AND ((ehrrgn.WHID IN (@EHRRegionsWHID)) OR 
                      (-1 IN (@EHRRegionsWHID))) AND (ehrrgn.WHID &lt;&gt;-1)
                 AND ((ehrprg.WHID IN (@EHRProgramsWHID)) OR 
                      (-1 IN (@EHRProgramsWHID))) AND (ehrprg.WHID &lt;&gt;-1)
                 AND ((ehrprgtype.WHID IN (@EHRProgramTypesWHID)) OR 
                 (-1 IN (@EHRProgramTypesWHID))) AND (ehrprgtype.WHID &lt;&gt;-1)
  
             GROUP BY
                    ct.[StaffFK] 
  
               ) tt ON  ct.StaffFK = tt.StaffFK
  
    WHERE        
         (ct.IsNoShow = 0) 
    AND (ct.IsDeleted = 0)
    AND (ct.[ServiceDate] BETWEEN @MonthBOP AND @MonthEOP) 
    AND ((ehrrgn.WHID IN (@EHRRegionsWHID)) OR (-1 IN (@EHRRegionsWHID))) 
    AND (ehrrgn.WHID &lt;&gt;-1)
    AND ((ehrprg.WHID IN (@EHRProgramsWHID)) OR (-1 IN (@EHRProgramsWHID))) 
    AND (ehrprg.WHID &lt;&gt;-1)
    AND ((ehrprgtype.WHID IN (@EHRProgramTypesWHID)) OR 
         (-1 IN (@EHRProgramTypesWHID))) AND (ehrprgtype.WHID &lt;&gt;-1)
     AND (ct.ServiceFK NOT IN (1, 19, 31, 49, 64, 83, 85, 86, 87, 111, 131, 
                               133, 134, 137, 139, 
          140, 141, 165, 167, 168, 171, 173, 174, 175, 198, 436, 442, 443, 
                            503, 522, 540, 541))
  
   GROUP BY 
            ehrrgn.WHID 
          , ehrrgn.EHRRegion 
          , ehrprgtype.WHID  
          , ehrprg.WHID 
          , ehrprg.EHRProgramCode
          , ehrprg.EHRProgramName 
          , poswh.WHID 
          , poswh.PositionNameWH
          , posfamwh.WHID 
          , posfamwh.PositionFamilyNameWH
          , pos.WHID 
          , empl.EmployeeCode
          , empl.EmployeeName
          , ct.ServiceDate
          , dd.YearMonthSort
          , ct.StaffFK 
          , tt.Total
  </code></pre>
  
  <p>Why in SSRS they jumped so high?</p>
  
  <p>Here is my SSRS "Design":</p>
  
  <p><a href="https://i.stack.imgur.com/XXuXj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XXuXj.png" alt="enter image description here"></a>   </p>
  
  <p>And here is the result-example in SSRS:
  While these numbers (half-circled in red) are so high, they are very small
  (and correct, after auditing) in SSMS (t-sql):
  <a href="https://i.stack.imgur.com/WyMFh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WyMFh.png" alt="enter image description here"></a></p>
  
  <p>Please help or advice!</p>
  
  <p>PS-
  You can click on the pictures to see it larger</p>