List the Children of Members of a SET

we have concept of spatial hierarchy of

 

division > market > submarket

 

not divisions have members @ market level though.

 

having created set called [top 30], contains 30 divisions, i'd able produce report users can drilldown division market in cases there market members.

 

the following mdx returns need @ division level

 

code snippet

select { [time].[quarter].&[q1 2007]:[time].[quarter].&[q4 2007] } on columns ,

{ [top 30] } on rows

from [qms]

where ( [measures].[% occupancy] )

 

 

houston

las vegas

la county

indianapolis

 

what

 

houston

las vegas

la county

- la central

- la north

- la south

indianapolis

 

as of 4 divisions above, la county has members @ market level.

is possible do... ?

it's ok, got it...

 

code snippet

select { [time].[quarter].&[q1 2007]:[time].[quarter].&[q4 2007] } on columns ,

hierarchize(

{

descendants([top 30],[division].[division] ),

descendants([top 30],[division].[market] )

}

) on rows

from [qms]

where ( [measures].[% occupancy] )

 

 

 

SQL Server  >  SQL Server Analysis Services