CODEWITHSUNDEEP
sql-serverreporting-servicesmdxsql-server-2016
Ben
Ben

Reputation: 893

Parameter Does Not Work in MDX Subselect Filter

I have the following MDX query:

SELECT 
  NON EMPTY 
    Measures.[Enrolments] ON COLUMNS,
  NON EMPTY 
    ( STRTOMEMBER(@FromISOYear, CONSTRAINED) : 
    STRTOMEMBER(@ToISOYear, CONSTRAINED) ) ON ROWS
FROM (
 SELECT
      Filter
      (
            [Term Start Date].[ISO Year].Children *
            [Term Record Creation].[ISO Year].Children *
            [Term Record Creation].[ISO Week Number Of Year].Children
       ,
        Cint([Term Record Creation].[ISO Week Number Of Year].CurrentMember.Member_Key) <= Cint( STRTOMEMBER(@ToISOWeekNumberOfYear, CONSTRAINED) )
        OR
        Cint([Term Record Creation].[ISO Year].CurrentMember.Member_key) < Cint([Term Start Date].[ISO Year].CurrentMember.Member_key)
      ) ON COLUMNS
  FROM [Enrolments]
);

Basically, the filter expression works when ISO Week Number of Year is not a parameter e.g. if I change the filter expression to say [ISO Week Number of Year] <= 7 it delivers correct results. When I pass the same value from SSRS as a parameter, I get incorrect results - the filter doesn't appear to be applied.

Could someone please explain why this is and provide a fix?

Upvotes: 1

Views: 123

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

I notice you are missing Member_Key here. Change the following:

Cint( STRTOMEMBER(@ToISOWeekNumberOfYear, CONSTRAINED) )

To:

Cint( STRTOMEMBER(@ToISOWeekNumberOfYear, CONSTRAINED).Member_Key )

Upvotes: 1

Related Questions