MS SQL Server OLAP - cannot refer to [Dimension],[Level].Currentmember ???

Author

cannot refer to [Dimension],[Level].Currentmember ???

kary kalapanda

2005-04-21, 11:24 am

here is my query:

with member [Measures].[hcount]
as

'Count(Extract(Filte
r(
NonEmptyCrossJoin([E
mployee].[Emplid].Members,
[Dim1].[Col1].members,[Dim2].[Col2].members),
[Dim1].currentmember.properties("prop1") >
[Dim2].currentmember.properties("prop2") ) ,
[Employee])
)'

select {[Measures].[hcount]} on columns,
NonEmptyCrossJoin([E
mployee].[Emplid].Members,
[Dim1].Members,[Dim2].Members,[Dim3].Members ) on rows
from cube1

my [Dim1],[Dim2],[Dim3]
dimensions have only 1 level each and they are
[Col1],[Col2],[Col3]
respectively. Each of the levels has a property
which is "prop1","prop2" and "prop3" respectively. So is setup like
this:

[Dim1].[Col1]->property("prop1")
[Dim1].[Col2]->property("prop2")
[Dim1].[Col3]->property("prop3")

But when I run the above query I get an error saying "prop1" is an
invalid property name !!!

If on the other hand I change the query to this( I omit the level names
when referring to Dim1,Dim2 and Dim3) :

with member [Measures].[hcount]
as

'Count(Extract(Filte
r(
NonEmptyCrossJoin([E
mployee].[Emplid].Members,
[Dim1].members,[Dim2].members),
[Dim1].[Col1].currentmember.properties("prop1") >
[Dim2].[Col2].currentmember.properties("prop2") ) ,
[Employee])
)'

select {[Measures].[hcount]} on columns,
NonEmptyCrossJoin([E
mployee].[Emplid].Members,
[Dim1].Members,[Dim2].Members,[Dim3].Members ) on rows
from cube1

I get an error saying unknown dimension [Dim1].[Col1] !!!

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

Deepak Puri

2005-04-22, 8:24 pm

To correct the second error, remove references to levels - from SQL BOL,
CurrentMember applies to dimensions:

CurrentMember

Returns the current member along a dimension during an iteration.

Syntax

«Dimension».CurrentMember

So the MDX query would become:
[color=darkred]
with member [Measures].[hcount]
as

'Count(Extract(Filte
r(
NonEmptyCrossJoin([E
mployee].[Emplid].Members,
[Dim1].members,[Dim2].members),
[Dim1].currentmember.properties("prop1") >
[Dim2].currentmember.properties("prop2") ) ,
[Employee])
)'

select {[Measures].[hcount]} on columns,
NonEmptyCrossJoin([E
mployee].[Emplid].Members,
[Dim1].Members,[Dim2].Members,[Dim3].Members ) on rows
from cube1
[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

kary kalapanda

2005-04-23, 3:23 am

Deepak,

Thanks for your reply. I also realized that I was getting an error
(uknown property) on my Dim1.currentmember.properties("prop1")because I
had my all-level enabled on my dimensions. After I got rid of my
all-level my query runs fine.

Thank you so much for your help.

-Kary.

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

Sponsored Links