Sum and Group Results - Date Comparison

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » Sum and Group Results - Date Comparison

Sum and Group Results - Date Comparison

Rate Topic

Display Mode

Topic Options

Author

Message

evgapro

Posted Friday, March 15, 2013 3:43 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 3:14 PM
Points: 2, Visits: 11

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

Post #1431809

Mackers

Posted Thursday, March 21, 2013 3:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:25 AM
Points: 117, Visits: 473

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

Post #1433641

evgapro

Posted Thursday, March 28, 2013 10:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 3:14 PM
Points: 2, Visits: 11

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) )

Post #1436584

« Prev Topic | Next Topic »

Permissions