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.

Query field

Examples

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))

Unscheduled features

(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")

Note: Querying in WSAPI for a portfolio item state, such as a state of Done, results in an error. To view results based on portfolio item state, you can use the filter field on the Custom List app.

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 = "mike@acme.com") OR (Owner.UserName = "sally@acme.com"))

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")

or

(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 + , and today - . For examples and usage details, see Date Variables.

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.

or

((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)))

Popular fields

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.

Syntax Description
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.

Common problems

If your query is not returning the data you expect, see the suggestions below.

Type settings

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
Custom Grid Portfolio Kanban

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 )).

Incorrect
((Name = "A") AND (Name = "B") AND (Name = "C"))

Correct
(((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"))

Correct syntax
((Tags.Name = "Blue") AND (Tags.Name = "Green"))

Also remember that custom fields in your workspace begin with "c_". For a custom field named Color:

Incorrect
(Color = "Blue")

Correct
(c_Color = "Blue")

Case sensitivity

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 = "george@vandelay.com") will not work. The value must match exactly in the query.

Project settings

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.

User permissions

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.

Feedback

Need more help? The CA Agile Central Community is your one-stop shop for self-service and support. To submit feedback or cases to CA Agile Central Support, find answers, and collaborate with others, please join us in the CA Agile Central Community.