SSRS slow then Error

Question

I Create Chart Report using SSRS report and use this report in MS CRM

My report About Summary in parameter section when run user can choose report display summary per day / month / year or quater

Then report work correctly when a little rows but when I choose display per day and choose date range between 2014-01-01 and 2015-03-23 report run too long and then I get this error

Then I run Query in Sql server management studio It's run 00:00:00 sec and return 1814 rows

Here is my Query

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

here is my data set and report design

Thank you.

B Woods B Woods · Accepted Answer · 2015-03-23T14:07:09

This is probably an issue with too many records and your report and/or database timing out. I believe this to be the case because the report runs when you limit the data set to just a few records.

I would check this resource for more help in fixing this issue.what is the default timeout processing option in SSRS Server

SSRS slow then Error

1 Answers