I should preface this by saying that I know basically nothing about MDX. I didn't even know it existed until yesterday, when someone handed me this query to use, claimed it was working, and I ran it and it returned back every result when it was supposed to filter by a person. So I would love an explanation of a. what it's actually doing currently, as well as b. how I can fix it to do what I want:
SELECT NON EMPTY
} ON COLUMNS,
[Consultant].[Account Full Name].[Account Full Name].ALLMEMBERS *
[Consultant].[Activity Full Name].[Activity Full Name].ALLMEMBERS *
[Consultant].[Engineer Full Name].[Engineer Full Name].ALLMEMBERS
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
SELECT ( [Date].[Date].&[2013-01-01T00:00:00] : [Date].[Date].&[2014-01-01T00:00:00] )
ON COLUMNS FROM [Data]
WHERE ([Consultant].[Engineer Full Name].&[Adam])
) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS;";
In the actual code, the date range and person's name are parameters to the function that calls this query, and I added some newlines to make it more readable, but other than that, this is the query that the code I was given runs.
From what I can tell, it first creates data by selecting on a date range, then "slicing" it by the name in a field, then generating a table where one axis is the Time Spent, and the other axis is some other data. Which is great, except the WHERE is just being completely ignored. I get results only within the date range, but not filtered in any way by the Engineer Full Name.
I've been googling all day, but while I have found a few nominally-introductions to MDX, I haven't found one that didn't really assume that you basically already knew MDX, which is less than entirely helpful. So I would really love someone to help me figure out what's wrong with this.
The query that you have here is a subselect, so the WHERE clause is not a slice in the same way it would be if used in a non-subselect query. Your explanation of slicing and the table is correct -- the behaviour of subselect is what's tripping you up.
Understand that the subselect creates a new "subcube", restricted to the members in the subselect, plus all their ancestors (ie, it keeps a well-formed hierarchy back to the root). People are sometimes surprised to find the ancestors in the final results if the top level select is not very restrictive. (I know this isn't your case here, it's just FYI.)
The WHERE clause has no effect on the subcube. I think you should be able to move this to the ROWS to achieve what you want.
... FROM ( SELECT ( [Date].[Date].&[2013-01-01T00:00:00] : [Date].[Date].&[2014-01-01T00:00:00] ) ON COLUMNS ([Consultant].[Engineer Full Name].&[Adam]) ON ROWS FROM [Data] )
Here's the documentation for subselect. See the Remarks section at the end about WHERE: http://technet.microsoft.com/en-us/library/ff487138.aspx
Hope that helps.