Filtering Based on WorkItem.Created Date in Excel using PIVOT Table to get defects created in last 30 days, last 60 days etc

Question

Hi,

I need to filter / create a custom set that will return me bugs created in the last 30 days, 60 days in excel. I have added a TFS OLAP Connection. I tried using MDX query to filter the date, but I am not successful.

I am able to create a set for last 4 weeks using create date hierarchy by week and month. But when i try to filter directly on created date like below and create a set...

CreatedDate >VBA!DateAdd("d", -30, VBA!Now())

Still defects before 30 days are also shown. Can some one please help me form the custom set / MDX to get this problem resolved.

If you can suggest me other better ways also, it will be appreciable. Note this needs to be from the excel through TFS OLAP connection.

 

 

Thanks,

Raghu

 

Answer 1

Hello Raghu,

Thanks for your post.

I am sorry that I don’t very familiar with MDX query, and if you still want to work it with MDX query, you need to consult SSAS experts on the SSAS forum here:

http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/threads

However, for your scenario, I have another approach which may be help for you: instead of using MDX query, you can just use excel to access the TFS analysis cubes, and get the appropriate work items for you.

Please follow the steps below:

1). Create an excel file and add a data source to the Tfs_Analysis cubes.

On the Data menu->From Other Sources->From Analysis Services->On the Data Connection Wizard, type the server name and your log on credentials->select the Team System cube->Finish.

2). Create your own PivotTable report which is similar to:

https://skydrive.live.com/?cid=c0257d84e94b7a32#cid=C0257D84E94B7A32&id=C0257D84E94B7A32%21368

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Thanks a lot Vicky! I have put the below solution in place and it worked for me.

 

Filter(

[Work Item].[System_CreatedDate].[System_CreatedDate],

    [Work Item].[System_CreatedDate].CurrentMember.Member_Value < Now()

    AND [Work Item].[System_CreatedDate].CurrentMember.Member_Value >= DateAdd("d", -60, VBA![Date]())

)

Answer 3

Hello Raghu,

Glad to hear you have had your issue resolved. And thanks for sharing us your solution here.

Have a nice day.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi,

When I tried with above statement I am getting "Query (3, 4) Parser: The syntax for 'Filter' is incorrect" error. Below is my query. Can you give me solution for it?

FYI, My requirement is to get current date workitems count.

SELECT NON EMPTY { [Measures].[Work Item Count] } ON COLUMNS,NON EMPTY { ([Work Item].[System_ChangedBy].[System_ChangedBy].ALLMEMBERS * [Work Item].[Microsoft_VSTS_Common_Severity].[Microsoft_VSTS_Common_Severity].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Team System] Filter( [Work Item].[System_CreatedDate],

[Work Item].[System_CreatedDate].CurrentMember.Member_Value = Now() )CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

---

Radhika