Sort and Aggregate Functions in the VersionOne Open Platform

In the last post I discussed how to query VersionOne and create Story Cards using XSL.  To accomplish this we used the following query which returns all stories for a specific project and iteration:

http://localhost/VersionOne/rest-1.v1/data/Story?sel=Name,Number&where=Scope.Name="Release 1.0";Timebox.Name="Month C 1st Half"

The API allows us to control the Order in which the results are returned using the 'sort' parameter.  To use this parameter you need to add &sort={Attribute} to the query.  As an example, if I want the results sorted by importance, I would issue the following query

http://localhost/VersionOne/rest-1.v1/data/Story?sel=Name,Number&where=Scope.Name="Release 1.0";Timebox.Name="Month C 1st Half"&sort=Order

If I wanted them sorted by Estimate, I would issue this query

http://localhost/VersionOne/rest-1.v1/data/Story?sel=Name,Number&where=Scope.Name="Release 1.0";Timebox.Name="Month C 1st Half"&sort=Estimate

You can sort on multiple attributes by using a comma to separate the attribute.  In the next example the server will return the results sorted by Estimate and then by Name.

http://localhost/VersionOne/rest-1.v1/data/Story?sel=Name,Number&where=Scope.Name="Release 1.0";Timebox.Name="Month C 1st Half"&sort=Estimate,Name

You’ll notice that by default data is retuned in ascending order.  You can reverse a sort by adding a minus sign (-) or declare ascending explicitly by adding a plus sign (+).  The following query will return data reverse sorted on the Name attribute

http://localhost/VersionOne/rest-1.v1/data/Story?sel=Name,Number&where=Scope.Name=%22Release%201.0%22;Timebox.Name=%22Month%20C%201st%20Half%22&sort=-Namehttp://localhost/VersionOne/rest-1.v1/data/Story?sel=Name,Number&where=Scope.Name="Release%201.0";Timebox.Name="Month%20C%201st%20Half"&sort=-Name

The VersionOne API also provides aggregate functions that allow you summarize a collection of data.  To demonstrate this I’ll use the 'Children' attribute on the Story asset.  This attribute is a collection of all the Task and Test defined for a given story.  The result for the following query will contain all the Task and Test for a Story called "Order Check"

http://localhost/versionone/rest-1.v1/data/Story?sel=Name,Number,Children&where=Story.Name="Order%20Check"

If we only wanted to know the number of Task and Test, we would use the Count aggregate function on the Children attribute.  Here’s the query

http://localhost/versionone/rest-1.v1/data/Story?sel=Name,Number,Children.@Count&where=Story.Name=%22Order%20Checkhttp://localhost/versionone/rest-1.v1/data/Story?sel=Name,Number,Children.@Count&where=Story.Name="Order%20Check"

Another useful aggregate function is @Sum, which can be used to add the values of numeric fields. Task and Test both have numberic attributes for the estimated effort (DetailEstimate) and an for the remaining effort(ToDo).  Have the Sum of these attributes is useful and can be obtained by adding Children.DetailEstimate.@SumChildren.DetailEstimate.@Sum and Children.ToDo.@SumChildren.ToDo.@Sum to the query. 

http://localhost/versionone/rest-1.v1/data/Story?sel=Name,Number,Children.@Count,Children.DetailEstimate.@SumChildren.DetailEstimate.@Sum,Children.ToDo.@SumChildren.ToDo.@Sum&where=Story.Name="Order%20Check"

We can now enhance our Story cards to include the number of task and test, the total estimated effort and the remaining work.  We can also display or print the cards in order of importance.  Here the complete query

http://localhost/VersionOne/rest-1.v1/data/Story?sel=Name,Number,Children.@Count,Children.DetailEstimate.@Sum,Children.ToDo.@Sum&where=Scope.Name="Release%201.0";Timebox.Name="Month%20C%201st%20Half"&sort=Order &xsl=Custom/StoryCards.xsl

Comments

[AndyEd]

You can play with Sort Orders in the VersionOne command set for the highly experimental Firefox command line plugin Ubiquity. Get the bits for Firefox at http://updates.versionone.com/ubiquity/