currentmember referred to in WHERE Clause

  • Hi All,

    The following is mdx selecting from a subselect . any idea what is the purpose of having a WHERE clause i.e WHERE [Date].[Calendar].CurrentMember . What is it's purpose ? What is currentmember is referring to. I can't see what it is trying to enforce.

    SELECT NON EMPTY { [Measures].[Sales Amount] } ON 1,

    NON EMPTY { ([Product].[Category].[Category].MEMBERS ) } on 2

    FROM ( SELECT

    ( { [Date].[Calendar].Members]} ON 0 FROM [Adventure Works])

    WHERE [Date].[Calendar].CurrentMember

    )

    )

    this is the sort of boiler plate mdx generated out by SSRS when using a parameter

  • I'd have a hard time believing SSRS generated that.

  • well believe it....here's the kiddie

    SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) }DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWSFROM ( SELECT

    ( STRTOSET(@DateCalendar, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works])

    WHERE ( IIF( STRTOSET(@DateCalendar, CONSTRAINED).Count = 1, STRTOSET(@DateCalendar, CONSTRAINED), [Date].[Calendar].currentmember )

    )

  • Ah, well that certainly is SSRS generated MDX.

    You are right that in this situation it the where clause appears superfluous and can be removed. However it does have a purpose which relates to the query context as subcubes and where clauses affect the cube differently. One noticable issue being subcubes do not restrict calculated measures, which may or may not be behaviour you want.

    Try this - add these calculated members to your query:

    WITH MEMBER [Measures].[x] AS

    [Date].[Calendar].CurrentMember.Member_Caption

    MEMBER [Measures].[y] AS

    ([Date].[Calendar].CurrentMember, [Measures].[Sales Amount])

    Then try them with and without the WHERE clause (with only one date picked).

    I think the Iif statement in the where clause is to prevent issues with multi-selects.

    Much better write-ups and explanations here and here.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply