Hi,

The VisualTotals function does not appear to work if the set being operated
on is not ordered in its natural hierarchy. Can anyone confirm whether or
not this is by design?

In the following two MDX queries, the values returned for USA and each state
are the sum of sales for all their children despite the use of VisualTotals.
However, the second query behaves as expected.

-- parent totals do not reflect selected child members

select
{ [measures].[unit sales] } on axis(0),
non empty
visualtotals(
{ [customers].[country].members,
[customers].[state province].members,
[customers].[usa].[ca].[arcadia], [customers].[usa].[or].[beaverton],
[customers].[usa].[wa].[ballard]
})
on axis(1)
from sales

-- parent totals reflect the child members in the set

select
{ [measures].[unit sales] } on axis(0),
non empty
visualtotals(
hierarchize(
{ [customers].[country].members,
[customers].[state province].members,
[customers].[usa].[ca].[arcadia], [customers].[usa].[or].[beaverton],
[customers].[usa].[wa].[ballard]
}))
on axis(1)
from sales

Peter

This behavior of VisualTotals() doesn't appear to be discussed in the
SQL Server MDX reference entry:

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agmdxfunctions_8l0z.asp

But it is pointed out on page 383 of the "MDX Solutions" book: "..The
order of the appearance of members is important.."; so this behavior is
known. Whether it is by design can be better answered by [MS] folks.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***