Reporting Service - Using InScope to do custom subtotals
Imagine you're doing a report of Employee sales figures. You put
together a quick Matrix report in Reporting Services, throwing Employee
on the left and Fiscal Quarter across the top, with Sale amount in the
data area. Fabulous, you've just created a useful report in no
time. But now your boss says, "I want the same sort of report showing sales by employee and quater,
but for the subtotals on the bottom and the right I want to see how many employees
missed their quota each quarter and how many times a particular
employee has missed their quota. Now what do you do?
Reporting Services handy automatic subtotaling isn't going to work.
I discovered this week that the InScope function comes to the
rescue! You see the subtotals at the bottom of the matrix aren't
in the scope of your row group(s) and the subtotals on the right edge
of the matrix aren't in the scope of your column group(s) so you can
use an expression like this in your datacell to get sales amount summed
in the data cells and missed quotas summed in the subtotals:
=IIF(InScope('employee') And InScope('quarter'),
SUM(Fields!sales_amount.Value), SUM(IIF(SUM(Fields!sales_amount.Value)
< Fields!sales_quota.Value, 1, 0)))
Assuming you have 2 groups named 'employee' and 'quarter', this
expression says if I'm in the scope of 'employee' and in the scope of
'quarter' (aka I'm in a data cell) then spit out the sum of
sales_amount for my groups. If I'm not (aka I'm in a subtotal)
then instead sum up the # of cells where the quota wasn't met.
I hope you've enjoyed my first blog post, it should at least be more useful than "Hello World"!
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using