SELECT Statement Works In SQL Server Management Studio But Not In SSRS

Hi,

I'm attempting to extract some yearly average figures from our DB. I've written a SELECT statement in SQL Server MS which returns exactly what I need:


SELECT YEAR, CAllSource, AVG(CallTotal) AS [Average calls per day]

FROM (SELECT COUNT(CallID) AS CallTotal, DATEPART(YEAR, Recvddate) AS Year, CASE WHEN CallSource IN ('Auto Ticket', 'Email')

THEN 'Email' WHEN CallSource IN ('Phone') THEN 'Phone' ELSE 'Other' END AS CallSource

FROM Calllog where

DATEPART(MONTH, Recvddate) = 3

AND DATEPART(dw, RecvdDate) NOT IN ('7', '1')

GROUP BY RecvdDATE, callsource) as sub

GROUP BY YEAR, CallSource

ORDER BY YEAR, CallSource

The problem is that when I attempt to use this in SSRS I get the following error:

"sub.Year is not a recognised DATEPART Option".

Now as you can see, "sub.Year" is not even mentioned in the expression. However I noticed that when running the query, SSRS automatically adds "sub." before the YEAR in the section highlighted in yellow above. a) Why is it doing this, and b) does anyone know of a workaround/fix?

Thanks
Matt