When I try to do a Hirerachize of 2 sets that have cross
joins in them, VisualTotals does not seem to work. when
the following query is executed


select {

Hierarchize({

CROSSJOIN( { VisualTotals( {[Customers].[All Customers].
[USA].children} , "** SubTotal -
*") } , { VisualTotals(
{[Product].[All Products].[Drink].children} , "**
SubTotal - *") } ) +

CROSSJOIN( { VisualTotals( { EXCEPT( {[Customers].
[All Customers].[USA].[CA].children}, { [Customers].[All
Customers].[USA].[CA].[Arcadia] } ) } , "**
SubTotal - *") } , { VisualTotals( {[Product].[All
Products].[Drink].[Dairy].children} , "** SubTotal -
*") } )

})

}

on 0

from sales


The value retured for [CA].[Dairy] does not seem to
subtract the value for Arcadia.


(1) Am I doing the query correct?
(2) Is there a workaround for the above situation?

thanks

Tom

There are some issues with the query as stated:

- VisualTotals() does not do anything when applied to a set of sibling
members, as is the case throughout the query.

- The only child of [Drink].[Dairy] is also named [Dairy].
This may cause confusion when using MDX Sample App.

Not sure what the original intent was, but I have modified the query to
correctly sub-total [CA], omitting [Arcadia]:

Hierarchize(

CROSSJOIN([Customers].[All Customers].[USA].children,
[Product].[All Products].[Drink].children)

+

CROSSJOIN(VisualTotals({[Customers].[All Customers].[USA].[CA]}
+ EXCEPT([Customers].[All Customers].[USA].[CA].children,
{[Customers].[All Customers].[USA].[CA].[Arcadia]}),
"** SubTotal - *"),
[Product].[All Products].[Drink].[Dairy].children))

on 0

from sales
- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!