SSRS缓慢，然后出现错误

Question

I使用SSRS报告创建图表报告，并在MS CRM 中使用此报告

当运行时，我的报告关于参数部分的摘要用户可以选择每天/月/年或季度的报告显示摘要

当有几行时，报告工作正常，但当我选择每天显示并选择2014-01-01和2015-03-23之间的日期范围时，报告运行时间过长，然后我得到了这个错误

然后我在Sql服务器管理工作室中运行Query它运行00:00:00秒并返回1814行

这是我的查询

DECLARE @paramSdate AS DATE = (SELECT CAST('2014-01-01' AS DATE))
DECLARE @paramNdate AS DATE = (SELECT CAST('2015-03-23' AS DATE))
DECLARE @sdate AS DATE 
    ,@edate AS DATE 
SET @sdate = @paramSdate 
SET @edate = @paramNdate
CREATE TABLE #tmp (x DATE,link INT) 
WHILE @sdate <= @edate 
BEGIN 
    INSERT INTO #tmp VALUES(@sdate,1) 
    SET @sdate = DATEADD(DAY,1,@sdate)  
END 
SELECT  FORMAT(t.x,'dd/MM/yyyy','TH-th') AS [x] 
    ,h.name 
    ,ISNULL(cc.cnt,0) AS [cnt] 
FROM #tmp t 
LEFT JOIN ( 
    SELECT 2 AS [type] , 'New Visit' AS [name] ,1 AS [link] 
    UNION 
    SELECT 3 AS [type] , 'Re-Visit' AS [name] ,1 AS [link] 
    UNION  
    SELECT 4 AS [type] , 'Book' AS [name] ,1 AS [link] 
    UNION  
    SELECT 5 AS [type] , 'Total' AS [name] , 1 AS [link] 
) h ON t.link = h.link 
LEFT JOIN ( 
        SELECT COUNT(*) AS [cnt] 
     ,CAST(v.new_contactdate AS DATE) AS [d]
            ,v.new_type 
        FROM [SCASSET_MSCRM].[dbo].[new_VisitHistory] v with(nolock) 
        WHERE v.new_type IN (2) 
        AND v.new_projectid IN ('CB2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D42115AC-633F-E411-80DD-F01FAFE5E7C0'
,'DE2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'B92115AC-633F-E411-80DD-F01FAFE5E7C0'
,'032215AC-633F-E411-80DD-F01FAFE5E7C0'
,'EE2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'EF2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'F02115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D32115AC-633F-E411-80DD-F01FAFE5E7C0'
,'BD2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'BA2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D82115AC-633F-E411-80DD-F01FAFE5E7C0'
,'EB2115AC-633F-E411-80DD-F01FAFE5E7C0') 
        AND CAST(v.new_contactdate AS DATE) BETWEEN @paramSdate AND @paramNdate 
        GROUP BY v.new_type 
     ,CAST(v.new_contactdate AS DATE) 
        UNION 
        SELECT COUNT(*) AS [cnt] 
     ,CAST(v.new_contactdate AS DATE) AS [d]
            ,'3' AS [new_type] 
        FROM [SCASSET_MSCRM].[dbo].[new_VisitHistory] v with(nolock) 
        WHERE v.new_type IN (3,4) 
        AND v.new_projectid IN ('CB2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D42115AC-633F-E411-80DD-F01FAFE5E7C0'
,'DE2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'B92115AC-633F-E411-80DD-F01FAFE5E7C0'
,'032215AC-633F-E411-80DD-F01FAFE5E7C0'
,'EE2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'EF2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'F02115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D32115AC-633F-E411-80DD-F01FAFE5E7C0'
,'BD2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'BA2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D82115AC-633F-E411-80DD-F01FAFE5E7C0'
,'EB2115AC-633F-E411-80DD-F01FAFE5E7C0') 
        AND CAST(v.new_contactdate AS DATE) BETWEEN @paramSdate AND @paramNdate 
        GROUP BY v.new_type 
     ,CAST(v.new_contactdate AS DATE) 
        UNION  
        SELECT COUNT(*) AS [cnt] 
     ,CAST(s.new_reservationdate AS DATE) AS [d] 
            ,'4' AS [new_type] 
        FROM [SCAsset_MSCRM].[dbo].[new_salehistory] s with(nolock) 
        WHERE s.new_projectId IN ('CB2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D42115AC-633F-E411-80DD-F01FAFE5E7C0'
,'DE2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'B92115AC-633F-E411-80DD-F01FAFE5E7C0'
,'032215AC-633F-E411-80DD-F01FAFE5E7C0'
,'EE2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'EF2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'F02115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D32115AC-633F-E411-80DD-F01FAFE5E7C0'
,'BD2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'BA2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D82115AC-633F-E411-80DD-F01FAFE5E7C0'
,'EB2115AC-633F-E411-80DD-F01FAFE5E7C0') 
        AND CAST(s.new_reservationdate AS DATE) BETWEEN @paramSdate AND @paramNdate 
        GROUP BY 
     CAST(s.new_reservationdate AS DATE) 
        UNION 
        SELECT COUNT(*) AS [cnt] 
     ,CAST(v.new_contactdate AS DATE) AS [d] 
            ,'5' AS [new_type] 
        FROM [SCAsset_MSCRM].[dbo].[new_VisitHistory] v with(nolock) 
        WHERE v.new_projectId IN ('CB2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D42115AC-633F-E411-80DD-F01FAFE5E7C0'
,'DE2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'B92115AC-633F-E411-80DD-F01FAFE5E7C0'
,'032215AC-633F-E411-80DD-F01FAFE5E7C0'
,'EE2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'EF2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'F02115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D32115AC-633F-E411-80DD-F01FAFE5E7C0'
,'BD2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'BA2115AC-633F-E411-80DD-F01FAFE5E7C0'
,'D82115AC-633F-E411-80DD-F01FAFE5E7C0'
,'EB2115AC-633F-E411-80DD-F01FAFE5E7C0') 
        AND v.new_type IN (2,3,4) 
        AND CAST(v.new_contactdate AS DATE) BETWEEN @paramSdate AND @paramNdate 
        GROUP BY 
 CAST(v.new_contactdate AS DATE) 
) cc ON cc.new_type = h.[type] 
 AND CAST(cc.d AS DATE) = CAST(t.x AS DATE) 
ORDER BY t.x 
OPTION(RECOMPILE) 
DROP TABLE #tmp

这是我的数据集和报表设计

谢谢。

Answer 1

这可能是由于记录太多以及报表和/或数据库超时造成的问题。我相信会出现这种情况，因为当您将数据集限制为几个记录时，就会运行报告。

我会查看此资源以获得解决此问题的更多帮助。SSRS服务器中的默认超时处理选项是什么