New to MDX - FILTER Question
2 posters
Page 1 of 1
New to MDX - FILTER Question
I've got an MDX query I need to modify, but not quite sure how to proceed. I essentially know most of tye syntax, but am at a loss as to where the components of the syntax should appear in the query. Here is the original query:
I'm sure a FILTER clause is what I need, but not sure where to slip that in.
What I need to do is tweak the query so that the DISCIPLINE value of Loyalty is excluded from the results, along with the DEPARTMENT value of OLSON PR.
This is how I'm sure the two lines that say what should be excluded, I'm just not sure where they should go:
[Employee].[FTE vs FL].[Discipline].&[LOYALTY],
[Employee].[FTE vs FL].[Discipline].[Department].&[OLSON PR Chicago]
MDX is definitely not my strong point. Any assistance anyone can render would be greatly appreciated!!
Thanks in advance!!
- Code:
WITH
MEMBER [Employee].[FTE vs FL].[CF Total] AS AGGREGATE([Client Facing Disciplines])
MEMBER [Time].[Year - Half Year - Quarter - Month - Date].[Trailing 12 Months] AS AGGREGATE(LASTPERIODS(60, STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)))
MEMBER [Time].[Year - Half Year - Quarter - Month - Date].[YTD] AS AGGREGATE(YTD(STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)))
SELECT
{ [Measures].[Employee Hours %], [Measures].[Employee Hours], [Measures].[Available Hours minus PTO] } ON COLUMNS,
NON EMPTY
CROSSJOIN(
{
[Employee].[FTE vs FL].[CF Total],
DrilldownMember(
[Client Facing Disciplines],
{
[Employee].[FTE vs FL].[Discipline].&[DIGITAL AND INTERACTIVE],
[Employee].[FTE vs FL].[Discipline].&[PUBLIC RELATIONS]
})
},
{
LASTPERIODS(60, STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)),
[Time].[Year - Half Year - Quarter - Month - Date].[Trailing 12 Months],
[Time].[Year - Half Year - Quarter - Month - Date].[YTD]
})
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [OLSON BI]
WHERE ([Employee Hours Time Category].[Time Category].&[Client Facing])
I'm sure a FILTER clause is what I need, but not sure where to slip that in.
What I need to do is tweak the query so that the DISCIPLINE value of Loyalty is excluded from the results, along with the DEPARTMENT value of OLSON PR.
This is how I'm sure the two lines that say what should be excluded, I'm just not sure where they should go:
[Employee].[FTE vs FL].[Discipline].&[LOYALTY],
[Employee].[FTE vs FL].[Discipline].[Department].&[OLSON PR Chicago]
MDX is definitely not my strong point. Any assistance anyone can render would be greatly appreciated!!
Thanks in advance!!
ansonee- Posts : 3
Join date : 2011-08-11
Re: New to MDX - FILTER Question
You'd be better off going to the MSDN forum for SSAS to get some of the guys there to help you out.
Similar topics
» Having a dynamic set which is affected by the filter in SSAS 2005
» Multiple fact tables and a having filter clause
» SCD question
» One Question
» IDT 4.0 Question
» Multiple fact tables and a having filter clause
» SCD question
» One Question
» IDT 4.0 Question
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum