It's all about the answers!

Ask a question

Need to build a query to show work items started last week - please help!!

Maria Victoria Martinez Torino (14924559) | asked Aug 14 '13, 11:53 a.m.
retagged Aug 14 '13, 6:14 p.m. by Ralph Earle (25739)
I'm trying to build a custom query to show work items started during last week (lets say an action set from New to In Progress within the last 7 days, including today).
So far I could figure out one of the query conditions:
+ Status is [Unresolved]
+ Type is [Action]
Unfortunately, I don't know how to configure the condition to set the dates. Do you know if there is some OOTB feature for this?

I am using RTC v4.0.2

Thank you!

2 answers

permanent link
Millard Ellingsworth (2.5k12431) | answered Aug 15 '13, 5:34 p.m.
Hi, Maria!

Based on some research, I don't think this is currently possible using a query. I think you could create a plan view that would let you do this, albeit in a more round-a-bout way.

Background: Looking at this work item, it seems there exist both a Start Date and Actual Start Date. I'm querying the Tracking and Planning team about Start Date as it seems to be a built-in attribute that would be user-controlled (like Due Date) but I can't find a way to create an editor presentation that would allow me to set it. It's possible it is not meant to be visible (deprecated but not removed?). I tried creating several queries using Start Date and could not get any items returned.

If you edit a plan view, you will see a field called Actual Start Date. This field can be added to a plan column and does indeed seem to capture the moment at which the item was changed from New/Open to In Progress. You can also use this field in the Exclude filter in a plan view. I was able to create a plan view that excluded items that were not "In Progress" and were not started in the last 7 days (today-7). It's not as clean a solution as a query, but used in a sufficiently high-level plan should lasso the work items you are looking for.

Assuming picture posting will work, here is what my plan view looks like:
Plan view configuration for Started in Last Week

Millard Ellingsworth commented Aug 15 '13, 5:52 p.m.

Update: The Planning Component Lead confirms that the Start Date attribute is unused and unusable. I will follow up with a work item to hide or remove it. Actual Start Date is a planning-computed value not available to Work Items (at least at this time), so the solution above may be the best you can do for now.

An alternative (available now, hinted at by Ralph) would be to add a "Started Date" attribute to your work items and a CalculatedValue provider for it that records the transition from New to In Progress. If you feel the need to go that way, there are a variety of good discussions in the forum about calculated value providers and date fields and attribute customizations in general.

Geoffrey Clemm commented Aug 16 '13, 11:21 a.m.

Millard Ellingsworth commented Aug 16 '13, 11:40 a.m.

Sorry, Geoffrey, my bad. I should have added that I re-opened to get this fixed. 

Geoffrey Clemm commented Aug 16 '13, 12:28 p.m.

Thanks Millard ... I should have scrolled down further in the work item search results (given that this work item had the phrase "start date" in its summary, it's rather strange that it only got a 62% "search score" :-).   And I was amused to note that I was the one who submitted 90394!   (Although in my defense, it was 4 years ago, tomorrow, that I submitted it :-).

permanent link
Ralph Earle (25739) | answered Aug 14 '13, 3:03 p.m.
 As far as I know, out of the box, there is no way to set a time constraint on a status change (ie, from New to In Progress). A couple of possible solutions that would give you part of what you need are to set a "Creation date" or "Modified date" condition as "after (including) 7 days ago".

Maria Victoria Martinez Torino commented Aug 14 '13, 5:02 p.m.

Hi Ralph,
What about the "Start Date" attribute? I noticed that this attribute can be added as a filter at the Plan level and it is automatically populated when the work item changes from "Open/New" to "In Progress" state.
Is there any chance to add this attribute to the query and to play somehow with it in order to get what I need? If there is not, is there any other workaround for what I need?
Thanks again in advance!!

Ralph Earle commented Aug 14 '13, 6:13 p.m.
Victoria, that's a good idea to use the Start date attribute. Since we don't generally track it in my project, I hadn't thought of it. In the query editor, you would click on "Add Condition" and select "Start date" from the list of attributes. Set it as "after (including) 7 days ago" (or the appropriate time interval). If you want to display the start date in your query results, select the column display tab in the query editor, click on "Add column," and select it from the list of attributes.

If you want to go further, you can add attributes to your existing work item types, or change which attributes display. These processes are described in the help topics. Here are the URLs in the 4.0.3 help: for the Web client or the Eclipse client:  

Maria Victoria Martinez Torino commented Aug 15 '13, 10:18 a.m. | edited Aug 15 '13, 10:20 a.m.

Hi Ralph, I tried using the "Start Date" attribute but it seems not to be working or maybe I am missing something else here. I set the query config as follows:
+ Status is [Unresolved]
+ Status is not [Open (Action workflow)]
+ Type is [Task (a.k.a Action)]
+ Start Date after (including) [7 days ago]
Scenario A: I created a Task and left it in "Open" state => the query doesn't shown that Task, that should OK as it is expected behavior.
Scenario B: I moved the Task created in Scenario A from "Open" to "In Progress" state => the query still doesn't shown the Task. Should I assume this is a bug?
Query configuration

By the way, I am using RTC 4.0.2 (and configuring using eclipse client 4.0.2).

Thanks in advance!
PS: I have attached an image to this post, showing query configuration, please let me know if you can see it properly for I cannot see it.

Your answer

Register or to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.