auto generated mdx from reporting services

  • 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

  • Yes you are correct. But its just the way the query is built. You can always fine tune the MDX yourself 🙂

    Raunak J

  • 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