I posted a couple of weeks ago about the dashboards functionality within Azure DevOps. As I mentioned, it’s important to understand how to write queries in order to get the most from your dashboards. In this post we look at how queries work.
What are queries?
Queries work in the same way they do in databases. Just like tables in a database, Azure DevOps also has tables behind the scene and just like in a database where you can query those tables, you can do the same in Azure DevOps.
All queries are based on work items, which is actually a huge part of the product anyway, so the amount of data you have access to and have the ability to query is pretty large.
As you can see from the screenshot above, out of the box, there isn’t much to see, you get two main queries; Assigned to me and Followed work items. You should also be able to see that queries come in two folders. My Queries and Shared Queries. Queries which are saved in the My Queries folder are only visible to you, so in here you can save queries which only you need to use.
Any queries which you save in Shared Queries will be visible to anyone with permission in the project you are currently working in. Importantly, if you want to add a query to a dashboard, then the query must be saved in Shared Queries.
Creating a new query is very simple. From the Queries window, which you can find under Boards, simply click the New query button on the ribbon.
The query editor has a number of different options, let’s look at the key ones:
- Save query – This provides you with the option to save the current query you are working on, this is where you have the option to save it to either My Queries or Shared Queries.
- Column options – This button lets you select which columns are present in the output of the query, you can add columns as well as customise the order in which they are displayed.
- Copy query URL – Rather than talk people through finding your query, you can use this button to copy a URL to the query on your clipboard, you can then send this directly to anyone with access to your project.
- Filters – Filters are where you define your search criteria, think of it like your “where” clause in a SQL query. You can group results, use and/or operators and search for data using a number of different data operators.
On the far right side of the query editor, just under the ribbon you will notice a check box which says Query across projects. This option gives you the ability to execute your query across all projects which you have permission to look inside.
Let’s now create a simple query. This query will look for user stories which are in the next sprint and do not currently have any story points assigned. This query would be useful to help Scrum Masters ensure all stories have an appropriate value for the number of story points they take in the sprint.
This is actually quite an easy query to write, you only need three field criteria set, let’s look at the query in more detail.
- Work Item Type – This will define what type of work item we are looking at, default here will be a keyword [Any], since user stories are the only work item types with story points as a field, we can set this to User Story.
- Iteration Path – In Azure DevOps, the iteration path is the sprint which the work item belongs to. This is configured within the work item among other places. As we need to look at the next sprint, which is a dynamic value, we can use a keyword again, this is @CurrentIteration + 1, this tells the query to look in the current iteration, plus one. You also need to provide a team to search, if you are using multiple teams and want to look in multiple teams, just add another criteria in for that team.
- Story Points – As we are looking for any user stories with no story points set, we can simply set the operator to equals in this field with no value, this will show up user stories where the story points field is blank.
Once you are done, click Run query and your results will be displayed in the results pane at the bottom of the window. You can now save your query using the Save query button at the top ribbon. Remember to save in Shared Queries if you want to use it on a dashboard.
Adding to a dashboard
Now we have our query, let’s quickly add this to a dashboard, for the full steps, check out my post on Dashboards. Add a Query Tile widget to your dashboard and then configure the widget.
Select your query from the Query drop down, in my example I have changed the default background colour to white as well and added some conditional formatting to show the tile as amber when more than zero results are returned and then red when more than five results are returned.
On your dashboard, you can now see the tile you have configured with the results of your query.