Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

auto generated mdx from reporting services Expand / Collapse
Author
Message
Posted Sunday, September 15, 2013 1:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 21, 2014 4:04 AM
Points: 69, Visits: 336
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

Post #1494898
Posted Monday, September 23, 2013 2:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:09 AM
Points: 1,046, Visits: 1,846
Yes you are correct. But its just the way the query is built. You can always fine tune the MDX yourself

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1497298
Posted Monday, October 21, 2013 4:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 21, 2014 4:04 AM
Points: 69, Visits: 336
Hi Raunak

It checks out...thx
Post #1506604
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse