reporting services - Substitute command for OVER in SSRS

Question

How can I implement the OVER() command in SSRS 2008?

Is there a trick to it, or am I stuck?

And if I am indeed not able to use OVER(), then how can I use PARTITION in a SSRS report?

OVER() satisfies my requirements, and Telerian (another member) was very helpful by pointing it out - but SSRS doesn't seem to be able to use it for reporting.

Every time I use a variant of OVER() or PARTITION in the SSRS SELECT statement, I get rebuffed by the system - any thoughts or work-arounds?

The error message that I was receiving from the 2008 Query Studio was:

The OVER SQL construct or statement is not supported.

Answer 1

This may be due to not including a column alias for the summarised column - the following SQL generates a usable dataset for me in SSRS:

select d.*, 
       ROW_NUMBER() over (partition by date order by id) rn 
from dbo.myTable d