April 11, 2016 at 3:00 pm
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
April 12, 2016 at 6:04 am
I'd have a hard time believing SSRS generated that.
April 12, 2016 at 12:11 pm
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 )
)
April 13, 2016 at 3:29 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply