Advanced Filters for Views and Visual Reports help to select, hide or highlight data when the exact filtering rule you need cannot be built using more simple and intuitive Basic Filters and Visual Filters. Filters created in Advanced Mode are much more powerful. You can create very complex queries with them.
Using Advanced Filters in views
As an example let's open any Board view and apply permanent Advanced Filter to its setup. Press Actions > Set up view button for that, then navigate to Setup tab.
Type ? in the filter field to initiate Advanced Mode.
You can filter rows, columns and entities in cells.
3 filters are available for views: rows, columns and cards:
If you are curious to know what's shown on the screenshot above:
- Columns: Filter out all entities in the "Closed" state
- Cards: Filter out all entities in their final state (i.e. "Done" entities) that are assigned to me
The language of filters is quite powerful, but not easy to grasp initially. Here, you'll find the main concepts and learn how to use Advanced Filters effectively.
Entities, Properties and Collections
To know what you can filter, you need to understand the data model. There are many entities in Targetprocess, including Project, Iteration, Release, User Story, Bug, Task, and Feature. These entities have various properties.
Let's take the most common entity — a User Story. It has quite a few properties and collections... here are some of them:
- Name, Description and Comments: user story name, text description and comments stream
- EntityState: current state (such as Open, In Progress, Done)
- Assignments: users assigned to a user story under variours roles
- Tags: tags added to the user story
- Custom Fields: values set in custom fields of a user story
- CreateDate, StartDate, EndDate: dates when a user story was created, started or completed
- Release, Iteration, TeamIteration: release, sprint (iteration) and team iteration to which the user story is assigned
- Tasks: collection of related tasks
- Bugs: collection of related bugs
- Project and TeamAssignments: project and teams the user story is assigned to
- Inbound and Outbound Relations and Assignables: collection of related entities
All of these properties (and many more) can be used to filter through User Stories.
Fields and properties of most frequently used entities
See the complete list of entities with all their properties here: https://md5.tpondemand.com/api/v1/index/meta.
Filters and Filter Combinations
OK, so let's try to apply some filters. You've just created a new view with User Stories. In this image, we are seeing all User Stories that have a rest tag:
Here are the two very basic filters you can apply:
|Assignable||?"abc" in Name||See only the stories with "abc" in Name|
|Assignable||?"abc" in Name and "abc" in Description||See the stories with "abc" in Name and in Description|
You immediately see that you can combine the filters together:
?filter1 and filter2 and filter3
You can use or as a keyword as well. For example, if we want to find all the User Stories that have "abc" in the Name and Description, in addition to all User Stories that have "navigation" in the Name:
?("abc" in Name and "abc" in Description) or ("navigation" in Name)
We use brackets to separate the filters, like this:
?(filter1 and filter2) or filter3
The sky is the limit here; it's possible to create some very advanced filters. But let's get back to basics first. Basically, you can filter by any property. Let's say we want to show User Stories from a specific Iteration. Here's the filter:
?"#1.1" in Iteration.Name
This means the User Story entity has a related Iteration entity, and the Iteration entity has the Name property. We use a dot (.) to separate the entity and its property:
Alternatively, we can use Iteration.Id for the same purpose:
?Iteration.Id is 1395
Here we see a new operator:
Field names are not case sensitive, so you can write "iteration.id is 1395" if you're feeling lazy. However, keep in mind that some filter formulas are case sensitive, as explained further below.
Here's a list of all supported operators:
|is not||not equal to|
|>=||greater or equal to|
|<=||less or equal to|
|not in||does not have|
Some more basic examples:
|Assignable||?CreateDate > '01-Mar-2012'||extract all the stories created after March 1|
|Assignable||?Priority is "Must Have"||stories with the Must Have priority|
|User Story / Task / Bug||?TimeSpent > 0||stories with time spent > 0|
|Assignable||?"plugin" not in Tags||stories with no "plugin" tag|
Filters for blank or empty values
Sometimes you want to extract entities that have no such property or where the field is blank. None custom value is used for this purpose.
|Assignable||?Release is None||entities that are not assigned to a Release|
|Assignable||?Release is not None||entities that are assigned to a Release|
Some filters, such as the the 'not' negative case filter, are case sensitive.
?not Tags.where(Name is "ABC") - works
?Not Tags.where(Name is "ABC") - doesn't work
Filters by parent entities
Filter for parent Feature for User Stories:
?Feature is "Feature A"
Filter for parent Epic for User Stories
If on your view User Stories are selected as cards then there are two ways to filter them by parent Epic within their parent Feature:
?Feature.Epic is "Epic 1" ?Feature.Epic.Name is "Epic 1"
If User Stories are distributed by Features, it is enough to filter just Features by parent Epic:
?Epic is "Epic 1" ?Epic.Name is "Epic 1"
Filters for collections
The User Story entity has quite a few collections related to it, including Comments, Tasks, Bugs, Times, etc. How can we filter based on collections? Is it possible to filter out all User Stories that don't have any related Tasks? Or is it possible to only see the stories that contain Bugs with a specific name? Yes, it is possible!
There are two operators you can use for collections:
Count: how many elements are there in the collection?
Where: the inner filter in the collection. It is a very clever thing, so you can define filters that will be applied to the collection.
|User Story / Feature||?Bugs.Where("zoom" in Name)||gets User Stories or Features that have bugs with the word "zoom" in the ?name|
|User Story||?Tasks.Where(Effort > 0 and TimeSpent == 0)||gets User Stories that have Tasks with estimated effort and zero time spent|
|User Story||?Tasks.Count is 0||gets User Stories that have no related Tasks|
|User Story / Feature||?not Bugs.Where(Severity.Name is 'Laugh if notice')||stories or features without low Severity bugs|
So, you can create some pretty complex and intelligent filters! Targetprocess has almost no restrictions. We try to follow this rule: simple things should be simple, and complex things should be possible.
- What is the difference between 'Contains' and 'Where' operators?
- 'Contains' accepts an element and returns a boolean. 'Where' accepts a predicate and returns a boolean. For collections, most of the times you can think about 'Contains('X')' as 'Where(It is 'X')'.
- Filters by Entity States such as Open, In Progress, Done
- Filters for personal assignments: assigned users and roles; includes description of Me keyword
- Filters by dates such as ones when an entity was created, started or completed
- Filters by Name, Description and Comments: useful for searching by keywords, phrases and text patterns
- Filters by assigned Tags
- Filters by Custom Field values
- Filters for Releases, Sprints (Iterations) and Team Iterations; includes description of Current, Future, Past keywords
- Filters by assigned Projects and Teams: useful when capabilities of Projects and Teams selector do not fit your needs
- Filters by Relations
- Filters for Time entities: useful in Time List views and Visual reports by Time Spent
Hide rows and columns
More examples for your inspiration
Now it's time to construct some more complex filters. To combine filters, use the AND, or OR operators with brackets.
Extract all the items assigned to a specific user that were closed last month:
?AssignedUser.Where(LastName is 'Dubakov') and EntityState is "Done" and EndDate > TODAY - 30(days)
Extract all the Open items that have "process" in Name, Description or Tags:
?("process" in Name or "process" in Description or "process" in Tags) and EntityState is "Open"
Extract Bugs with the "ASAP" business value (priority) and User Stories with the "Must Have" business value (priority):
?(EntityType is 'Bug' and Priority.Name.Contains('ASAP')) or (EntityType is 'UserStory' and Priority.Name.Contains('Must Have'))