SQL存储过程在SQL Server管理工具中运行正常，但在报告服务(SSRS)中却无法运行。

Question

我有一个过程，旨在使用SSRS为人员列表构建数字报告，其中列A是他们的姓名，列B和C是他们今年迄今为止的数字（从1月份开始到例如5月份），列D和E是去年同期月份的相同数字。下面是该过程的示例。此外，我已更改名称和关键数据，以使其更易于理解，但这可能意味着我无法立即发现某些地方存在轻微的拼写错误。每个参数的数据示例都作为已注释的行放置在下面。

ALTER PROCEDURE [dbo].[number_comparison]
    @resource AS int
    -- retrieves a specific employee, or NULL. NULL on this parameter will retrieve a list of all employees (or all within a given type if the employee_type parameter is set)
    ,@employee_Type AS nvarchar(20)
    -- type will retrieve all employees of a certain type. NULL on this will retrieve all employees regardless of type
    ,@time AS nvarchar(6)
    -- Comes in the form YYYYMM. Cannot be NULL.
AS 
BEGIN

DECLARE @time_last_year nvarchar(6) = CONVERT(nvarchar(6), @time-100);

SELECT 
    fk_resource
    ,SUM(amount) AS sum_amount_last_year
    ,AVG(amount) AS avg_amount_last_year
INTO 
    #lastyearsnumbers
FROM
    datamart.numbers_month
INNER JOIN 
  dvh.resources
  ON
    (resources.pk_resource = fk_resource)
  AND
    (resources.pk_ressurs != -1)
WHERE
  (fk_time LIKE LEFT(@time_last_year,4)+'%')
AND
  (@resource IS NULL OR fk_resource = @resource)
AND
  (@employee_Type IS NULL OR employee_Type = @employeeType)
AND
  (fk_time < ((@time_last_year)+1))
GROUP BY
  fk_resource

SELECT
  fk_resource
  ,name
  ,SUM(amount) AS sum_amount_this_year
  ,AVG(amount) AS avg_amount_this_year
  ,TEMP.sum_amount_last_year
  ,TEMP.avg_amount_last_year
FROM
  datamart.numbers_month 
INNER JOIN 
  dvh.resource
  ON
    (resource.pk_resource = fk_resource)
  AND
    (resource.pk_resource != -1)
LEFT OUTER JOIN
  #lastyearsnumbers TEMP
  ON TEMP.fk_resource = datamart.numbers_month.fk_resource
WHERE
  (fk_time LIKE (LEFT(@time,4))+'%')
AND
  (@resource IS NULL OR fk_resource = @resource)
AND
  (@employee_Type IS NULL OR employee_Type = @employee_Type)
AND
  (fk_time < (@time+1))
GROUP BY
  fk_resource, name, TEMP.sum_amount_last_year, TEMP.avg_amount_last_year

END;

这项任务的重点在于，应该能够检索某一年月份的报告，并查看从该年1月至所选月份所累积的数字，然后将其与前一年的同一段时间进行比较。

它使用左外连接，因为有些员工是新员工，去年没有任何数据。

当我在SQL Server Management Studio中执行此过程时，它完全运行良好。我得到了所有正确的数据和包括没有去年数据的新员工在内的员工列表。

当我在SSRS中执行过程时，我只得到一个员工列表，其中只包括今年和去年都有数据的员工。几乎就像左外连接被作为内连接运行一样。

这是什么原因引起的？

- Geir K.H.

---

@vercelli，你发现了我在将所有内容重命名为更容易理解的名称时犯的一些错误。感谢你指出来。现在应该已经纠正过了。 - Geir K.H.

好的，但问题仍然存在：GROUP BY fk_resource、name、TEMP.sum_amount_last_year、TEMP.avg_amount_last_year。 - vercelli

据我所知，别名不是必需的，才能使查询正常工作。就像我说的，在SQL Server Management Studio中可以正常运行。在SSRS中也可以无错误地运行。只是连接的数据不正确。 - Geir K.H.

但是在GROUP BY fk_resource属于＃lastyearsnumbers还是datamart.numbers_month？ - vercelli

很高兴听到这个消息 ;-） - vercelli

Answer 1

感谢 @vercelli 指出我没有明确指定 group by 参数指向哪些表。SQL Server Management Studio 本身可以理解哪些是正确的表，但是在通过 SSRS 运行时似乎会出现问题。我给表取了别名，并使用别名在参数中指定了表，这似乎解决了问题。

- Geir K.H.

Answer 2

我记得在某个地方看到过（一时想不起来），报表向导不喜欢临时表。当我找到它时，我会更新评论。但与此同时，您可以尝试使用CTE（通用表达式）代替临时表或使用任何分段表来运行相同的查询吗？

我修改了您的脚本，使用了CTE，如下所示。

    ALTER PROCEDURE [dbo].[number_comparison]
        @resource AS int
        -- retrieves a specific employee, or NULL. NULL on this parameter will retrieve a list of all employees (or all within a given type if the employee_type parameter is set)
        ,@employee_Type AS nvarchar(20)
        -- type will retrieve all employees of a certain type. NULL on this will retrieve all employees regardless of type
        ,@time AS nvarchar(6)
        -- Comes in the form YYYYMM. Cannot be NULL.
    AS 
    BEGIN

    DECLARE @time_last_year nvarchar(6) = CONVERT(nvarchar(6), @time-100);

    WITH cte AS
    (
        SELECT 
            fk_resource
            ,SUM(amount) AS sum_amount_last_year
            ,AVG(amount) AS avg_amount_last_year
        FROM
            datamart.numbers_month
        INNER JOIN 
          dvh.resources
          ON
            (resources.pk_resource = fk_resource)
          AND
            (resources.pk_ressurs != -1)
        WHERE
          (fk_time LIKE LEFT(@time_last_year,4)+'%')
        AND
          (@resource IS NULL OR fk_resource = @resource)
        AND
          (@employee_Type IS NULL OR employee_Type = @employeeType)
        AND
          (fk_time < ((@time_last_year)+1))
        GROUP BY
          fk_resource
    )
    SELECT
      fk_resource
      ,name
      ,SUM(amount) AS sum_amount_this_year
      ,AVG(amount) AS avg_amount_this_year
      ,TEMP.sum_amount_last_year
      ,TEMP.avg_amount_last_year
    FROM
      datamart.numbers_month 
    INNER JOIN 
      dvh.resource
      ON
        (resource.pk_resource = fk_resource)
      AND
        (resource.pk_resource != -1)
    LEFT OUTER JOIN
      cte as TEMP
      ON TEMP.fk_resource = datamart.numbers_month.fk_resource
    WHERE
      (fk_time LIKE (LEFT(@time,4))+'%')
    AND
      (@resource IS NULL OR fk_resource = @resource)
    AND
      (@employee_Type IS NULL OR employee_Type = @employee_Type)
    AND
      (fk_time < (@time+1))
    GROUP BY
      fk_resource, name, TEMP.sum_amount_last_year, TEMP.avg_amount_last_year

    END;

- Naveen

---

你说得对；SSRS 不支持临时表，这就是为什么使用存储过程来完成的原因。@vercelli 在我的原始问题中发表的评论提供了解决方案！ - Geir K.H.