AS not aggregating correctly

[bdbits]

Imagine if you will the following AS report based on an employee cube (not real data, hope the formatting works):


Emp Count    | 2011  | 2010   | 2009  | All Years
---------------------------------------------------
Native Am    |   83  |     65 |    17 |    123
Hispanic     |   77  |     81 |    14 |    137
White        |   63  |     57 |    15 |    112
All Ethnic   |  223  |    203 |    46 |    372


The measure is a category count that uniquely identifies an employee. As you may have noticed, the row totals (last column) are not correct. The columnar totals (the bottom row) are correct. The individual nodes in the crosstab are correct, and if I create a similar crosstab in Report Studio the totals are all correct. I am really at a loss at to why this is happening. I tried exporting it to RS to look at it, but frankly that is a mess and undecipherable to me. I have checked the model several times and it appears to be correct as far as I can tell. There does not appear to be a consistency to how much the totals are off, e.g. doubled or divided, consistent amount or %, etc.

Any thoughts on where to look for the problem?

[cognostechie]

When you create the same Crosstab in RS, RS does not calculate the Totals by itself. You probably created the Total column manually in which
case RS will sum the values of the Years resulting in correct aggragation.

In AS, it is probably showing the value of the 'All Years' dimension (top level) and not aggregating. It might mean that
while building the cube, the catagory count is correctly aggregated till the individual year level but not for All Years.
A workaround is to delete the All years column from AS and create a calculated column to sum up the years but that
would not fix the cube so from a Power User perspective, you will need to look at the cube model more closely.

From my experience, I know that the category count works and aggregates correctly at all levels.