OVER SQL Construct not supported in SSRS

Question

I have a query with which I am trying to create a dataset in SSRS, but I am getting an error saying,

The OVER SQL construct or statement is not supported.

The query I am using is as below:

SELECT AM, REP, PRIM_SPEC, SUM(TOT_CALL)
FROM (
       SELECT AM, REP, SUM(TOT_CALL) as TOT_CALL, 
       CASE 
           WHEN ROW_NUMBER() OVER (PARTITION BY REP ORDER BY SUM(TOT_CALL) DESC) > 5 
           THEN 'Other'
           ELSE prim_spec
       END AS prim_spec
       FROM DEMO_CALL
       WHERE PERIOD >= @Mese
       AND (REP IN (@REP)) 
       AND (AM = @AM)
       GROUP BY AM, REP, prim_spec
) A
GROUP BY AM, REP, PRIM_SPEC
ORDER BY 1,2,4 DESC

How can I use my OVER SQL Construct for this issue?

Answer 1

This is not an issue with SSRS, but with the SQL statement itself. You cannot compare your ROW_NUMBER before it has been generated. Try this:

SELECT distinct AM, REP, TOT_CALL, 
CASE 
   WHEN RN > 5 
   THEN 'Other'
   ELSE prim_spec
  END AS prim_spec
FROM (
 SELECT AM, REP, SUM(TOT_CALL) as TOT_CALL, ROW_NUMBER() OVER (PARTITION BY REP ORDER BY SUM(TOT_CALL) DESC) as RN
 FROM DEMO_CALL
 WHERE PERIOD >= @Mese
   AND (REP IN (@REP)) 
   AND (AM = @AM)
 GROUP BY AM, REP
) DEMO_CALL
GROUP BY AM, REP, prim_spec
ORDER BY 1,2,4 DESC