I came across two little learning experiences this week. Here's hoping someone else is stupid enough to make the same mistakes as me and look it up!


I was trying to add an expression to a table that relied on Previous(Fields!FieldName.Value) and getting an error returned from SSRS:

The Value expression : has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

This confused my greatly because I was sure I'd used it in the past without any qualifier, but instead tried to continue by adding the containing group name, as in Previous(Fields!FieldName.Value, "GroupName"). It didn't help, I was still getting the same error.

Which is when I realised I was doing this in a row that was part of a group, rather than a detail section. If you think about it, there might be multiple rows in that group and it doesn't know which one it should get. What I eventually twigged on, was that it wanted me to use an aggregate like Previous(First(Fields!FieldName.Value, "GroupName")). This worked and did what I wanted.

Note: Previous() works without that error if you're using it in a matrix where the column isn't part of the column group data; e.g. a proper heading.

It was a bit of a duh moment, but IMHO a smarter error message would have been nice.

Cross Apply

I've been using Cross Apply in some queries lately, without realising that if it returns no rows then you won't get any rows on the left either; I've always wanted Outer Apply instead. This forced me to look it up and learn a little more about the difference between Outer Apply, Cross Apply, and Cross Join.

Luckily, this was in development and not production