Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

crossjoin and dimension

Status
Not open for further replies.

jehst

Programmer
Joined
Oct 3, 2003
Messages
4
Location
US
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
 
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 :


That's all, A+
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top