SSRS 2005: matrix column subtotal w/incorrect scope
Posted on 2007-09-28
This works correctly in the BIDS tool and on one of our servers, but fails on the other two servers. The working server is actually behind by several patches, but otherwise (as far as I can tell) is identical.
When a matrix has multiple rows and multiple columns, the highest level column subtotal is in the wrong scope. Say we have three row groups (r1, r2, and r3) and two column groups (c1, c2). All five groups have subtotals enabled. We can check the scope of every cell by setting the expression of our Detail/Value textbox like so:
=IIf(InScope("r1"),"r1, ","") &
IIf(InScope("r2"),"r2, ","") &
IIf(InScope("r3"),"r3, ","") &
IIf(InScope("c1"),"c1, ","") &
When run, the lowest level "detail" boxes should be in the scope of all groups: "r1, r2, r3, c1, c2,". The subtotal of r3 is in scope for everything except for itself (r1, r2, c1, c2,), the subtotal for r2 is in scope for everything except for r2 and r3 (r1, c1, c2,), and the subtotal for r1 is only in the scope of the columns (c1, c2,). The same applies to columns: subtotals for c2 are in (r1, r2, r3, c1,) and subtotals for c1 are just the rows (r1, r2, r3,). When subtotals intersect, they are only in the scopes that match: so r2 (r1, c1, c2,) and c2(r1, r2, r3, c1,) is in (r1,c1). The intersection of r1 and c1 is in the bottom right corner and is no scope at all.
This is how it should work, and it does work this way in the design tool and our one (development) server. The other two servers (which are, unfortunately, the Test and Production servers) have a problem: the subtotal of c1 (the column running furthest along the right) is ONLY r1. Always. Which results in some ridiculously large subtotals for detail rows.
The number of rows and columns (as long as it's more than one of both) makes no difference; the problem still hits the furthest right column in the exact same way. We've changed everything we can think of, and tried multiple copies built from scratch, but the results are still consistent.