Hello,

Could someone explain why the following MDX query returns #ERR (Foodmart
2000/Sales/MDX Sample App):

WITH MEMBER [Time].[Total] AS 'AGGREGATE({[Time].[Year].AllMembers})'
SELECT {[Time].[Total]}ON AXIS(0) FROM [Sales]

The Time dimension in Foodmart has no (All) level, which I suspect is the
problem. I don't really have anything to support my suspicion other than
observation. This same query run against a Time dimension with an (All)
level works correctly, as well as against non-Time dimensions with (All)
levels.

Is the lack of an (All) level the problem, or is it something else? Since
the (All) level is optional, is there something I can to in my queries to
properly aggregate in these situations?

Thanks!

Keith

The problem is that, since there is no [All] level for [Time], [Total]
is added by default at the [Year] level. In that case, using
[Year].AllMembers causes a recursive definition of [Total], since it is
a member of [Year].

By arbitrarily assigning [Total] as the child of a year, it is at the
[Quarter] level, so this version works:

'AGGREGATE({[Time].[Year].AllMembers})'
SELECT {[Time].[Total]}ON AXIS(0) FROM [Sales]

Or .Members, rather than .AllMembers, will work as well.


- Deepak

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

Deepak,

Thanks so much for your reply! It cleared things up nicely.

Keith

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote