How do I add a filter to this query?

[douginrome]

I'm an MDX newbie.. with some help I was able to create a query that returns accurate results. All I need to do now is filter for Completed Work value of greater than 0.


I have tried to use the filter wizard in VS2005, but I have never gotten the desired results.




Platform Info:
SQL Server 2005 Standard
VS2005



---------Start Statement----------------------------------------------------------
WITH

MEMBER [Measures].[Completed Work by WI on dt1] AS

(

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,


[Date].[Year Week Date].[Date].&[2006-04-23T00:00:00],

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work by WI on dt2] AS

(

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,

[Date].[Year Week Date].[Date].&[2006-04-29T00:00:00],

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work] AS

[Measures].[Completed Work by WI on dt2] - [Measures].[Completed Work by WI on dt1]

SELECT
NON empty
{
[Measures].[Completed Work]

}

ON COLUMNS,

NON EMPTY

{
([Assigned To].[Person].[Person],[Work Item].[System_Id].[System_Id],[Work Item].[System_Title].[System_Title])


}

ON ROWS

FROM

[Team System]

----------End Statement---------------------------------------------------

[douginrome]

After reading Bill's excellent article on filtering, I solved it... code below.

Until I read Bill's article I did not understand that I put the entire set in the first part of the filter.... anyhow, thanks Bill!




WITH

MEMBER [Measures].[Completed Work by WI on dt1] AS

(

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,


[Date].[Year Week Date].[Date].&[2006-04-23T00:00:00],

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work by WI on dt2] AS

(

[Assigned To].[Person].CurrentMember,

[Work Item].[System_Id].CurrentMember,

[Date].[Year Week Date].[Date].&[2006-04-29T00:00:00],

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work] AS

[Measures].[Completed Work by WI on dt2] - [Measures].[Completed Work by WI on dt1]

SELECT
NON empty
{
[Measures].[Completed Work]

}

ON COLUMNS,

NON EMPTY

{
Filter(
([Assigned To].[Person].[Person],[Work Item].[System_Id].[System_Id],[Work Item].[System_Title].[System_Title]), [Measures].[Completed Work] >0 )


}