crossjoin and dimension

[jehst]

hi,

i want to have 3 columns, two of them from the same dimensions. As i know that it's not possible to crossjoin members from the same dimension, i tried to declare them as measures, but it doesn't seem to work. I don't know wether it is due to a syntax or another problem...does someone has an idea???

description:
the two members are from the [geography] dimension
they are:[geography].[area] and [geography].[plant]
the final table i d like to obtain looks like that:


AREA PLANT avail
area1 plant1 14
area1 plant2 13
area1 plant3 12

area2 plantx 11
area2 planty 44
area2 plantz 1
area2 plantr 0
.
.
.

plants 1,2,3 are situated in area 1
plants x,y.. in area 2

my querie would then be:

with

member[Measures].[Avail exc] as '([Measures].[Aexc_V_YTD],[Indicator type].[All indicators types].[Reliability].[Production]) '
member [Measures].[zone] as '([Geography].currentmember.parent.name)'
member [Measures].[plant] as '([Geography].currentmember.name)'

select
{ [Geography].[Plant].members
}

on rows,
{
[Measures].[zone] ,
[Measures].[plant] ,
[Measures].[Avail exc]
}
on columns



from [....]
where ([Data type].[Actual],[Time period].[2003])



thank you

 

[CodA75]

Ok your query seems ok to me exept your two measures [zone] and [plant]; I explain : what you want is to retrieve some level properties ie name and parent name so why so you put them in tuple ? they are not data to be crossed ...
Try this instead :

member [Measures].[zone] as '[Geography].currentmember.parent.name'
member [Measures].[plant] as '[Geography].currentmember.name'

Please, let me know if it works for you ...
You can also check this site on the net that present several manner to cope with this problem :

http://www.scdi.org/~avernet/misc/olap-mdx/

That's all, A+