Hi,

I am getting the following error.
"Members belong to different hierarchies in the function."

Basically i am trying to build Region Level Member from a State Level Member
in the MDX statement. Then count the number of invoices generated in that
region.

Please help with the MDX statement to achieve this.


WITH
MEMBER [Measures].[Count] as
'count(exists([sales].[invoice_id].MEMBERS,{[tIME].&[2006]}, "SALES MEASURE
GROUP"))'
MEMEBER [Geography].[State_Cd].[State Set] as
'AGGREGATE ({[Geography].[State_Cd].&[CT],[Geography].[State_Cd].&[MA]})'

MEMEBER [Geography].[Region_Cd].[Region Set] as
'AGGREGATE ({[Geography].[State_Cd].[State Set]
,[Geography].[Region_Cd].&[NE]})'

Select {[Measures].[Count]} on columns
from
[SALES MODEL]
WHERE
([Geography].[Region_Cd].[Region Set] )

Thanks,

Prasad.

A couple of observations:

- If [Geography].[Region_Cd] and [Geography].[State_Cd] are attributes
which both appear in a user hierarchy like [Geography].[Region-State],
use this single heirarchy.

- Aggregating a calculated member may be problematic


An example of a user hierarchy in Adventure Works:

Member [Geography].[Country].[Country Set] as
Aggregate({[Geography].[State-Province].&[AL]&[US],
[Geography].[State-Province].&[AZ]&[US],
[Geography].[Country].&[Canada]})
select {[Measures].[Reseller Sales Amount]} on 0
from [Adventure Works]
where [Geography].[Country].[Country Set]
----------------------------------------------------
Members belong to different hierarchies in the function.
Member [Geography].[Country].[Country Set] as
Aggregate({[Geography].[Geography].[State-Province].&[AL]&[US],
[Geography].[Geography].[State-Province].&[AZ]&[US],
[Geography].[Geography].[Country].&[Canada]})
select {[Measures].[Reseller Sales Amount]} on 0
from [Adventure Works]
where [Geography].[Country].[Country Set]
----------------------------------------------------------
Reseller Sales Amount
$15,855,940.20

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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