Solved

SSRS 2005: matrix column subtotal w/incorrect scope

Posted on 2007-09-28
4,281 Views
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, ","") &
IIf(InScope("c2"),"c2, ","")

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.

Any ideas?
0
Question by:Rakafkaven

LVL 4

Author Comment

This is apparently a known issue with SP2 of Reporting Services: http://forums.microsoft.com/MSDN/showpost.aspx?postid=2215374&siteid=1

Adding another dummy column group did indeed work.

Points will go to anyone who has other workarounds, explanations, or just anything interesting to add.  Or they'll disappear when the mods revert them.  Whatever.
0

LVL 1

Accepted Solution

PAQed with points refunded (500)

Computer101
0

Featured Post

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve thâ€¦
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.