The Azeeki Blog
Start Here
New to Azure Dev Ops reporting? Don’t worry, we’ve got your back. Start here to learn the basics.
Explore the Blog
Azure DevOps Reporting with a Sliding Date Window for Work Items
Azure DevOps sliding date window filtering provides a simple way to keep your OData datasets relevant without having to constantly update filter dates. In previous blog posts, I used a fixed date in the OData filter to return the work items that were created or updated after a specific date. The approach works well, but it requires you to update the date to keep the dataset relevant and to limit the result set. In this post, I will show how to replace the fixed date with a sliding date window. Instead of filtering from a hardcoded date, the query dynamically returns work items within the date window. This keeps the dataset smaller
Build Dynamic Azure DevOps Reports with OData Parameters
One of the first things I add to an OData query in a Power BI report is parameters. The parameters make the report easier to maintain, reuse, and adds flexibility as reporting requirements change. Instead of modifying the query every time you need a different date range, Team Project, or filter, you can simply update a parameter and let the report do the work. More importantly, parameters allow the filtering to happen at the source rather than inside Power BI after the data has already been retrieved. This reduces the amount of data returned and improves refresh performance. Parameters are a key design pattern that enables reports to adapt to changing business
Azure DevOps OData Reporting Without Analytics Views
Azure DevOps OData Reporting Azure DevOps OData Reporting provides an easier alternative to Analytics Views while maintaining a similar reporting experience in Power BI. One of the most common questions when moving away from Azure DevOps Analytics Views is how to recreate the same reporting experience without the complexity of creating multiple tables and complex relationships. The good news is that the ODATA entities (tables) provide a method to include the fields from related tables in a single query results. This allows for a denormalized dataset very similar to what Analytics Views (AV) provide. In this blog post, I will show how to create an ODATA query that is scoped to a
OData vs Analytics Views: Which Should You Use?
Azure DevOps OData Reporting vs Analytics Views Azure DevOps Reporting using OData can open up many opportunities when creating reports in Power BI. The Azure DevOps product group is encouraging users to utilize Analytics Views for your Power BI reports. For users just starting with ADO reporting in Power BI, this is the best method to use. It is a first-class connector in Power BI that connects to AVs and retrieves work items. As I have mentioned in other blogs, this is a great way to start reporting for smaller teams and projects with work item counts in the tens of thousands. You won’t find me trying to talk you out of
How to Export Azure DevOps Team Projects to Excel
Create the Team Project Report in Excel Export Azure DevOps Team Projects to Excel using the OData endpoint is an easy and efficient way to generate detailed reports. This method lets you view key information such as Team Project ID, project name, and last update time directly in Excel. By leveraging Excel’s powerful reporting features, you can quickly create list reports for analysis and share insights across your team. Plus, this approach integrates seamlessly with Power BI, making it ideal for advanced data visualization and Azure DevOps reporting. Open Excel and go to Menu -> Data -> From other Sources -> From ODATA Feed ODATA Query: Read this blog post if you
How to Export Azure DevOps Users to Excel
Generating a report of users from Azure DevOps (ADO) using Excel via the ODATA endpoint is a straightforward method to view user details, including their login names and the last time their information was updated. Excel serves as an excellent starting point for creating list reports. This blog post shows you to export Azure DevOps users to Excel to use for reporting. NOTE: The ODATA query used in this blog can be used in Power BI reporting. Using Excel for ADO Reporting: Open Excel and go to Menu -> Data -> From other Sources -> From ODATA Feed ODATA Query: Read this blog post if you are new to creating and testing
How to Create an ADO Work Item Progress Report
Create an ADO Work Item Progress Report with Analytics Views Enhance your team’s project management by using Analytics Views and Power BI to track the status of child work items, such as user stories or tasks. The ADO work item progress report helps your team quickly display completed work per sprint, identify overdue work items, and serve as an audit tool to highlight incorrectly assigned features per sprint. By monitoring the completion of these child work items, teams can accurately assess overall progress, identify potential bottlenecks, and ensure that all project aspects are on track. The Azure DevOps help file provides guidance on setting up the Feature Progress and Rollup reports using
How to Find the User Assigned to Work Items
The Use Case for Finding Users Assigned to Work Items A customer faced an issue where an employee who had left the company was deactivated in Active Directory (AD). They needed to identify all the work items assigned to this user, but the user’s name wouldn’t appear in Azure DevOps (ADO) when using the work item query editor. To resolve this, I ran a set of ODATA queries to help them identify the work items in which the user was assigned. Review this blog if you are new to ODATA and need to understand how to run the queries. The customer wanted to create a work item query and export to Excel
How to Display the ADO Sprint Status in a Power BI Report
Why Display the ADO Sprint Status on a Power BI Report Analytics Views (AV) provide the iteration start and end dates and the full path. It does not provide a field to show the past, future, or current iteration like on the task view in Azure DevOps (ADO). It is extremely helpful in the Power BI reports to display the sprint status to quickly filter the relevant work items. This is what it looks like on the Sprints view for the Task board: On the backlog, you see the start and end dates for the sprint: The Iterations ODATA entity includes the IsCurrent field, but it does not indicate whether iterations are
How to Parameterize the ODATA Queries in Power BI
Parameterize the ODATA Queries Parameterizing OData queries in Power BI reports and Fabric Dataflows makes it much easier to reuse the same solution across multiple Azure DevOps team projects. This is especially helpful when building an organizational reporting model where the structure of the data remains consistent but the team project changes. In this example, the OData query is scoped to an Azure DevOps organization and single team project. The Power BI parameters contain these values instead of hardcoding them into the URL. This approach simplifies maintenance, reduces duplication, and allows you to quickly onboard additional team projects without rewriting or copying queries. This blog post shows how to add the parameter
How to Find a Custom Work Item Field in ODATA
Find a Custom Work Item Field Azure DevOps work item templates offer the flexibility to customize by adding new fields, allowing teams to tailor the platform to their specific software processes. These newly added fields will automatically appear in the Analytics Views list. However, if your data source is ODATA, you will need to locate this field using a metadata query to correctly use the field in the ODATA query. This blog shows you how to find a custom work item field added to the ADO work item template and use in the ODATA query. For example, the Team Triage field is added to the Bug work item template. The new field
Include Iteration and Area Path to an ODATA Query
Why Include the Iteration, Area Path, and Other Fields to the ODATA Query The Azure DevOps Analytics Views offer direct access to fields like iteration, area path, project name, and user name within the dataset for reporting purposes. However, incorporating these values directly into the dataset using an ODATA query requires additional effort. The ODATA tables feature navigation properties that enable users to include related entities such as Area Paths and Iterations in the result set. Without utilizing these navigation properties, the result set for the Area Path will only return the AreaSK field, which merely contains a key value rather than the actual Area Path. By leveraging these navigation properties, you
Learn How to Select the Data Model for Azure DevOps Reporting
Options to Select the Data Model for Azure DevOps Reporting When it comes to data reporting, particularly with Power BI for visualizing Azure DevOps data, selecting the right Power BI data model for Azure DevOps reporting is crucial. A well-chosen data model simplifies the management of visualizations and enhances the efficiency and comprehensibility of measures. There are various options available for working with Azure DevOps data, and this blog highlights the models I have successfully used in customer reports. While not exhaustive, these models have proven to be highly effective for reporting purposes. This blog post will help you select the data model for Azure DevOps reporting in Power BI. Single Fact
Visualizing ADO Workitems: Epics to Tasks with Rollup Metrics
Visualizing ADO Workitems in a Hierarchial Data Model To effectively visualize the hierarchical structure of work items in Azure DevOps (ADO) within Power BI, you need to create a data model that mirrors ADO’s inherent structure. This involves linking work items through their Workitem ID and ParentworkitemID fields, establishing a parent-child relationship. The hierarchy starts at the top with work items like Themes or Epics and cascades down to the lowest level, such as Tasks. The primary objective of this data model is to enable cascading visuals in Power BI reports, which is particularly useful for viewing top-level work items like Epics and their associated child work items, such as User Stories.
Easily Create a Single Table Data Model Report
Easily Create a Single Table Data Model for Effective Reporting To create an effective ADO report, you don’t need a complex data model if your goal is to provide summary information. Using an Analytics View is the easiest way to start reporting, as it includes most of the necessary details about work items. Additionally, it encompasses information about iterations, area paths, team projects, and users. This is called a composite data model as it retrieves data from other analytics tables and displaying it in a single result set. The data model discussed in this blog post consists of a single table, but you can add a calendar table if you need to
Easily Create a Work Item Rollup Calculations Report
Create Work Item Rollup Calculations with a Hierarchy Data Model in Power BI Generating reports on Azure DevOps (ADO) work items in Power BI can be quite challenging, especially when dealing with hierarchical relationships. ADO’s current reporting capabilities only support a single-level relationship, linking child work items to their parent through the ParentWorkitemID field. This limitation complicates the process of creating reports that accurately reflect the hierarchical structure of work items, making it difficult to generate calculations for lower-level work item types, such as tasks, which roll up to higher-level work item types like Epics or Features. Consequently, teams struggle to gain insights into their project’s progress at the feature or epic
How to Get Started with Azure DevOps Reporting
How to Get Started with Azure DevOps Reporting using Power BI If you’ve found this blog, it’s likely because you’ve encountered the constraints of reporting within Azure DevOps (ADO). While ADO is sufficient initially, as your team or organization evolves in its development practices, you start to seek out reporting capabilities that align more closely with your specific team processes. The built-in reporting in ADO is designed for a wide user base and may not offer the level of customization required by many teams. The main roadblock for teams is understanding how to get started with reporting on ADO work items using Power BI. This blog aims to guide you through the
How to Publish and Setup Data Refresh for the Power BI Report
Publish and Setup Data Refresh for your ADO Report Publishing a Power BI report and setting up data refreshes through the Azure DevOps analytics interface is one of the last steps to go live with the report. This blog post will guide you through the essential steps to publish and setup data refresh for your Power BI report and configure the necessary credentials for accessing the Azure DevOps analytics interface. By following these instructions, you will be able to automate data refreshes, ensuring that your reports are always up-to-date with the latest insights from your Azure DevOps projects. Publish the Report To publish a report to the Power BI workspace, you need
Query ADO Workitems with Tag in Power BI
Quickly Query ADO Workitems with Tags using a ODATA When using Azure DevOps (ADO), you may observe that tags associated with work items are returned differently depending on whether you employ Analytics Views (AVs) or an OData query. AVs return a delimited list of tags within a single column, whereas OData returns the tags as a separate table, which must be considered in the data model. This inconsistency can result in confusion and challenges when attempting to generate consistent reports. This blog post provides a detailed walkthrough for query ADO workitems with tags in an OData query and establishing the relationship between the tags table and work items. ODATA QUERY: The advantage
Add a Date Table to Azure DevOps Reporting
Improving Data Insights with Time Intelligence Integrating a date table to Azure DevOps (ADO) reporting in Power BI is essential for generating reports that offer a thorough analysis of work items, pipelines, and testing in ADO. A date dimension is a table featuring one record per day, allowing the creation of measures that analyze data by various date attributes like week number, fiscal period, and day of year, among others. This capability enables the development of custom metrics tailored to your team’s processes, such as lead and cycle times or burn down charts. Additionally, it ensures consistency in analysis and simplifies the creation of trend reports on work items, pipelines, or testing