CA PPM Advanced Report returns 6632 error when running report

0

Hello,

I created a new report in JasperSoft Studio (Which runs perfectly fine in preview mode, and in SQL Server Management Studio)..

However, I get an error when trying to run from Clarity Advanced Reporting.

Here is part of the Query that I believe is causing the issue (Removed rest due to containing  secured information) :

 

SET NOCOUNT ON
DECLARE @start_date DATE = 'Jan 1, 2011';
DECLARE @end_date DATE = dateadd(YEAR,40,@start_date);
DECLARE @Nwkdy AS TABLE (dtday DATE);
 
WITH  AllDays
    AS ( 
 
     SELECT @start_date AS DtDay
          , MONTH(@start_date) AS mnth
          , DAY(@start_date) AS dayofM
          , DATENAME(weekday, @start_date) AS dtname
        UNION ALL
     SELECT  
            DATEADD(DAY, 1, DtDay)
          , MONTH(DATEADD(DAY, 1, DtDay)) AS mnth
          , DAY(DATEADD(DAY, 1, DtDay)) AS dayofM
          , DATENAME(weekday,DATEADD(DAY, 1, DtDay)) AS dtname
             FROM   AllDays
             WHERE  DtDay < @end_date  
             )
 
INSERT INTO @Nwkdy
   SELECT DtDay FROM AllDays 
    WHERE  
          ( 
                (mnth =1 AND dayofM = 1)                                              -- new years
        OR      (mnth =5 AND dayofM >= 25 AND dtname = 'Monday')                      -- memorial day
        OR      (mnth =7 AND dayofM = 4)                                              -- Indendence day
        OR      (mnth =9 AND dayofM  <= 7 AND dtname = 'Monday')                      -- Labour day
        OR      (mnth =11 AND dayofM BETWEEN 22 AND 28 AND dtname = 'Thursday')       -- Thanksgiving
        OR      (mnth =12 AND dayofM = 25)                                            -- Xmas
        )
        OPTION (MAXRECURSION 0)

Any help would be appreciated.

AGAIN : This runs perfectly fine in JasperSoft Studio Preview AND SQL Server Management Studio.

camnott's picture
86
Joined: Mar 10 2017 - 6:24am
Last seen: 1 year 3 months ago

0 Answers:

No answers yet
Feedback