Examples of WS API queris on Timesheet related objects

Document ID : KB000057611
Last Modified Date : 14/02/2018
Show Technical Document Details

Issue

How to query on Timesheet related objects in Web Services API?

Resolution

There are two objects in Web Services API that are relevant to Time Tracker: TimeEntryItem and TimeEntryValue.

Example of a query on TimeEntryItem based on a Worproduct.Name:

https://rally1.rallydev.com/slm/webservice/v2.0/timeentryitem?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/11111&query=(WorkProduct.Name = us1)&start=1&pagesize=20&fetch=WorkProductDisplayString,TaskDisplayString,Values

and the relevant part of a result:

{
??????? "_rallyAPIMajor": "2",
??????? "_rallyAPIMinor": "0",
??????? "_ref": "https://rally1.rallydev.com/slm/webservice/v2.0/timeentryitem/77777",
??????? "_objectVersion": "3",
??????? "TaskDisplayString": "TA1: ta1",
??????? "Values": {
????????? "_rallyAPIMajor": "2",
????????? "_rallyAPIMinor": "0",
????????? "_ref": "https://rally1.rallydev.com/slm/webservice/v2.0/TimeEntryItem/77777/Values",
????????? "_type": "TimeEntryValue",
????????? "Count": 2
??????? },
??????? "WorkProductDisplayString": "US1: us1",
??????? "_type": "TimeEntryItem"
????? },


Example of a query on TimeEntryValue filtered by DateVal field, that fetches Hours,TimeEntryItem,Task,WorkProduct,User,Project:

https://rally1.rallydev.com/slm/webservice/v2.0/timeentryvalue?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/11111&query=(DateVal%20%3E%202010-01-01)&fetch=Hours,TimeEntryItem,Task,WorkProduct,User,Project&start=1&pagesize=20

Example of a query on TimeEntryValue filtered by WeekStartDate that will return a reference to a Project,WorkProduct,Task,User,Values, and the CreationDate:

https://rally1.rallydev.com/slm/webservice/v2.0/timeentryitem?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/1111&query=(WeekStartDate > 2013-09-01)&start=1&pagesize=20&fetch=Project,WorkProduct,Task,User,Values,CreationDate

Per Web Services API object model, there is a WeekStartDate attribute on TimeEntryItem, and it's being used in this query. But it is also possible to user CreationDate which is inherited fromPersistableObject.
Here are a couple of examples of valid query syntax:

(WeekStartDate > 2012-01-01)
(CreationDate > 2012-01-01)
(CreationDate = 2012-02-14T00:30:28.593Z)


Example of Values endpoint:

Vaues is a collection on TimeEntryItem.

https://rally1.rallydev.com/slm/webservice/v2.0/TimeEntryItem/5442504008/Values

Example of Collection Summaries specific to v2.0 of WS API:

WS API documentation has an example of Getting Collection Summaries. That example is adapted below to be used with Values . Here the collection is summarized by LastUpdated and Hours. Both are attributes of Values object.

https://rally1.rallydev.com/slm/webservice/v2.0/timeentryitem/5442504008?fetch=values:summary[LastUpdated;Hours]&pagesize=200

Here are the results in the browser:

User-added image

Example of a query that filters on UserName:

It is possible to narrow the query with two conditions, including the UserName


((User.UserName = user@co.com) AND (WeekStartDate > 2013-07-20))

https://rally1.rallydev.com/slm/webservice/v2.0/timeentryitem?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/1111&query=((User.UserName = user@co.com) AND (WeekStartDate > 2013-07-20))&start=1&pagesize=20&fetch=Project,WorkProduct,Task,User,Values,CreationDate,WeekStartDate

It is possible to add User specific attributes to the fetch and they will be fetched. In the query below DisplayName,Disabled are added:

https://rally1.rallydev.com/slm/webservice/v2.0/timeentryitem?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/1111&query=((User.UserName = user@co.com) AND (WeekStartDate > 2013-07-20))&start=1&pagesize=20&fetch=Project,WorkProduct,Task,User,Values,CreationDate,WeekStartDate,DisplayName,Disabled

Example of a query on TimeEntryValue filtered by TimeEntryItem:

It is possible to query TimeEntryValue based on TimeEntryItem, e.g.

(TimeEntryItem = "/timeentryitem/777")

and also add TimeEntryItem attributes to the fetch, including those related to User object:

https://rally1.rallydev.com/slm/webservice/v2.0/timeentryvalue?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/1111&query=(TimeEntryItem = "/timeentryitem/777")&start=1&pagesize=200&fetch=DateVal,Hours,LastUpdated,TimeEntryItem,Task,Project,WorkProduct,User,Disabled

Detailed example - Comparing a report in the UI to results obtained from WS API:

Let?s look at the timesheets report generated? on Reports tab in CA Agile Central UI and compare it to results of API query.

Here is a task associated with a leaf story, leaf1:

User-added image

The owner of the task entered one hour on Nov 8:



User-added image

and ran a custom report on Reports tab:

User-added image

User-added image

Next, a new task is added to the leaf1 story. It?s owner is a different user:

User-added image


This user logs in CA Agile Central and enters his hours in the timesheet:

User-added image
This user runs the same report on Reports tab:


User-added image

Note that this user nicktemp3@wsapi.com? is not an administrator, but can see times entered by the other users. ?
A simple query on a TimeEntryItem (without specifying fetch or a query string) in WS API ran by the same user shows these results:

User-added image

Here is an example of? a more specific query directly entered in the browser:

https://rally1.rallydev.com/slm/webservice/v2.0/timeentryitem?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/1111&query=(WeekStartDate > 2013-11-01)&start=1&pagesize=20&fetch=Project,WorkProduct,Task,User,Values,CreationDate

A few details of the response are noted below:

a. Total result count:

TotalResultCount: 2,

b. Task is? identified:

Task:
{
_rallyAPIMajor: "2",
_rallyAPIMinor: "0",
_ref: "https://rally1.rallydev.com/slm/webservice/v2.0/task/15146956992",
_objectVersion: "1",
_refObjectName: "task1 of leaf1",



c.?? WorkProduct is? identified:

WorkProduct:
{
_rallyAPIMajor: "2",
_rallyAPIMinor: "0",
_ref: "https://rally1.rallydev.com/slm/webservice/v2.0/hierarchicalrequirement/15146955701",
_objectVersion: "5",
_refObjectName: "leaf1",



d. Number of values endpionts in the returned json:

https://rally1.rallydev.com/slm/webservice/v2.0/TimeEntryItem/15147813634/Values
https://rally1.rallydev.com/slm/webservice/v2.0/TimeEntryItem/15147151146/Values


To continue with this example, next week the first user updated the timesheet. The same query on TimeEntryItem filtered by (WeekStartDate > 2013-11-01)will reuturn 3 results, and the third result will include a new Values endpoint:

https://rally1.rallydev.com/slm/webservice/v2.0/TimeEntryItem/15245932965/Values

e. Example of a query on TimeEntryItem filtered by CreationDate:

The filter below applies to TimeEntryitem, and not to the Task or WorkItem. This query returns only one result: a time entry created by this user on 2013-11-13:

https://rally1.rallydev.com/slm/webservice/v2.0/timeentryitem?fetch=Project,WorkProduct,Task,User,Values,CreationDate&pagesize=100&query=((CreationDate >= 2013-11-13T00:00:00.000Z) AND (CreationDate < 2013-11-13T23:59:59.999Z))

f. Example of a query on TimeEntryItem filtered by Task that fetches CreationDate:

Compare the result from the query (e) to result of query below, which returns 2 results:

https://rally1.rallydev.com/slm/webservice/v2.0/timeentryitem?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/12352608129&query=(Task.FormattedID = TA110)&fetch=CreationDate&start=1&pagesize=20

User-added image