MDX CurrentMember with SSAS 2008 doesn't work as stated by MSDN

Question

First of all I use the SQL Management Studio for this query (no Excel 2007 that seems to have problems):

WITH
    SET [Project period dates] AS 
    {
        StrToMember("[Time].[Date].&[" + [Project].[ParentProject].CURRENTMEMBER.PROPERTIES("Project Start Iso") + "]"):
        StrToMember("[Time].[Date].&[" + [Project].[ParentProject].CURRENTMEMBER.PROPERTIES("Project End Iso") + "]")
    }
    MEMBER [Measures].[Test] AS ([Project period dates].COUNT)
SELECT
{
    [Measures].[Test]
}
on 0,
    NONEMPTY ([Project].[ParentProject].MEMBERS)
    DIMENSION PROPERTIES [Project].[ParentProject].[Project Duration], [Project].[ParentProject].[Project Start Iso], [Project].[ParentProject].[Project End Iso]
on 1
FROM
    [MyCube]
WHERE
(
    [Orgunit].[Orgunit].&[448]
)

This query delivers a list of projects with its three properties and a calculated member that is based upon my calculated set. The properties show the right values, but the calculated member shows always the same: the result of the very first project it should be calculated for.

I don't really understand why, because MSDN says:

The current member changes on a hierarchy used on an axis in a query. Therefore, the current member on other hierarchies on the same dimension that are not used on an axis can also change; this behavior is called 'auto-exists'.

They give examples with calculated members, but I think that should also work with calculated sets, I have read that query-based calculated sets are dynamic by nature. Maybe somebody can tell me if I understood that wrong or what else is my problem here.

Benoit Benoit · Accepted Answer · 2013-07-12T14:18:23

The named set are only computed once within a query. That is why your calculated member always return the same value.

You just have to remove the named set from your query:

MEMBER [Measures].[Test] AS  {
    StrToMember("[Time].[Date].&[" + [Project].[ParentProject].CURRENTMEMBER.PROPERTIES("Project Start Iso") + "]"):
    StrToMember("[Time].[Date].&[" + [Project].[ParentProject].CURRENTMEMBER.PROPERTIES("Project End Iso") + "]")
}.COUNT

MDX CurrentMember with SSAS 2008 doesn't work as stated by MSDN

1 Answers