Page 1 of 1

exclude field in aggragation

Posted: Mon May 16, 2011 11:06 am
by nko83
i everybody.

I have a problem to execute a MDX query.
I have a hierarchy Sector->Coil->Campaign and a mesure KPI4 for each Sector.

A coil is composed by 20 Sector, so I would to aggregate on Coil level, but excluding the first and last Sector of each coil (i.e.
number 1 and 20)

What is the right MDX query?

regards
nico

Re: exclude field in aggragation

Posted: Fri May 20, 2011 7:36 am
by Marcus Scherer
this one should do it, once again based on the mondrian example.
Assuming you want to always omit the first and last sector.

Code: Select all


with member [Measures].[coil_rest] as 'Sum(
EXCEPT(Descendants([Store].[Store State].CurrentMember,[store].[store city]), 
{[Store].[Store city].CurrentMember.FirstChild, [Store].[Store city].CurrentMember.LastChild}), 
[Measures].[Unit Sales])'


select {[Measures].[Unit Sales], [Measures].[coil_rest]} ON COLUMNS,
  {[Store].[Store State].Members} ON ROWS
from [Sales]
where [Time].[1997]

Re: exclude field in aggragation

Posted: Wed Apr 18, 2018 8:42 am
by Kimberybery
For more details on MDX query basics and MDX-SQL comparison, see the MDX for Everyone guide, or the official MDX documentation by MSDN.