March 21, 2013 at 3:01 am
If I have understood this correctly then its quite a tricky one but should be possible using the generate statement
I cannot test the below MDX but here is a stab at what you probably want
WITH
MEMBER [Measures].[AgedGroupTotal] AS ([Measures].[Work Item Count])
MEMBER [Measures].[AgedGroup30] AS
Sum(
Generate( [Work Item].[System_CreatedDate].[All].Children
,Filter(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name
, [Date].[Date].CurrentMember.Name) <= 30))
[Measures].[Work Item Count])
SELECT NON EMPTY
{ [Measures].[AgedGroup30]
, [Measures].[AgedGroupTotal] }
ON COLUMNS
, NON EMPTY {
[Date].[Month].[Month].ALLMEMBERS *
[Date].[Week].[Week].ALLMEMBERS *
[Date].[Date].[Date].ALLMEMBERS }
ON ROWS
FROM ( SELECT ( [Date].[Date].&[2013-01-01T00:00:00] : [Date].[Date].&[2013-03-31T00:00:00] )
ON COLUMNS
FROM ( SELECT ( { [Work Item].[System_State].&[Active] } )
ON COLUMNS FROM (
SELECT ( { [Work Item].[System_WorkItemType].&[Bug] } )
ON COLUMNS FROM [Work Item])))
Mack
March 28, 2013 at 10:06 am
Mackers,
Thanks for the response. I am getting the following error (try to debug it, but not having luck):
Executing the query ...
Query (7, 52) Parser: The syntax for ')' is incorrect.
Execution complete
WITH
MEMBER [Measures].[AgedGroupTotal] AS ([Measures].[Work Item Count])
MEMBER [Measures].[AgedGroup30] AS
Sum(
Generate( [Work Item].[System_CreatedDate].[All].Children
,Filter(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name
, [Date].[Date].CurrentMember.Name) <= 30))
[Measures].[Work Item Count])
SELECT NON EMPTY
{ [Measures].[AgedGroup30]
, [Measures].[AgedGroupTotal] }
ON COLUMNS
, NON EMPTY {
[Date].[Month].[Month].ALLMEMBERS *
[Date].[Week].[Week].ALLMEMBERS *
[Date].[Date].[Date].ALLMEMBERS }
ON ROWS
FROM ( SELECT ( [Date].[Date].&[2013-01-01T00:00:00] : [Date].[Date].&[2013-03-31T00:00:00] )
ON COLUMNS
FROM ( SELECT ( { [Work Item].[System_State].&[Active] } )
ON COLUMNS FROM (
SELECT ( { [Work Item].[System_WorkItemType].&[Bug] } )
ON COLUMNS FROM [Work Item])))
It seems to be complaining here:
, [Date].[Date].CurrentMember.Name) <= 30))
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply