Re: performing aggregates when using where queries

[ibuchanan]

Timothy,

 

This is a pretty meaty query you're after! I'll try to give you some pointers.

 

Your query (a) is probably not doing what you think. the asof parameter only works with history queries. Since you are trying to get an historical trend, that's probably what you want.

/rest-1.v1/Hist/Timebox?asof=2012-8-3T23:59:59

 

You can still do the where parameter to restrict the Timebox (or Timeboxes) you care about.

/rest-1.v1/Hist/Timebox?asof=2012-8-3T23:59:59&where=Timebox.Name='TC1211','TC1212','TC1213','TC1214'

 

You can't ask the Timebox itself to be filtered on Team, so you need to select and filter the Workitems:

/rest-1.v1/Hist/Timebox?asof=2012-8-3T23:59:59&where=Timebox.Name='TC1211','TC1212','TC1213','TC1214'&sel=Workitems[Team.Name='TC Sharks']

 

If all you want is the Sum for each Timebox, then you can just add that aggregation to the one attribute you have selected.

/rest-1.v1/Hist/Timebox?asof=2012-8-3T23:59:59&where=Timebox.Name='TC1211','TC1212','TC1213','TC1214'&sel=Workitems[Team.Name='TC Sharks'].ToDo[AssetState!='Dead'].@Sum

 

(Note, a "quirk" of the aggregations is they include deleted and template assets, while the same query without will auto-filter these. To make sure you get consistent results, use the AssetState!='Dead' filter with aggregation.)

 

And just to show off... ;) You can select multiple aggregations to get ToDo and Done.

/rest-1.v1/Hist/Timebox?asof=2012-8-3T23:59:59&where=Timebox.Name='TC1211','TC1212','TC1213','TC1214'&sel=Workitems[Team.Name='TC Sharks'].ToDo[AssetState!='Dead'].@Sum,Workitems[Team.Name='TC Sharks'].Actuals[AssetState!='Dead'].Value.@Sum

 

I hope that gets you closer to the information you seek.

 

Yours truly,

Ian Buchanan

Product Manager for Platform and Integrations

 

On Wednesday, August 15, 2012 5:46:12 PM UTC-4, Timothy Pratley wrote:

Hi,

I am trying to make a query to pull out 4 sprints worth of burn-down data to super-impose them on top of each other and identify trends. So far I have found two promising approaches:

a)  /rest-1.v1/Data/Timebox/475626/Workitems[Team.Name='TC%20Sharks'].ToDo.@Sum?asof=2012-8-3T23:59:59

b)  /rest-1.v1/Data/Workitem?where=Team.Name='TC%20Sharks';Timebox.Name='TC1211'

The advantage of (a) is that I can use the ToDo Sum aggregate which works great!

The advantage of (b) is that I don't need to lookup a timebox id as a separate step.

Is there a way I can combine both of these into one query that retrieves the sum of ToDo hours for a specific team and sprint asof a date?

Regards,

Timothy