Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sum and Group Results - Date Comparison Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 3:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 4, 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
Posted Thursday, March 21, 2013 3:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
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
Posted Thursday, March 28, 2013 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 4, 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 »

Add to briefcase

Permissions Expand / Collapse