DougInGeorgia

I want to show the fully qualified area path in the query below.

Currently it only returns the top tier of the area path. I want to return the full area path at it appears in the query designer in Team Explorer of VS 2005.

Thanks in advance.

Doug

------------------------

WITH

MEMBER [Measures].[Area Path] AS

Iif([Area].[Parent_ID].CurrentMember IS [Area].[Parent_ID].[All],

null,

([Area].[Parent_ID].CurrentMember.Parent, [Measures].[Area Path]) + "\" + [Area].[Parent_ID]. CurrentMember.Member_Value

)

SELECT

{

[Measures].[Area Path],

[Measures].[Current Work Item Count]

} ON COLUMNS,

NONEMPTYCROSSJOIN

(

StrToMember("[Area].[Parent_ID].[" + @Project + "]").Children ,

([System_CreatedDate].[Date].&[2007-04-03T00:00:00]:[System_CreatedDate].[Date].&[2007-04-11T00:00:00]),

2

) ON ROWS

FROM [Team System]

WHERE

(

STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),

[Work Item].[System_WorkItemType].&[Bug]

)



Re: Team Foundation Server - Reporting & Warehouse Fully Qualified Area Path

Othmane Rahmouni-MSFT

I had done something similar with Iteration, you can do the exact same thing with Area

WITH

MEMBER [Measures].[Iteration Path] AS

Iif([Iteration].[Parent_ID].CurrentMember IS [Iteration].[Parent_ID].[All],

null,

([Iteration].[Parent_ID].CurrentMember.Parent,[Measures].[Iteration Path]) + "\" +

Iif([Iteration].[Parent_ID].CurrentMember.UniqueName =

[Iteration].[Parent_ID].CurrentMember.Parent.DataMember.UniqueName,

"(" + [Iteration].[Parent_ID].CurrentMember.Member_Value + ")",

[Iteration].[Parent_ID].CurrentMember.Member_Value

)

)

SELECT NON EMPTY

{

[Measures].[Current Work Item Count] ,

[Measures].[Iteration Path]

} ON COLUMNS,

NONEMPTYcrossjoin

(

StrToMember("[Iteration].[Parent_ID].&[1]").Children,

[Work Item].[System_Title].[System_Title],

[Work Item].[System_Id].[System_Id],

[Measures].[Current Work Item Count],

3

) on rows

FROM [Team System]

Have a great day!

Othmane Rahmouni





Re: Team Foundation Server - Reporting & Warehouse Fully Qualified Area Path

DougInGeorgia

Thanks Othmane:

I have used your sample code and replaced Iteration with Area, but for some reason, I still do not get the full area paths.

Doug





Re: Team Foundation Server - Reporting & Warehouse Fully Qualified Area Path

Othmane Rahmouni-MSFT

Hi Doug,

I have changed the MDX query to get you all the areas:

WITH

MEMBER [Measures].[Area Path] AS

Iif([Area].[Parent_ID].CurrentMember IS [Area].[Parent_ID].[All],

null,

([Area].[Parent_ID].CurrentMember.Parent,[Measures].[Area Path]) + "\" +

Iif([Area].[Parent_ID].CurrentMember.UniqueName =

[Area].[Parent_ID].CurrentMember.Parent.DataMember.UniqueName,

"(" + [Area].[Parent_ID].CurrentMember.Member_Value + ")",

[Area].[Parent_ID].CurrentMember.Member_Value

)

)

SELECT NON EMPTY

{

[Measures].[Current Work Item Count] ,

[Measures].[Area Path]

} ON COLUMNS,

NONEMPTYcrossjoin

(

Descendants(StrToMember("[Area].[Parent_ID]").Children),

[Work Item].[System_Title].[System_Title],

[Work Item].[System_Id].[System_Id],

[Measures].[Current Work Item Count],

3

) on rows

FROM [Team System]

Good luck!

Othmane





Re: Team Foundation Server - Reporting & Warehouse Fully Qualified Area Path

Mauli Shah - MSFT

hey doug,

can you let us know if that worked for you

thanks!

mauli