SSRS 2005: matrix column subtotal w/incorrect scope

Posted on 2007-09-28
Last Modified: 2012-05-05
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?
Question by:Rakafkaven
    LVL 4

    Author Comment

    This is apparently a known issue with SP2 of Reporting Services:

    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.
    LVL 1

    Accepted Solution

    PAQed with points refunded (500)

    EE Admin

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    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.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now