Time in State Report in Azure DevOps. Is it possible to do without Power BI?

SaaSJet
6 min readOct 25, 2023

The most popular way to analyze the time spent working on a particular Work Item is through reports in Power BI. It’s not that it’s convenient; it’s just that there was no other way. The official Microsoft documentation for the “time-in-state” query gives us cumbersome instructions on generating such a report. There are a lot of complicated steps to build a report and a chart to get information that still needs to be analyzed. And it takes time, and it is pretty expensive.

Solution. Automated generation of Time in State, State Count, and Transition Count reports are available with the Time in State for Azure DevOps extension. Let’s take a closer look at why Power BI is not always a magic wand for analytics. And how extensions from the marketplace can, on the contrary, relieve you of routine and complex processes so that you have more time to do the work itself rather than prepare for it.

Exploring Disadvantages of Power BI Reports for Azure DevOps Data Analysis

Creating Power BI reports from Azure DevOps data can provide valuable insights into your team’s development and project management processes. However, there are some potential disadvantages to consider when using Time in State reports in Power BI with Azure DevOps data:

Data Complexity. Azure DevOps data can be complex, especially when dealing with large organizations or projects. Creating meaningful reports in Power BI may require a deep understanding of the Azure DevOps data model, which can be challenging for some users.

Customization Overhead. Building custom reports in Power BI can be time-consuming. You may need to invest significant effort in designing, creating, and maintaining these reports, which can disadvantage small teams or organizations with limited resources.

Licensing Costs. Power BI is a paid service, and using it to create reports from Azure DevOps data can result in additional licensing costs. This can be a disadvantage for organizations looking to minimize their software expenses.

Learning Curve. Azure DevOps and Power BI have their learning curves. Users may need to become proficient in both tools to create compelling reports, which can be time-consuming and challenging.

Real-time Data. Power BI reports may not always reflect real-time data from Azure DevOps. There can be delays in data synchronization, which may not be suitable for situations requiring up-to-the-minute information.

Data Security. When creating Power BI reports from Azure DevOps data, you must be mindful of data security and access controls. Ensuring that sensitive information is protected and only accessible to authorized personnel can be challenging.

Maintenance. As your organization and its projects evolve, your Power BI reports will require maintenance to stay relevant. This means ongoing work to update and refine your reports as your needs change.

Integration Challenges. Integrating Azure DevOps data with Power BI might require connectors or custom scripts, and this integration process can be complex, especially if you have unique data requirements.

To mitigate these disadvantages, it’s essential to carefully plan your Power BI reporting project, invest in user training, maintain data quality in Azure DevOps, and regularly review and update your reports to ensure they remain valuable and accurate.

Time in State Extension for Calculating Azure DevOps Work Item States

So, the Time in State extension automatically generates the reports you need. Instead of a complicated configuration process in Power BI, you can get everything in just one click.

✅ Easy installation process. Just find the extension on the marketplace and install it on your instance.

✅ Calendar Configuration. Customize your work calendars to align with your specific requirements. Establish and oversee calendars that align with your work timetable. Tailor them to your preferred time zone, incorporate days off, and make additional adjustments.

✅ Reports. Time in State for Azure DevOps allows you to generate three distinct types of reports: Time in State, State Count, and Transition Count.

All reports are generated automatically based on your selected time, Work Item periods, and Queries. You can also export the generated reports to Excel and continue working with them or share them with other teammates who do not have access to your Azure DevOps instance.

✅ Charts. If you’re a fan of data visualization, you can generate a chart for each report type (in one of three types — bar, area, and pie). It is also possible to visualize the duration of a specific Work Item’s stay in certain states in the chart (One chart Work Item).

✅ Reporting Page Settings. Available settings when generating reports:

  1. When generating the reports, you can filter data by Query.
  2. Use the Filter Work Items functionality to choose what Work Items will be displayed: Created, Changed, or Closed.
  3. Work Items and Time Range Periods. If you want to generate Time in State for Azure DevOps report with specific date ranges, you need to set two types of dates:

Work Items range– for work item list selection.

E.g., You want to select work items that were closed last week.

Time range — for data calculation.

E.g., You want to get the time only for the chosen work items and a specific period.

*If you haven’t set it, it will calculate the time data for the whole period (option Any date — selected by default).

4. Time format.

5. Choose Calendar. You can select either a custom calendar you’ve previously created or the default 24/7 calendar to generate data.

6. Filtering Work Items. Use the Filter Work Items by Query functionality to customize report timeframes. You can select three date ranges from the drop-down list that allows sorting Work Items depending on the dates of creating, updating, or solving issues.

  • Created
  • Changed
  • Closed

7. Sorting Data. Click on the column to arrange the values in ascending or descending order. This sorting capability is available for every column.

8. Smart View. The smart view feature has been designed to enhance your ability to distinguish calculated values within the grid visually. It will highlight numbers across the range from the minimum to the maximum, enabling you to identify variations quickly. This highlighting functionality is accessible for all types of reports. To activate this feature, toggle the “Smart View” switch.

Consequently, you’ll receive a report with a range of highlighted figures on the page you’re viewing. The smallest values will appear subtly, while the highest ones will be bold and enlarged for easy identification.

_________________________________________________________________

The Time in State for Azure DevOps extension is not an absolute panacea but a tool for generating all the necessary reports. But in most cases, it significantly optimizes the time spent on their creation. So, why give it a try?

A 14-day trial period is available for you. The app is free for teams of up to 10 users.

Enjoy!

--

--

SaaSJet

Apps for Skyrocketing Efficiency! Atlassian Platinum Marketplace Partner. We create apps for Jira, monday.com, Chrome, Azure and others.