Friday, January 8, 2010

Inscope operator in SQL Reporting Services

One of the very handy operator I've come across in SQL Reporting services is Inscope operator. Quite often we come across scenarios where we need to generate dynamic crosstabbing reports based on particular field data. One of most commonly used report in those cases are matrix reports. Matrix container gives us an easy opportunity for dynamic crosstabbing. One common faced issue in this type of reports is the generation of aggregate columns in matrix row/column. By default matrix has a subtotal column which just aggregates the value from individual data values. But things gets complex when we need to do any other type of aggregation like Averaging. As there's no physical column/row present for aggregation we need a way to distinguish the aggregate columns and write required expression for them.
Fortunately for us, the Inscope operator comes to our rescue. By using Inscope operator we can identify aggegate columns and columns in group. The Inscope returns true for columns in row/column group and false for aggregates. Thus by using an expression like

=IIF(Inscope("{Row/Column} group name"),individual expn,aggregate expn)

we are able to apply any type of aggregation for the aggregate columns.
more details can be found here