Greetings

I have an issue with using Excel pivottables to show a type 2 scd and
how Excel rolls up values. This is a parent child situation. I also
have a standard time dimension.

Take the following dimnesion

Dim
A
B

Dim after Change
A
A1
A2
B

Before the change data in the fact table is attached to A and B in the
dimension. A & B add up to give a total. Normal and expected.

After the change, A1 & A2 add up to give you A, and A and B add up to
give a total. Again, as expected

The problem arises when you try and show both a time frame that
includes before and after the change, i.e. both instances. In Excel
2003, the dim that appears after the changes work normally. For the
snapshot of the dim before the change, the values before the change do
not have anything in A1 and A2. They did not exist. But a value for
A did exist as that time. The pivot table will not show this value in
the total for A if the I have drilled down to show A1 and A2.
Further, my grand total at the bottom of the table is now missing this
value as well. When I collapse A1 and A2 into A, the value appears in
the cell as expected. Interestingly, in VStudio's cube browser, this
is exactly how it works.

Is there some type of workaround for this? Or is there some obscure
or undocumented setting I am missing? Thanks.

regards

JPH