Sum and Group Results - Date Comparison
-
I am working on a MDX Dataset from a TFS2010 Cube (I Have also posted on the Team Foundation Server - Reporting & Warehouse forum).
I am looking to produce the trend of aged defects over the last several months. I have code producing the data that I need, for I am unable to get it to count in the way I need for the SSRS line graph. Here is the code:
WITH MEMBER [Measures].[AgedGroupTotal] AS ([Measures].[Work Item Count])MEMBER [Measures].[AgedGroup30] AS IIF(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) <= 30, [Measures].[Work Item Count], null)
MEMBER [Measures].[AgedGroup3060] AS IIF(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) > 30 AND DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) <= 60, [Measures].[Work Item Count], null)
MEMBER [Measures].[AgedGroup6090] AS IIF(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) > 60 AND DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) <= 90, [Measures].[Work Item Count], null)
MEMBER [Measures].[AgedGroup90] AS IIF(DATEDIFF("d", [Work Item].[System_CreatedDate].CurrentMember.Name, [Date].[Date].CurrentMember.Name) > 90, [Measures].[Work Item Count], null)
SELECT NON EMPTY { [Measures].[AgedGroup30], [Measures].[AgedGroup90], [Measures].[AgedGroup6090], [Measures].[AgedGroup3060], [Measures].[AgedGroupTotal] }
ON COLUMNS, NON EMPTY { ([Date].[Month].[Month].ALLMEMBERS *
[Date].[Week].[Week].ALLMEMBERS *
[Date].[Date].[Date].ALLMEMBERS *
[Work Item].[System_CreatedDate].[System_CreatedDate].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
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])))
WHERE ([Work Item].[System_WorkItemType].&[Bug], [Work Item].[System_State].&[Active])
Here is the result:
The problem is the [Work Item].[System_CreatedDate].[System_CreatedDate].ALLMEMBERS needing to be included in the result. Without this member being included, the DATEDIFF formula's for the age groups do not calculate and give an #Error. If I output this data into an SSRS table with a SUM on the AgedGroups, and exclude the created date, I get the data set I need:
The issue is that I need this summed result in my MDX dataset. However, I can not exclude the Created date, which creates the issue. I am new to MDX, but in SQL this operation would be simple with using a TEMP table and sum/group by. However, I know MDX does not work this way. Can anyone help me figure out how to get this dataset in MDX?
Thank you for any help,
Evgapro
-
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
WITHMEMBER [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
-
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply