CODEWITHSUNDEEP
sql-serverreporting-servicesmdx
wgpubs
wgpubs

Reputation: 8261

MDX: Problem filtering results in MDX query used in Reporting Services query

Why aren't my results being filtered by the members from my [Group Hierarchy] returned via the filter() statment below?

    SELECT 
    NON EMPTY {[Measures].[Group Count], [Measures].[Overall Group Count]   } ON COLUMNS,
    NON EMPTY {
        [Survey].[Surveys By Year].[Survey Year].ALLMEMBERS * 
        [Response Status].[Response Status].[Response Status].ALLMEMBERS} 
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
    SELECT ( { [Survey Type].[Survey Type Hierarchy].&[9] } ) ON COLUMNS
    FROM (
        SELECT ( { [Response Status].[Response Status].[All] } ) ON COLUMNS
        FROM (
            SELECT ( STRTOSET(@SurveySurveysByYear, CONSTRAINED) ) ON COLUMNS
            FROM (
            SELECT(filter([Group].[Group Hierarchy].members, instr(@GroupGroupFullName,[Group].[Group Hierarchy].Properties( "Group Full Name" )))) on columns 
            FROM [SysSurveyDW]))))

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Upvotes: 0

Views: 1937

Answers (2)

wgpubs
wgpubs

Reputation: 8261

Here is a solution that works as expected. Moved the filter into the WHERE clause ... which didn't work UNTIL I put the "SELECT ({[Group].[Group].[Group]}) ON COLUMNS..." code in the FROM.

Can anyone see a better way to accomplish this? Definitely not the MDX guru here :)

SELECT 
    NON EMPTY {[Measures].[Group Count], [Measures].[Overall Group Count]   } ON COLUMNS,
    NON EMPTY { 
        STRTOSET(@SurveySurveysByYear) * 
        [Response Status].[Response Status].[Response Status].ALLMEMBERS}
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
    SELECT ({[Group].[Group].[Group]}) ON COLUMNS 
    FROM (
        SELECT ( { [Survey Type].[Survey Type Hierarchy].&[9] } ) ON COLUMNS
        FROM (
            SELECT ( { [Response Status].[Response Status].[All] } ) ON COLUMNS
            FROM [SysSurveyDW] )))
where
filter([Group].[Group Hierarchy].members - [Group].[Group Hierarchy].[All],
 instr(@GroupGroupFullName,[Group].[Group Hierarchy].Properties( "Group Full Name" )))


CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Upvotes: 1

Darren Gosbell
Darren Gosbell

Reputation: 1940

I think you may have your parameters around the wrong way. The way you have it coded, its effectively looking for members names that exist within your parameter. I'm not sure what you are passing in as the parameter, but I would have thought that it was more likely that you were searching for member names that contained the parameter value.

Upvotes: 0

Related Questions