Hi,

Let us execute following MDX query against AW database in AS2005 build 1399
(RTM)

with
member [Product].[Product Categories].[Road and Mountain] as
aggregate([Product].[Product
Categories].[Subcategory].&[2],[Product].[Product
Categories].[Subcategory].&[1])
member [Product].[Product Categories].[Mountain and Road] as
aggregate([Product].[Product Categories].[Subcategory].&[1],
[Product].[Product Categories].[Subcategory].&[2])

select
{[Measures].[Customer Count], [Measures].[Internet Order Count]} on
columns,
{[Product].[Product Categories].[Category].&[1],
[Product].[Product Categories].[Category].&[1].children,
[Product].[Product Categories].[Road and Mountain],
[Product].[Product Categories].[Mountain and Road]
}
on rows
from [Adventure Works]The result is incredible Customer CountInternetOrder CountBikes 9,132 15,205Mountain Bikes 4,089
4,970Road Bikes 6,397 8,068Touring Bikes 2,143 2,167Road
and Mountain 4.089 4.970Mountain and Road 6.397 8.068Is it right? I
doubt it is not.Thanks,Vladimir Chtepa

Hi Vladimir,

You forgot to put curly brackets around the set inside both your Aggregate
functions! However, that doesn't mean that there isn't any weird behaviour
here. The working version of your query is:


with
member [Product].[Product Categories].[Road and Mountain] as
aggregate({[Product].[Product
Categories].[Subcategory].&[2],[Product].[Product
Categories].[Subcategory].&[1]}, MEASURES.CURRENTMEMBER)
member [Product].[Product Categories].[Mountain and Road] as
aggregate({[Product].[Product
Categories].[Subcategory].&[1],[Product].[Product
Categories].[Subcategory].&[2]}, MEASURES.CURRENTMEMBER)

select
{[Measures].[Customer Count], [Measures].[Internet Order
Count],[Measures].[Internet Sales Amount] } on columns,
{[Product].[Product Categories].[Category].&[1],
[Product].[Product Categories].[Category].&[1].children,
[Product].[Product Categories].[Road and Mountain],
[Product].[Product Categories].[Mountain and Road]
}
on rows
from [Adventure Works]

Note that you need to put in Measures.Currentmember as the second parameter
to the Aggregate function to get it to work - for some reason, without it
both calculated members return null.

HTH,

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Vladimir Chtepa" wrote:

Thanks, Chris.

You have answered my second question related to second parameter in the
aggregate function.
It's strange behavior of aggreagate withou 2-nd parameter widerspricht
contradicts to the BOL.

Quote BOL
"If Numeric_Expression is not specified, this function aggregates each
measure within the current query context using the default aggregation
operator specified for each measure."

Thanks,
Vladimir Chtepa

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:7751880B-EDDE-4EBC-A2A0-5D3666F32988 (AT) microsoft (DOT) com...

You don't need to put the second parameter to Aggregate function (in fact,
this is how almost every client tool uses it on multiselect and grouping).
The fact that it returns NULL without it - is an indication of the bug,
perhaps the same bug that Aggregate over distinct count sometimes return
wrong results in Yukon RTM. I am running SP1 version of AS, and it is fixed
there.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =