2

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.
William M-B
  • 321
  • 4
  • 13
  • Which RDBMS (SQLServer, Oracle, MySQL) are you reporting from? What error message are you getting? –  Jan 27 '12 at 08:30
  • I am using SQL Server 2008, and the error message is that OVER() is not supported - but about 25% of the time it begins to run the report in SSRS 2008R2, but stalls out and gives me the screen of death. – William M-B Jan 27 '12 at 14:14
  • Have you given the summarised column a column alias? –  Jan 27 '12 at 14:26

1 Answers1

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
  • 1
    It ends up that the issue was in the SSRS query studio, which was rejecting my OVER() statement. The report runs fine, but if I load up the query studio and go to edit the SQL code, I get an 'unsupported' message. It doesn't stop the actual report from running, but did give it hiccups (I don't know what I had in the code to make it crash repeatedly. It may have been something like a table lock, not SSRS). On the other hand, your result is exactly what I ended up using! I found that by sorting the date columns by ASC and DESC, I can select the first row, kind of a rough LASTDATE() command. – William M-B Jan 27 '12 at 19:10