September 15, 2013 at 1:32 pm
hi All,
Am trying to get my head around mdx generated from reporting services when a parameter is entered e.g
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 )
)
I can understand it that it always creates a subselect but why does it also add the "WHERE" slicer clause in the subselect ...surely it is not needed. The subselect itself has already filtered the cube down to the set entered. And what is the WHERE trying to achieve anyway ? The IIF is saying if we only have one member in the parameter list then slice it on the single member else slice it on currentmember. Current member of what though ? Current member usually refers to a named calculation...there is no named calculation in the subselect ...all very confusing.
shouldn't the below do the job anyway...much simpler
SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) } ON ROWSFROM ( SELECT ( STRTOSET(@DateCalendar, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works])
rgds all
robin
September 23, 2013 at 2:56 am
Yes you are correct. But its just the way the query is built. You can always fine tune the MDX yourself 🙂
Raunak J
October 21, 2013 at 4:14 am
Hi Raunak
It checks out...thx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply