Portfolio Item Query Examples
The Query field, available in Custom List and Portfolio Kanban Board apps, provides you with a way to filter the data displayed using multiple criteria. You can enter SQL-style queries into the field to find specific portfolio items or their associated work items.
Portfolio items with specific text in the name
This example is useful if you use various naming conventions to identify different groups of portfolio items:
(Name contains "Technical Debt")
Portfolio items with a specific investment category
(InvestmentCategory = "Cost Savings")
Portfolio items with a specific custom field value
(c_MyCustomField = "MyCustomValue")
Create a custom grid that shows all leaf stories attached to a feature
((Feature.FormattedID = F3550) AND (DirectChildrenCount = 0))
(Release = null)
Features in current release
((Release.ReleaseStartDate <= today) AND (Release.ReleaseDate >= today))
Leaf user stories attached to features in current release
(((Feature.Release.ReleaseStartDate <= today) AND (Feature.Release.ReleaseDate >= today)) AND (DirectChildrenCount = 0))
Scheduled for a release
(Release.Name = "PSI 2013.Q1")
Portfolio items with a specific parent
(Parent.Name = "Design new login page")
Portfolio items with no parent
(Parent = null)
Portfolio items with no child portfolio items or user stories
(DirectChildrenCount = "0")
Portfolio items that have a state other than Discovering or Done
((State.Name !="Discovering") AND (State.Name !="Done"))
Portfolio items that have a state between Discovering and Done
In this example, the State field on portfolio items uses an ordered value to identify the custom states. Discovering is the first available state, and has a value of 1. Done is the final state, and has a value of 4:
((State.OrderIndex > 1) AND (State.OrderIndex < 4))
Portfolio items that have total completion percent (by story count) of 50% or less
(PercentDoneByStoryCount <= ".50")
Portfolio items that do not have tags that contain specific text
(Tags.Name !contains "Blue")
Portfolio items with two tags: Red and Blue
((Tags.Name = "Blue") AND (Tags.Name = "Red"))
Portfolio items with three tags: Red, Blue, and Green
(((Tags.Name = "Blue") AND (Tags.Name = "Red")) AND (Tags.Name = "Green"))
Portfolio items owned by Mike or Sally
((Owner.UserName = "email@example.com") OR (Owner.UserName = "firstname.lastname@example.org"))
Portfolio items that have an actual start date after November 5, 2012
(ActualStartDate > "2012-11-05")
Portfolio items that have a planned start date later than last week
(PlannedStartDate > "lastweek")
(PlannedStartDate > "today - 7")
Tip: Use the following date variables and date expressions: today, yesterday, tomorrow, lastweek, nextweek, lastmonth, nextmonth, lastquarter, nextquarter, lastyear, next year, today +
Portfolio items that have planned dates, but not all associated user stories are finished
(((PlannedStartDate != null) AND (PlannedEndDate != null)) AND (PercentDoneByStoryCount <= ".50"))
All child stories under a specific portfolio item
Set a custom grid app to view user stories, then enter the FormattedID of the parent portfolio item in the query:
(PortfolioItem.FormattedID = "F123")
Query on stories in a story hierarchy that rollup to a leaf Portfolio Item allocated to a specific release
In this example, we display a custom grid showing leaf stories not allocated to an iteration that roll up to a leaf level PI (Feature) assigned to release 2014.Q2:/p>
(((Feature.Release.Name = "2014.Q2") AND (Iteration.Name = null)) AND (DirectChildrenCount = 0))
Filter out features with no state or archived features
((Tags.Name = "RPM") AND (State.Name != null) AND (State.Name != "Archived"))
Portfolio items in progress
((PercentDoneByStoryCount > 0) AND (PercentDoneByStoryCount < 1))
This includes portfolio items that have had at least one story accepted.
((ActualStartDate != null) AND (ActualEndDate = null))
This includes portfolio items that have stories in progress but nothing has been accepted yet.
Finished portfolio items
((ActualStartDate != null) AND (ActualEndDate != null))
Recent portfolio items and portfolio items in progress
(((ActualStartDate != null) AND (ActualEndDate = null)) OR ((ActualStartDate != null) AND (ActualEndDate = LastQuarter)))
Below are some fields that may help you build a specific query. Not all fields are listed. To see if a field is available for use, check your Web Services API documentation.
|AcceptedLeafStoryCount||The number of user stories (without children) associated to a portfolio item with a state of Accepted.|
|ActualStartDate||The date work actually began on a portfolio item, generated when the first lowest-level story associated to a portfolio item is set with a state of In-Progress.|
|Description||Searches contents of the Description field. You can use contains or !contains conditions.|
|LastUpdateDate||The last date the portfolio item was edited or created, which can also be updated when associated work items are edited.|
|LeafStoryCount||The number of user stories (without children) associated to a portfolio item.|
|Parent||The next level up in your portfolio item hierarchy. Use this field to locate a group of portfolio items with a common parent.|
|PercentDoneByStoryCount||The percentage of user stories (without children) associated to a portfolio item that have a state of Accepted.|
|PercentDoneByStoryPlanEstimate||The percentage of user story (without children) plan estimates associated to a portfolio item that have a state of Accepted.|
|PlannedStartDate||The date work is anticipated to begin on the portfolio item.|
|State||The Kanban state of a portfolio item. Not to be confused with the Schedule State field of associated user stories.|
If your query is not returning the data you expect, see the suggestions below.
The Type field in app settings controls what level of portfolio items will be displayed. If your query does not return any results, you may be viewing the wrong level of the portfolio hierarchy.
|Custom Grid app||Portfolio Kanban Board app|
Correct parentheses syntax
Parentheses used in queries with multiple conditions must be balanced. For every condition in the query, you must add one ( to the front of the string. You must also end each condition beyond the first with )).
((Name = "A") AND (Name = "B") AND (Name = "C"))
(((Name = "A") AND (Name = "B)) AND (Name = "C"))
Correct field name syntax
While reviewing the Web Services API documentation, be sure to remember that many fields are objects, which contain child elements. For example, Tags are an object in CA Agile Central, but have an element to locate the Name.
May return inconsistent results
((Tags = "Blue") AND (Tags = "Green"))
((Tags.Name = "Blue") AND (Tags.Name = "Green"))
Also remember that custom fields in your workspace begin with "c_". For a custom field named Color:
(Color = "Blue")
(c_Color = "Blue")
Note that field values must be queried using the correct case. For example, if a username in CA Agile Central is capitalized as George@Vandelay.com, a query formatted as (Owner.UserName = "email@example.com") will not work. The value must match exactly in the query.
Ensure that your project scoping in the app settings menu is correct. A query will not return work items that reside in projects outside of your selection.
Queries will not return work items that reside in projects you do not have permission to view. Ensure that you have permissions in the projects you want to query.
How to build queries
Learn more about formatting syntax in the Query field.