0.125intruetrueFeatureWorkSummary8.5in3=IIF(Parameters!DEBUG.Value = True, False, True)9.625inDevDiv_Project12true=Fields!DevDiv_Project.ValueID11true=Fields!ID.ValueTitle10true=Fields!Title.Valuecalc_Completed_Work9true=FormatNumber(Fields!calc_Completed_Work.Value,2)calc_Remaining_Work8true=FormatNumber(Fields!calc_Remaining_Work.Value,2)calc_ValueOfCompletedWorkAsOf7DaysAgo7true=FormatNumber(Fields!calc_ValueOfCompletedWorkAsOf7DaysAgo.Value,2)calc_WorkCompletedRecently6true=FormatNumber(Fields!calc_WorkCompletedRecently.Value,2)calc_ValueOfRemainingWorkAsOf7DaysAgo5true=FormatNumber(Fields!calc_ValueOfRemainingWorkAsOf7DaysAgo.Value,2)calc_TotalWork4true=FormatNumber(Fields!calc_TotalWork.Value,2)calc_Pct_CompletedAWhileAgo3true=FormatPercent(Fields!calc_Pct_CompletedAWhileAgo.Value,2)calc_Pct_CompletedRecently2true=FormatPercent(Fields!calc_Pct_CompletedRecently.Value,2)calc_Pct_Remaining1true=FormatPercent(Fields!calc_Pct_Remaining.Value,2)textbox20true=FormatPercent(
(Fields!calc_Pct_CompletedAWhileAgo.Value +
Fields!calc_Pct_CompletedRecently.Value +
Fields!calc_Pct_Remaining.Value)
,2)0.20833in=Fields!DevDiv_Project.ValueDescending=IIF( IsDate(Fields!FeatureEndDate.Value),
Code.FormatMDXRelativeDate( Fields!FeatureEndDate.Value, 0, "yyyy/MM/dd"),
""
)Ascending=Fields!Title.ValueAscendingtextbox2738trueProjecttextbox137trueIDtextbox636trueTitletextbox235trueCurrent Completetextbox334trueCurrent Remaintextbox2333trueCompleted As of Last Weektextbox1132trueWork Completed in Past Weektextbox2231trueRemaining As of Last Weektextbox430trueCurrent Total Worktextbox529truePct Completetextbox1428truePct Completed in Past Weektextbox1727truePct Remaintextbox1526trueTotal Percent0.20833in0.625in0.625in1.625in0.625in0.625in0.75in0.75in0.75in0.625in0.625in0.75in0.625in0.625in
[Work Item].[Microsoft_DeveloperDivision_Classifications_Project]WITH MEMBER [Measures].[ParameterCaption] AS '[Work Item].[Microsoft_DeveloperDivision_Classifications_Project].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Work Item].[Microsoft_DeveloperDivision_Classifications_Project].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Work Item].[Microsoft_DeveloperDivision_Classifications_Project].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Work Item].[Microsoft_DeveloperDivision_Classifications_Project].ALLMEMBERS ON ROWS FROM [Current Work Item]trueWorkItemMicrosoftDeveloperDivisionClassificationsGroup[Work Item].[Microsoft_DeveloperDivision_Classifications_Group].&[TFS]
Work Item.DevDiv Group
[Work Item].[Microsoft_DeveloperDivision_Classifications_Group]WITH MEMBER [Measures].[ParameterCaption] AS '[Work Item].[Microsoft_DeveloperDivision_Classifications_Group].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Work Item].[Microsoft_DeveloperDivision_Classifications_Group].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Work Item].[Microsoft_DeveloperDivision_Classifications_Group].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Work Item].[Microsoft_DeveloperDivision_Classifications_Group].ALLMEMBERS ON ROWS FROM [Current Work Item]trueMDXDateForWorkCompletedSinceDate[Date].[Date].&[2006-12-06T00:00:00]
[Work Item].[System_State]WITH MEMBER [Measures].[ParameterCaption] AS '[Work Item].[System_State].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Work Item].[System_State].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Work Item].[System_State].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Work Item].[System_State].ALLMEMBERS ON ROWS FROM [Current Work Item]trueWITH
MEMBER [Measures].[ValueOfCompletedWorkAsOf7DaysAgo] AS
(
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork],
[Work Item].[Microsoft_DeveloperDivision_Classifications_Group].[All],
[Work Item].[Microsoft_DeveloperDivision_Classifications_Project].[All],
[Work Item].[System_Title].[All],
[Work Item].[System_State].[All],
[Work Item].[Microsoft_DeveloperDivision_Features_RiskLevel].[All],
STRTOMEMBER(@MDXDateForWorkCompletedSinceDate)
)
MEMBER [Measures].[ValueOfRemainingWorkAsOf7DaysAgo] AS
(
[Measures].[Microsoft_VSTS_Scheduling_RemainingWork],
[Work Item].[Microsoft_DeveloperDivision_Classifications_Group].[All],
[Work Item].[Microsoft_DeveloperDivision_Classifications_Project].[All],
[Work Item].[System_Title].[All],
[Work Item].[System_State].[All],
[Work Item].[Microsoft_DeveloperDivision_Features_RiskLevel].[All],
STRTOMEMBER(@MDXDateForWorkCompletedSinceDate)
)
MEMBER [Measures].[FeatureEndDate] AS
EXTRACT(
NonEmpty(
[Microsoft_DeveloperDivision_Features_DateEnd].[Date].[Date] *
[Work Item].[System_Id].CurrentMember,
[Measures].[Current Work Item Count]
),
[Microsoft_DeveloperDivision_Features_DateEnd].[Date]
).Item(0).Member_Value
SELECT
Non Empty
{
[Measures].[FeatureEndDate],
[Measures].[Current Work Item Microsoft_VSTS_Scheduling_CompletedWork],
[Measures].[Current Work Item Microsoft_VSTS_Scheduling_RemainingWork],
[Measures].[ValueOfCompletedWorkAsOf7DaysAgo],
[Measures].[ValueOfRemainingWorkAsOf7DaysAgo]
} ON COLUMNS,
NonEmpty(
STRTOSET(@WorkItemMicrosoftDeveloperDivisionClassificationsGroup, CONSTRAINED) *
STRTOSET(@WorkItemMicrosoftDeveloperDivisionClassificationsProject, CONSTRAINED) *
[Work Item].[System_Id].[System_Id] *
[Work Item].[Microsoft_DeveloperDivision_Features_RiskLevel].[Microsoft_DeveloperDivision_Features_RiskLevel] *
[Work Item].[System_Title].[System_Title],
[Measures].[Current Work Item Count]
) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Current Work Item]
WHERE
(
[Work Item].[System_WorkItemType].&[Orcas Feature],
STRTOSET(@WorkItemSystemState, CONSTRAINED)
)
=Parameters!WorkItemMicrosoftDeveloperDivisionClassificationsProject.Value=Parameters!WorkItemMicrosoftDeveloperDivisionClassificationsGroup.Value=Parameters!MDXDateForWorkCompletedSinceDate.Value=Parameters!WorkItemSystemState.ValueTfsOlapReportDSSystem.String<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[Microsoft_DeveloperDivision_Classifications_Group].[Microsoft_DeveloperDivision_Classifications_Group]" />System.String<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[Microsoft_DeveloperDivision_Classifications_Project].[Microsoft_DeveloperDivision_Classifications_Project]" />System.String<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[System_Id].[System_Id]" />System.String<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[Microsoft_DeveloperDivision_Features_RiskLevel].[Microsoft_DeveloperDivision_Features_RiskLevel]" />System.String<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Work Item].[System_Title].[System_Title]" />System.Int32<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[FeatureEndDate]" />System.Int32<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Current Work Item Microsoft_VSTS_Scheduling_CompletedWork]" />System.Int32<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Current Work Item Microsoft_VSTS_Scheduling_RemainingWork]" />System.Int32<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ValueOfCompletedWorkAsOf7DaysAgo]" />System.Int32<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ValueOfRemainingWorkAsOf7DaysAgo]" />=Fields!calc_Completed_Work.Value + Fields!calc_Remaining_Work.Value=Fields!calc_Completed_Work.Value - Fields!calc_ValueOfCompletedWorkAsOf7DaysAgo.Value= IIF( Fields!calc_TotalWork.Value <> 0,
( (Fields!calc_Completed_Work.Value / Fields!calc_TotalWork.Value) - Fields!calc_Pct_CompletedRecently.Value ),
0
)= IIF( Fields!calc_TotalWork.Value <> 0,
IIF( (Fields!calc_WorkCompletedRecently.Value / Fields!calc_TotalWork.Value) > 0,
Fields!calc_WorkCompletedRecently.Value / Fields!calc_TotalWork.Value,
0
),
0
)= IIF( Fields!calc_TotalWork.Value <> 0,
Fields!calc_Remaining_Work.Value / Fields!calc_TotalWork.Value,
1
)=IIF( IsNothing(Fields!Completed_Work.Value),
0,
Fields!Completed_Work.Value
)=IIF( IsNothing(Fields!Remaining_Work.Value),
0,
Fields!Remaining_Work.Value
)=IIF( IsNothing(Fields!ValueOfCompletedWorkAsOf7DaysAgo.Value),
0,
Fields!ValueOfCompletedWorkAsOf7DaysAgo.Value
)=IIF( IsNothing(Fields!ValueOfRemainingWorkAsOf7DaysAgo.Value),
0,
Fields!ValueOfRemainingWorkAsOf7DaysAgo.Value
)=code.EndDateFilterCalculation( Fields!FeatureEndDate.Value, Parameters!EndDateFilter_Date.Value, Parameters!EndDateFilter_Type.Value)WITH MEMBER [Measures].[ParameterCaption] AS '[Work Item].[Microsoft_DeveloperDivision_Classifications_Group].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Work Item].[Microsoft_DeveloperDivision_Classifications_Group].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Work Item].[Microsoft_DeveloperDivision_Classifications_Group].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Work Item].[Microsoft_DeveloperDivision_Classifications_Group].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@WorkItemMicrosoftDeveloperDivisionClassificationsProject, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Work Item].[System_WorkItemType].&[Orcas Feature] } ) ON COLUMNS FROM [Current Work Item]))=Parameters!WorkItemMicrosoftDeveloperDivisionClassificationsProject.ValueTfsOlapReportDStruetrue<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Measure" UniqueName="[Measures].[ParameterValue]" /><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Measure" UniqueName="[Measures].[ParameterCaption]" />=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Measure" UniqueName="[Measures].[ParameterLevel]" />trueSELECT Setting AS LastProcessedTime
FROM _WarehouseConfig
WHERE ID = 'LastProcessTimeStamp'TfsReportDSSystem.StringLastProcessedTimetrueSELECT
DATEADD(day, 0, MAX([Current Work Item].Microsoft_DeveloperDivision_Features_DateEnd) ) as DateEnd
FROM
[Current Work Item] INNER JOIN
[Work Item] ON [Current Work Item].[Work Item] = [Work Item].__ID INNER JOIN
[Team Project] ON [Current Work Item].[Team Project] = [Team Project].__ID
WHERE
([Team Project].[Team Project] = 'Orcas') AND
([Work Item].System_WorkItemType = 'Orcas Feature') AND
([Work Item].Microsoft_DeveloperDivision_Classifications_Project = @Release) AND
([Work Item].System_State IN (@FeatureState) ) AND
[Work Item].Microsoft_DeveloperDivision_Classifications_Group IN (@ProductUnit)=Parameters!Release.Value=Parameters!FeatureState.Value=Parameters!ProductUnit.ValueTfsReportDSSystem.DateTimeDateEnd'A return value of false = do NOT filter the item out
'Of course that means a return value of true = DO filter out the item
'
Function EndDateFilterCalculation( byval pEndDate as Date, byval pEndDateFilter_Date as Date, byval pEndDateFilter_Type as String) as Boolean
Dim retval_FilterItem as Boolean
if YEAR( pEndDate) > 1
select case pEndDateFilter_Type
case "None"
retval_FilterItem = false
case "Before"
if ( pEndDate <= pEndDateFilter_Date )
retval_FilterItem = false
else
retval_FilterItem = true
end if
case "After"
if ( pEndDate >= pEndDateFilter_Date )
retval_FilterItem = false
else
retval_FilterItem = true
end if
case else
retval_FilterItem= false
end select
else
' The current end date is essentially null (1/1/0001) and so we won't ever filter those out.
retval_FilterItem = false
end if
return (retval_FilterItem)
End Function
Function FormatMDXRelativeDate( ByVal pStartDate as Date, ByVal pRelativeOffsetDays as Integer, ByVal pFormatStr as String) as String
Dim ResultDate as Date
Dim retval as String
ResultDate = DateAdd("d", pRelativeOffsetDays, pStartDate)
if( pFormatStr = "")
pFormatStr = "yyyy-MM-dd"
End If
retval = Format(ResultDate, pFormatStr)
Return( retval )
End Function
' Some reports have a multivalue parameters which are passed to an MDX query and
' then MDX query then transforms the value into a set. For example, imagine
' a query which needs to find all bugs for ProductUnitA, ProductUnitB and ProductUnitC.
' In this case you often would have a multivalued parameter which controls the list
' of valid Product Units from which the user can pick. Each Product Unit would have a
' Label value that the user sees and then an underlying "Value" value which is passed
' down into the MDX query. Since the "value" is passed directly to MDX, it actually
' takes the form of MDX such as: [Work Item].[MyNameSpace_ProductUnit].&[ProductUnitA]
'
' That is all fine and dandy until you want to URL enable the report so that people
' can directly "pick" from the multivalue parameter by directly encoding their selection
' into the URL. In this case, the URL line because at a minimum unusable because very
' few people understand the MDX syntax. However then there also is the problem of all
' the spaces and special characters in the URL line which necessitate surrounding quotes.
' Even if somebody puts in the quotes, initial testing indicates that this still won't work.
' Rather than try and figure out how to make an unusable approach "work" -- what we really
' need to do is make something MUCH easier for the user work smoothly.
'
' To do that, what we really want is to use report parameter labels which not only are
' a) understandable by the interactive user of the report but also b) have values which
' are also easy to understand and encode in the URL. Then internal to the report we can use
' some calculations to transform the report parameter value into the corresponding MDX parameter value
' needed by the underlying MDX query.
'
' This function performs such a transformation on a multivalue report parameter collection.
' Each value in the collection is surrounded by a user-defined prefix & suffix before being
' appended to the return value string.
'
Function ParameterCollection_TransformToMDX( ByVal pCollection as String(), ByVal pMDXPrefix as String, ByVal pMDXSuffix as String) as String
dim IsPrefixCommaNeeded as Boolean
dim curr_pCollectionObj as String
dim retval as String
' This function presumes that the parameter is a multivalued collection.
' As such, if the user selects more than one parameter then the resulting
' MDX must be formatted as a SET. That means it must be surrounded with
' braces (e.g. {} ).
retval = "{"
IsPrefixCommaNeeded = False
for each curr_pCollectionObj in pCollection
if ( IsPrefixCommaNeeded = True)
retval = retval & ", "
End If
retval = retval & pMDXPrefix & curr_pCollectionObj & pMDXSuffix
' All subsequent trips through the loop need the comma prefix
IsPrefixCommaNeeded = True
next
retval = retval & "}"
return( retval )
End Function
10.125in11inen-US1in