MDX: Filtering a member set by a measure's table values

Posted by oyvinro on Stack Overflow See other posts from Stack Overflow or by oyvinro
Published on 2009-02-05T10:17:28Z Indexed on 2010/04/07 19:03 UTC
Read the original article Hit count: 163

Filed under:
|
|

I have some numbers in a fact table, and have generated a measure which use the SUM aggregator to summarize the numbers. But the problem is that I only want to sum the numbers that are higher than, say 10. I tried using a generic expression in the measure definition, and that works of course, but the problem is that I need to be able to dynamically set that value, because it's not always 10, meaning users should be able to select it themselves.

More specifically, my current MDX looks like this:

WITH
SET [Email Measures] AS '{[Measures].[Number Of Answered Cases], 
[Measures].[Max Expedition Time First In Case], [Measures].[Avg Expedition Times First In Case], 
[Measures].[Number Of Incoming Email Requests], [Measures].[Avg Number Of Emails In Cases],
[Measures].[Avg Expedition Times Total],[Measures].[Number Of Answered Incoming Emails]}' 

SET [Organizations] AS '{[Organization.Id].[860]}' 
SET [Operators] AS '{[Operator.Id].[3379],[Operator.Id].[3181]}'
SET [Email Accounts] AS '{[Email Account.Id].[6]}'
MEMBER [Time.Date].[Date Period] AS Aggregate ({[Time.Date].[2008].[11].[11] :[Time.Date].[2009].[1].[2] }) 
MEMBER [Email.Type].[Email Types] AS Aggregate ({[Email.Type].[0]}) 
SELECT {[Email Measures]} ON columns,
[Operators] ON rows 
FROM [Email_Fact]
WHERE ( [Time.Date].[Date Period] )

Now, the member in question is the calculated member [Avg Expedition Times Total]. This member takes in two measures; [Sum Expedition Times] and [Nr of Expedition Times] and splits one on the other to get the average, all this presently works. However, I want [Sum Expedition Times] to only summarize values over or under a parameter of my/the user's wish.

How do I filter the numbers [Sum Expedition Times] iterates through, rather than filtering on the sum that the measure gives me in the end?

© Stack Overflow or respective owner

Related posts about mdx

Related posts about pentaho