I am trying to show values of a set, the subtotals of their parents
(using Generate/VisualTotals), and the totals of those same parents
all on one axis. Here is the concept in Adventure Works:


WITH

// Cities I'm interested in
SET CitySet as
{
[Geography].[Geography].[City].&[Irvine]&[CA],
[Geography].[Geography].[City].&[Oakland]&[CA],
[Geography].[Geography].[City].&[Palo Alto]&[CA],
[Geography].[Geography].[City].&[Sacramento]&[CA],
[Geography].[Geography].[City].&[Denver]&[CO],
[Geography].[Geography].[City].&[Englewood]&[CO],
[Geography].[Geography].[City].&[Westminster]&[CO],
[Geography].[Geography].[City].&[Destin]&[FL]
}

// States for cities
SET StateSet as
Generate
(
CitySet,
{[Geography].[Geography].currentmember.parent}
)

// State subtotals
SET StateSubtotalSet as
Generate
(
VisualTotals
(
Hierarchize
(
[CitySet] + [StateSet]
),
"* - Subtotal"
)
,
[StateSet]
)

SELECT
[Measures].[Reseller Sales Amount] on 0,
Hierarchize
(
{
CitySet,
StateSet,
StateSubtotalSet
}
) on 1

FROM
[Adventure Works]


The problem is, once I define the StateSubtotalSet set using the
Generate/VisualTotals method, it affects the StateSet set by
effectively replacing StateSet with StateSubtotalSet. Is this
expected behavior? Should I be going about this a different way?

Thanks,
Mitch