Problem with CURRENTMEMBER on multi select data filter - Please help

[siva]

Hi,

Iam using mdx with linkMember function.
linkMember(Product.CurrentMember, UsageUnitsProduct.[Product Name])

But this throws error where data filter is used on Product dimension
with some product selections. CurrentMember is culprit.

Error message "The MDX funciton currentmember failed because the
coordinate Product contains a set".

I understand there is a article by mosha on this, But not able to get
any solution for this from his article.

Please help.

Also in a different query iam using the following

IIF([CreatedTime].[Time_Fiscal].CurrentMember.Level is
[CreatedTime].[Time_Fiscal].[Month],........

Here Iam just checking if current member is at level month, if so my
logic is different.

Same problem hits this query, When CreatedTime dimension is used as
data filter, Same above mentioned error comes becuase of Currentmember
usage.

Please please do help me on this.

[Deepak Puri]

Assuming that it occurs when multiple members of Product or of [Fiscal
Time] are selected, this blog entry by Mosha explains the error:

http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friend
ly_mdx.aspx
Writing multiselect friendly MDX calculations
..
The exact error message for the error says:

The MDX function CURRENTMEMBER failed because the coordinate for 'Store
State' attribute contains a set.
..
Current coordinate in the cube in fact is not limited by single value in
each attribute. Some attributes can have multiple values - i.e. sets.
(Again, it is actually even more generic then that, since sets can
freely cross attribute and dimension boundaries, but for the subject of
multiselects the above is enough). The sets in the coordinate are
preserved throughout all the calculation execution. Many MDX functions
don't have problems with it, however when it comes to things which rely
on hierarchy current member - such as Store.CurrentMember expression -
it doesn't know what to do - since there are multiple current members
for that coordinate. While theoretically (and perhaps in some future
version) CurrentMember can be generalized to work over sets - in AS2005
it simply cannot work, and therefore returns abovementioned error -
current coordinate contains a set.

Now, equipped with all this knowledge we can start thinking how to
rewrite the expression for our calculated measure in such a way that it
will continue to work and work correctly in the presence of sets in the
coordinates on Store hierarchy. The rewrite is actually quite easy, once
we notice, that Exists function and EXISTING operator are generalization
of hierarchy related functions such as Descendants.
..
So, since the .CurrentMember can't directly return a set (in AS2005),
you have to use Existing or Exists() to compute the set of current
members. The exact expression will depend on what you're trying to
compute, and how you expect it to work when multiple members are
selected - can you provide more details of the usage scenarios?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

[siva]

Thanks for your reply deepak. My usage scenario is something like this.


I have a measure group called usageunits which has dimension
UsageUnitsProduct. There I have measure called usageunits.

I have another measure group called SR whose grain is something else.
This measureGroup has dimension called product. Now based on the
product dimension selected , For each current member selected on
product , I need to get usageunits from the UsageUnits measure group.
For this purpose Iam using linkMember function to link
UsageUnitsProduct hierarchy with the current selection(member) of
Product dimension.

Not able to imagine how to handle this during multi selection.

Similarily ,

In some other function , Iam using this code.

IIF([CreatedTime].[Time_Fiscal].CurrentMember.Level is
[CreatedTime].[Time_Fiscal].[Month],........

Iam just checking whether currentmember level is month. It fails during
filter selection of Time_Fiscal. Not sure how to handle this.

Thanks for your help in advance.

Regards,
Siva

Post by Deepak Puri
Assuming that it occurs when multiple members of Product or of [Fiscal
http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friend
ly_mdx.aspx
Writing multiselect friendly MDX calculations
..
The MDX function CURRENTMEMBER failed because the coordinate for 'Store
State' attribute contains a set.
..
Current coordinate in the cube in fact is not limited by single value in
each attribute. Some attributes can have multiple values - i.e. sets.
(Again, it is actually even more generic then that, since sets can
freely cross attribute and dimension boundaries, but for the subject of
multiselects the above is enough). The sets in the coordinate are
preserved throughout all the calculation execution. Many MDX functions
don't have problems with it, however when it comes to things which rely
on hierarchy current member - such as Store.CurrentMember expression -
it doesn't know what to do - since there are multiple current members
for that coordinate. While theoretically (and perhaps in some future
version) CurrentMember can be generalized to work over sets - in AS2005
it simply cannot work, and therefore returns abovementioned error -
current coordinate contains a set.
Now, equipped with all this knowledge we can start thinking how to
rewrite the expression for our calculated measure in such a way that it
will continue to work and work correctly in the presence of sets in the
coordinates on Store hierarchy. The rewrite is actually quite easy, once
we notice, that Exists function and EXISTING operator are generalization
of hierarchy related functions such as Descendants.
..
So, since the .CurrentMember can't directly return a set (in AS2005),
you have to use Existing or Exists() to compute the set of current
members. The exact expression will depend on what you're trying to
compute, and how you expect it to work when multiple members are
selected - can you provide more details of the usage scenarios?
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.developersdex.com ***

[siva]

Hi Deepak,

I want to explain the usage scenario more clearly.

I have two measure groups , SR and UsageUnits. Following are the sample
fact data.

UsageUnits
========

Date UsageUnitsProduct UsageUnits
13-Jan-2006 A 50
13-Jan-2006 B 60
13-Jan-2006 C 120
14-Jan-2006 A 60
14-Jan-2006 B 90
14-Jan-2006 C 180


sr
==

Date SRProduct SRCount
13-Jan-2006 A 4
13-Jan-2006 B 5
13-Jan-2006 C 7
14-Jan-2006 A 8
14-Jan-2006 B 9
14-Jan-2006 C 11


Please note that SRProduct , USageUnitsProduct are different
dimensions. Even though the values are same , They cannot be same
dimensions. But date dimension is same for both the measure groups.

Now , In SR measure group , We have a calculated measure called UU
where based on the Date and SRProduct selected we have to get UU.

So Code is something like this.

(Measures.UU, [UsageUnitsProduct].[Product
Name].["+[SRProduct].[SRProduct Name].CurrentMember.name+"])

As a string concatination we are passing SRProduct Current Member and
getting UU for equivalent product.

Instead of String concatenation , We can use linked member but still we
have to use CurrentMember.

Now due to the data filter on SRProduct, The error throws on
CurrentMember due to multiselect.

Hope this is clear this time. Please try to help.

Thanks,
Siva

Post by siva
Thanks for your reply deepak. My usage scenario is something like this.
I have a measure group called usageunits which has dimension
UsageUnitsProduct. There I have measure called usageunits.
I have another measure group called SR whose grain is something else.
This measureGroup has dimension called product. Now based on the
product dimension selected , For each current member selected on
product , I need to get usageunits from the UsageUnits measure group.
For this purpose Iam using linkMember function to link
UsageUnitsProduct hierarchy with the current selection(member) of
Product dimension.
Not able to imagine how to handle this during multi selection.
Similarily ,
In some other function , Iam using this code.
IIF([CreatedTime].[Time_Fiscal].CurrentMember.Level is
[CreatedTime].[Time_Fiscal].[Month],........
Iam just checking whether currentmember level is month. It fails during
filter selection of Time_Fiscal. Not sure how to handle this.
Thanks for your help in advance.
Regards,
Siva

Post by Deepak Puri
Assuming that it occurs when multiple members of Product or of [Fiscal
http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friend
ly_mdx.aspx
Writing multiselect friendly MDX calculations
..
The MDX function CURRENTMEMBER failed because the coordinate for 'Store
State' attribute contains a set.
..
Current coordinate in the cube in fact is not limited by single value in
each attribute. Some attributes can have multiple values - i.e. sets.
(Again, it is actually even more generic then that, since sets can
freely cross attribute and dimension boundaries, but for the subject of
multiselects the above is enough). The sets in the coordinate are
preserved throughout all the calculation execution. Many MDX functions
don't have problems with it, however when it comes to things which rely
on hierarchy current member - such as Store.CurrentMember expression -
it doesn't know what to do - since there are multiple current members
for that coordinate. While theoretically (and perhaps in some future
version) CurrentMember can be generalized to work over sets - in AS2005
it simply cannot work, and therefore returns abovementioned error -
current coordinate contains a set.
Now, equipped with all this knowledge we can start thinking how to
rewrite the expression for our calculated measure in such a way that it
will continue to work and work correctly in the presence of sets in the
coordinates on Store hierarchy. The rewrite is actually quite easy, once
we notice, that Exists function and EXISTING operator are generalization
of hierarchy related functions such as Descendants.
..
So, since the .CurrentMember can't directly return a set (in AS2005),
you have to use Existing or Exists() to compute the set of current
members. The exact expression will depend on what you're trying to
compute, and how you expect it to work when multiple members are
selected - can you provide more details of the usage scenarios?
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.developersdex.com ***

[Deepak Puri]

Hi Siva,

Based on Mosha's advice, how about trying this?

Sum(Existing [SRProduct].[SRProduct Name].[SRProduct Name].Members,
(Measures.UU, [UsageUnitsProduct].[Product Name].["
+[SRProduct].[SRProduct Name].CurrentMember.name+"]))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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