All columns, types, and operators

You can use the find query language together with all columns, types and operators in Hansoft.

All columns in Hansoft have an ideal type associated with it. For example, both column Work remaining and Earned Value have the type Hours. Detailed description, Item name and Comments have the type Text.

The type describes how to use find on a column and it also specifies how the operators work.

Keywords and parameters can be explicitly typed. This means that when Hansoft evaluates the find query it will override the type that would otherwise have been assigned to the keyword or parameter. For example you might want to explicitly type a keyword to Text to avoid it only finding items assigned to a user with the same name you want to search for as text in other fields.

Note:  You can view the query syntax associated with each column in the report editor.

All columns

Column name Type Example Finds:
Assign tag Resource Assigntag:Resource(<Resource>) Items where the Assign tag column contains the resource <Resource>
Assigned to Resource Assignedto:Resource(<Resource>) Items where the Assigned to column contains the resource <Resource>
Board column Text BoardColumn:Text(<Name>) Items where the Board Column name contains <Name>
Board lane Text BoardLane:Text(<Name>) Items where the Board Lane name contains <Name>
Budgeted work Hours Budgetedwork= <Hours> Items where the Budgeted work column contains the value <Hours>

Only available in Task scheduling projects.
Bug priority Number Bugpriority= <Number> or <Priority> Bugs where the Bug status column contains the bug priority <Bug status>

Items where
Category Backlog category Category= <Backlog category> Items where the Category column contains the category <Backlog category>
Comments Text Comments:Text(<Text>) Items that contain <Text> in the comment section
Committed to Item Committedto:Item(<Item>) Items where the Committed to column contains <Item>
Confidence Confidence Confidence= <Confidence> Items where the Confidence column contains <Confidence>
Custom column date Time MyColumn= <Time> Items where the MyColumn custom column contains <Time>
Custom column drop list Custom column drop list MyColumn= <CustomColumnDroplist> Items where the MyColumn custom column contains the droplist choice <CustomColumnDroplist>
Custom column Function Number MyColumn:<Number> Items where the MyColumn custom column contains the number <Number>
Custom column hyperlink Text MyColumn:<Text> Items where the MyColumn custom column contains the text <Text>
Custom column multi line text Text MyColumn:<Text> Items where the MyColumn custom column contains the text <Text>
Custom column number Number Mycolumn:<Number> Items where the MyColumn custom column contains the number <Number>
Custom column users Resource MyColumn:<Resource> Items where the MyColumn custom column contains the resource <Resource>
Custom column text Text MyColumn:<Text> Items where the MyColumn custom column contains the text <Text>
Database ID Number DatabaseID= <Number> Items where the MyColumn custom column contains the number <Number>
Description Text Description:Text(<Text>) Bugs where the Description field contains <Text>

Only available in the QA view.
Detailed description Text Detaileddescription:Text(<Text>) Bugs where the Detailed description field contains <Text>

Only available in the QA view.
Duration Days Duration= <Days> Items where the Duration column contains a value equal to <Days>
Earned value Hours Earnedvalue= <Hours> Items where the Earned value column contains <Item>

Earned value is an additional module that can be activated for free.
Estimated days Days Estimateddays= <Days> Items where the Estimated ideal days column contains a value equal to <Days>
Finish Time Finish= <Time> Items where the Finish column contains the date <Time>

Only available in Task scheduling projects.
General condition - Hidden Boolean GeneralconditionHidden= true Items marked as hidden
General condition - Assigned to me Boolean GeneralconditionAssignedtome= true Items assigned to me
General condition - Deleted Boolean GeneralconditionDeleted= true Items that have been deleted
General condition - Epic Boolean GeneralconditionEpic= true Items that have been flagged as epics
General condition - Has sub items Boolean GeneralconditionHassubitems= true Items that have subitems (e.g., they are parents)
General condition - Has users Boolean GeneralconditionHasusers= true Items that have users
General condition - Not assigned Boolean GeneralconditionNotassigned= true Items that are not assigned
General condition - Ongoing assigned tasks Boolean  GeneralconditionOngoingassignedtasks= true All non-complete assigned tasks where the current date is between the start and end dates of the task/sprint.
General condition - Ongoing work Boolean GeneralconditionOngoingwork= true All non-complete items (sprints, sub projects, tasks) where the current date is between the start and end date of the task/sprint. In the case of releases, returns all non-complete releases occurring on or after the current date.
General condition - Out of office task Boolean GeneralconditionOutofofficetask= true Items set to out of office tasks
General condition - Release overdue Boolean GeneralconditionReleaseoverdue= true Items with overdue release dates
General condition - User story Boolean GeneralconditionUserstory= true Items with an attached user story
Hyperlink Text Hyperlink:Text(<Text>) Items where the Hyperlink column contains the text <Text>
ID Number ID= <Number> Items where the ID column contains a value equal to <Number>

Use the ID column to see the item ID. The item ID follows the order that items are added.
Immediate parent sub projects Item Immediateparentsubproject:Item(<Item>) <Item>s immediate sub projects
Immediate sub items Item Immediatesubitems:Item(<Item>) Items that have a set of direct subitems that match the specified set of items
Item name / description Text Itemname:Text(<Text>) Items where the Item name column contains the text <Text>
Item type Item type Itemtype= <Item type> Items of the type <Item type>
Last commented on Time Lastcommentedon= <Time> Items where the Last commented on column contains the date <Time>
Last read comments Time Lastreadcomments= <Time> Items where the last comment was read on the date <Time>
Last updated by Resource  LastUpdatedBy=Resource(<Resource>) Items where the Last updated by column contains the resource <Resource> 
Last updated on Time Lastupdatedon= <Time> Items where the Last updated on column contains the date <Time>
Leaf sub items Item Leafsubitems:Item(<Item>) Items with a set of leaf sub-items recursively (e.g., they do not have any children) that matches the specified set of items
Level Number Level<3
Items in the first three hierarchy levels: 0, 1, 2
Linked to item Item Linkedtoitem:Text(<Item>) Items where the Linked to item column contains the item <Item>
Originally created / reported by Resource Originallycreatedby= Resource(<Resource>) Items where the Originally created by column contains the resource <Resource>
Originally created on Time Originallycreatedon= <Time> Items where the Originally created on column contains the date <Time>
Parent sub projects Item Parentsubprojects:Item(<Item>) Items with a parent set that matches the specified set of items
Pipeline tasks - Can start now Boolean PipelinetasksCanstartnow= true Items with a pipeline that can be initiated

If the entire pipeline has been completed, it will not be returned using this syntax.
Pipeline or Workflow Workflow / pipeline and status PipelineorWorkflow= MyPipeline Items with an attached pipeline or workflow named MyPipeline
Points Points Points= <Points> Items where the Points column contains a value equal to <Points>
Predecessor Item Predecessor= <Item> Items with predecessor items named item <Item>

Only available in Task scheduling projects.
Product backlog priority Number Productbacklogpriority= <Number> OR <Priority> Items where the Product backlog priority column contains a value equal to <Number>/<Priority>
Project Project Project= <Project> All projects named <Project>

Useful when filters items in the Portfolio view.
Release tag Text Releasetag:<Text> Items where the Release tag column contains the item status <Text>
Risk Risk Risk= <Risk> Items where the Risk column contains the risk value <Risk>
Scheduled condition - Completed Boolean ScheduledconditionCompleted= true Scheduled tasks that are currently completed
Scheduled condition - Critical path Boolean ScheduledconditionCriticalpath= true Scheduled tasks that are currently on a critical path
Scheduled condition - In progress Boolean ScheduledconditionInprogress= true Scheduled tasks that are currently in progress
Scheduled condition - Incomplete Boolean ScheduledconditionIncomplete = true Scheduled tasks that are currently incomplete
Scheduled condition - Not started yet Boolean ScheduledconditionNotstartedyet= true Scheduled tasks that have not started yet
Scheduled condition - Overdue Boolean ScheduledconditionOverdue= true Scheduled tasks that are overdue
Severity Number Severity= <Number> OR <Severity> Items where the Severity column contains a value equal to <Number>/<Severity>

Only available in the QA view.
Sprint priority Number Sprintpriority= <Number> OR <Priority> Items where the Sprint priority column contains a value equal to <Number>/<Priority>
Start Time Start= <Time> Items where the Start column contains the date <Time>

Only available in Task scheduling projects.
Status Item status Status= <Item status> Items where the Status column contains the item status <Item status>
Steps to reproduce Text Stepstoreproduce:Text(<Text>) Bugs where the Steps to reproduce field contains the text <Text>

Only available in the QA view.
Sub items Item Subitems:Item(<Item>) Items with a sub item named <Item>
Sub project path Text Subprojectpath:Text(<Text>) Sub project path for items that contain the string <Text> within their name
Successor Item Successor= <Item> Items with successor items named <Item>

Only available in Task scheduling projects.
User name Resource Username:Resource(<Resource>) Items where the User name column contains the resource <Resource>
User story Text Userstory:Text(<Text>) Items where the User story column contains the text <Text>
Watch Resource Watch:Resource(<Resource>) Items where the Watch column contains the resource <Resource>
WBS Text WBS=>"2.1" Items where the WBS column begins with "2.1"
Work remaining Hours Workremaining= <Hours> Items where the Work remaining column contains a value equal to <Hours>
Workflow status set by user Resource (Userthatchangedtoworkflowstatus<ProjectName><WorkflowName><Workflowstatus>:Resource("<Resource>")) Items where a specified user changed to a specified workflow status
Workflow status set on date Time Timewhenworkflowstatus<ProjectName><WorkflowName><WorkflowStatus>wasset = <Time> Items where a specified workflow status was set on a specified date

Text type

The following operators can be used on text.

Description Operator
Contains :
Contains case sensitive ::
Not contains !:
Not contains case sensitive !!:
Starts with =>
Starts with case sensitive ==>
Ends with = <
Ends with case sensitive == <
Equals to =
Equals to case sensitive ==
Not equals to !=
Not equals to case sensitive !!=
Greater than >
Greater than case sensitive >>
Less than <
Less than case sensitive <<
Greater than equal >=
Greater than equal case sensitive >>=
Less than equal <=
Less than equal case sensitive <<=

Example

Description= Code returns all items that have a description that exactly matches Code

Explicitly typing

Text(keyword to type)

Example:

Resources:Text("john") returns all items with a resources column containing the text john

Number types

The following operators can be used on a number.

Description Operator Synonyms
Equals to = ==   :   ::  =>  ==>  = <  == <
Not equals to != !!=   !:   !!:
Greater than > >>
Less than < <<
Greater than equal >= >>=
Less than equal <= <<=
Contains (needs explicit typing to text, see example below) :  

Example

DatabaseID= 1193 returns the item that has Database ID 1193

DatabaseID:"11" returns items that has a Database ID which contains number 11

Explicit typing

Number

Example:

Estimatedidealdays >= 5.30

returns all items that have estimated ideal days greater than or equal to 5.30

Boolean types

Boolean columns can either be of value true (represented by number 1) or false (represented by number 0).

The following operators are ideally used on columns typed as boolean.

Equals to =
Not equals to !=

Since the Boolean column returns a number, the operators for the Number type can also be used.

Example

ScheduledconditionInprogress= 1 returns all scheduled tasks that are currently in progress

Explicitly typing

true or false

Example:

ScheduledconditionOverdue= true

returns all scheduled tasks that are overdue

Hours and Days types

Hours and days are used on columns such as Duration and Work remaining and are specified by a d for days and h for hours in the end of a number.

If you write no letter after the number Hansoft will auto type to the default type for that column (for example Duration will auto type to days if you write Duration > 5).

Days and hours can be converted between each other so you can search for example Duration > 16h and it will automatically convert the duration expressed as days to hours. The conversion always assumes 8 hour days.

Operators for use with Hours or Days type

Since the Hours and Days are a number, the Number type operators apply.

Example

Duration > 5d returns all scheduled tasks that have a duration longer than 5 days

Explicit typing

Xd for days

Xh for hours

Example:

Work remaining <= 3.5d

returns all items that has work remaining less than or equal to three and a half days

Points type

The following operators can be used on points.

Since points are expressed as a number, the Number type operators apply.

Example

Points > 20 returns all items with more than 20 points

Explicit typing

Xpoints

Example of explicit typing

Points <= 10points returns all items with less than or equal to 10 points

Item type

The type item specifies one or several of any kind of item, task or bug in Hansoft.

Operators

Description Operator Synonyms
Any of items : ::
Any of items, some other > >>
Any of items, no other < >>
None of items !:  
None of items, some other !!:  
All of items, no other = ==
None of items or some other != !!=
All of items, some other >= >>=
At least all of items <= <<=
First is any of items => ==>
Non-first are any of items = < == <

Example

Committedto= "Email application - Prototype" returns all items committed to "Email application - Prototype"

Explicit typing

Item([ST, M, BI, AT, S, SP, B]"task name", ...)

ST is a Scheduled task

M is a Milestone

BI is a Backlog item

AT is a Agile task

S is a Sprint

SP is a Sub project

B is a Bug

Example of explicit typing

Linkedtotaskitembug : Item(BI"As an application user, I can reply to emails")

returns all items linked to backlog item (BI) "As an application user, I can reply to emails"

Linkedtotaskitembug= Item(BI"As an application user, I can reply to emails")

returns all items linked to ONLY backlog item (BI) "As an application user, I can reply to emails"

Linkedtotaskitembug : Item(BI"As an application user, I can reply to emails", BI"As an application user, I can forward emails")

returns all items linked to backlog item (BI) "As an application user, I can reply to emails" or backlog item (BI) "As an application user, I can forward emails"

Resources type

The type people specifies both individual users and user groups.

The following operators can be used on people.

Description Operator Synonyms
Any of selected : ::
Any of selected, some other > >>
Any of selected, no other < >>
None of selected !:  
None of selected, some other !!:  
All of selected, no other = ==
None of selected or some other != !!=
All of selected, some other >= >>=
At least all of selected <= <<=
First is any of selected => ==>
Non-first are any of selected = < == <

Example

assignedto = "Example Proj Manager"

returns all items that have only "Example Proj Manager" assigned

Explicit typing

R specifies individual resource and is written before the typed keyword

G specifies a resource group and is written before the typed keyword

resource([R or G]"keyword to type", [R or G]"keyword to type" etc.)

Example:

Lastupdatedby:resource(G"Business Unit 3", R"Example Proj Manager")

returns all items last updated by a user in user group "Business Unit 3" or to the individual user "Example Proj Manager"

Using the currently logged in user as a resource type

loggedinresource

Example:

assignedto:Resource(loggedinresource)

Time type

The time type is used for all kinds of date columns such as Start and Last Commented on. All dates are specified in the ISO 8601 format YYYY-MM-DD.

The following operators can be used on dates and times.

Description Operator Synonyms
On date = ==   :   ::   =>   ==>   = <   == <
Not on date != !!=   !:   !!:
After date > >>
Before date < <<
On or after date >= >>=
On or before date <= <<=

Example

Start >= 2009-10-01 returns the scheduled tasks that have a start date on or after 1st of October 2009

Explicit typing

Time

Example:

Finish <= 2009-10-05

returns the scheduled tasks that have a finish date on or before 5th of October 2009

Syntax

There are three ways to specify a date.

Gregorian date and time YYYY-[MM[-DD]][:HH[:MM[:SS]]]
ISO week, day and time YYYY-['W'WW-[D]][:HH[:MM[:SS]]]
Offset from the current time Now[(+,-)X(Y,M,D,W,H,m,S)][:HH[:MM[:SS]]]

A date is recognized as such when an integer with a - after is found. You don't need to specify the whole date and time, but can stop at any precision (see below). You cannot include any white space within the date expression and thus need to write the whole date without any spaces.

When searching on dates or time the keyword now can be used (start >= now). Together with keyword now you can also add + or - and

Second s
Minute m
Hour h
Day d
Week w
Month M
Year y

Example of now syntax

Start > Now-3M

returns scheduled tasks with a start date greater than three months ago.

LastUpdatedOn > Now-2h AND LastUpdatedOn <= Now

returns all items, tasks or bugs updated the last 2 hours.

Precision

When specifying a date or time it will implicitly have a precision attached to it. The precision is determined by the amount of accuracy that the date is specified with.

The precision of the time/date determines the range of time that the search sees as a match. If you for example specify a specific hour (2008-08-09:19) all times that fall within that hour will be matched in the search.

Precision examples

Syntax Accuracy
2008- Year
2008-08 Month
2008-08-09 Day
2008-08-09:19 Hour
2008-08-09:19:30 Minute
2008-08-09:19:30:11 Second
2008-W5 Week
2008-W5-1 Day
2008-W5-1:09 Hour
2008-W5-1:09:30 Minute
2008-W5-1:09:30:11 Second
Now Second
Now+1y Day
Now+1.5y Day
Now+1M Day
Now+1.5M Day
Now+1w Day
Now+1.5w Hour
Now+1d Day
Now+1.5d Hour
Now+1h Hour
Now+1.5h Minute
Now+1m Minute
Now+1.5m Second
Now:09 Hour
Now:09:30 Minute
Now:09:30:11 Second

Ranges

You can also specify a time range when specifying time. This is done with the FromDateToDate keyword.

Example

Start = FromDateToDate(now-2y, now+1y)

returns scheduled tasks with a start date from two years ago to one year into the future.

Drop list types

The drop list types are:

  • Confidence
  • Severity
  • Priority
  • Item status
  • Risk
  • Backlog category
  • Item type
  • Project
  • Bug status
  • Custom column drop list
  • Workflow
  • Workflow Status
  • Pipeline

Pre-defined and custom drop lists all follow the same pattern with regards to how to find on them. Please note that the drop lists here are separate types.

The following operators are ideally used on drop list types:

Equals to =
Not equals to !=

Since the drop list types are represented as a number, the operators for the Number type can also be used. This can make sense when the drop lists have a specified order such as Severity. You could then search for all items that have a severity worse than or equal to B: Severity <= Severity(B)

Example

Confidence = "High confidence"

ItemStatus != Completed

Project = "Development project"

BugStatus = Assigned

MyCustomColumnDropList = "Drop list item A"

Pipeline = Cutscene

Workflow = "Sign off"

Explicit typing

Confidence(High, Medium or Low)

Risk(High, Medium or Low)

Priority(High, Medium or Low)

BacklogCategory("Requirement", "Enhancement", "Technology upgrade", "Bug (A)", "Bug (B)", "Bug (C)" or "Bug (D)")

Severity(A, B, C or D)

ItemStatus("Not done", "In progress", "Completed", "Blocked" or "To be deleted")

ItemType("Scheduled task", "Milestone", "Backlog item", "Agile task", "Sprint", "Sub project" or "Bug")

Project(Project name)

BugStatus([Project name], Bug status name)

ColumnDrop(Column Name, Custom drop list item name)

Pipeline([Project name], Pipeline name)

Workflow([Project name], Workflow name)

WorkflowStatus([Project name], Workflow name, Workflow status)

When searching in the To do list or Global find the [Project name] mentioned above is relevant, because in these circumstances you need to specify the project you want to specify for example a bug status from.

When explicitly typing drop list types some of the values are fuzzily matched. For example BacklogCategory("Bug (A)") is the same thing as BacklogCategory(BugA) or BacklogCategory("A Bug").

Example of explicit typing

Severity(A)

returns all bugs that has Severity A

Project("My game project")

returns all items in project "My game project"

Pipeline("Cutscene")

Pipeline("My project","Cutscene Generic")

WorkflowStatus("Sign off", "First sign off status")

WorkflowStatus("My project", "Sign off", "First sign off status")

Column type

It is possible to compare two different columns by using the column type.

Example

Workremaining > Column(Estimatedidealdays)

returns all items where the work remaining exceeds the estimated ideal days.