Search code examples
matrixssrs-2008-r2ssrs-tablix

How can I get correct totals in SSRS matrix?

The matrix below has column totals under the red line. The pivoted column group columns are highlighted in yellow to the right of the vertical red line. The columns in the white to the left are not pivoted.

So it looks like the totals under the pivoted yellow columns are correct,

but the totals under the regular columns are totally wrong.

Those are simple =Sum(Fields!columnX.Value) totals in a group total row.

enter image description here

Matrix design is as follows (wherever you see "Expr" it is simply that Sum multiplied by a temporarily used constant 1, except where I deleted that from pour_weight for simplicity):

enter image description here

It appears that SSRS totals the left columns BEFORE pivoting the right columns, which is a total disaster.

What am I doing wrong?

Solution

  • Ended up creating a 2nd dataset without detail columns and with the Sums of each on the remaining columns, then using a Lookup function in the matrix cells to find the correct group's correct total.