Create an active bugs report in Power BI based on a custom Analytics view

Azure DevOps Services | Azure DevOps Server 2020 | Azure DevOps Server 2019

Using Power BI connected to Analytics views, you can create reports on your work items. Reports can show your current work data and also historical revisions of the work items.

In this article, you'll learn how to:

  • Create a custom Analytics view for active bugs
  • Generate a Power BI report based on that view
  • Filter the Analytics view
  • Create a trend chart
  • Create a card showing the current count of active bugs

Follow these steps to create a report in Power BI desktop that shows a daily active trend of bugs based on a custom Analytics view:

  1. From the web portal for Azure DevOps:
    a. Open Analytics views
    b. Create a custom Analytics view for your data.
  2. From the Power BI Desktop:
    a. Get the dataset defined by the Analytics view
    b. Create a trend report
    c. Apply filters to your trend report
    d. Create a card and apply the Is Current=True filter to show current count.

Prerequisites

To create a Power BI report that references an Analytics view, you must meet the following criteria.

  • You must be a member of a project. If you don't have a project yet, create one.
  • If you haven't been added as a team member, get added now. Anyone with access to a project, except stakeholders, can view Analytics views.
  • Have the View Analytics permission set to Allow. See Grant permissions to access Analytics.
  • Have installed Power BI Desktop February 2018 Update or later version. You can download this client application from the official Power BI Desktop download page.
  • Have tracked bugs for some period of time to generate a trend report.

Open Analytics to access views

Default Analytics views are defined for each project. You can view, modify, and create a custom view from Analytics.

If you don't see the project you want, choose the Azure DevOps logo to browse all projects.

For Azure DevOps Server 2019.1

After you install or enable Analytics, a set of default Analytics views are created for each project. You can view, modify, and create a custom view from Analytics.

For Azure DevOps Server 2019

After you install the Analytics Marketplace extension, a set of default Analytics views is created for each project. You can view, modify, and create a custom view from Analytics.

From your web portal, expand Overview and choose Analytics views.

Screenshot of expand overview and choose Analytics views.

From your web portal, expand Boards and choose Analytics views.

Screenshot of expand Boards and choose Analytics views.

If you don't see Analytics, check that it's enabled for the project collection and that you have permissions to view Analytics. See the Prerequisites for this article.

Create a view to only include Active bugs

From the web portal, Analytics view, choose plus icon New View to create a custom view. Fill out the forms provided in each tab as shown in the steps below.

Choose Continue to move to the next tab in the panel. If you need to revisit a tab, select the tab title.

Name your view and set to Private

  1. Give your view a name, such as Active bugs. Select Private view to save it under My Views. Otherwise, you can select Shared view to save it under Shared Views.

    General

    Choose Continue to move to the next tab.

  2. From the Work items tab, select the Project and Team that contain the data you want to report on.

    Here we choose the FabrikamFiber project and the Fiber Suite App team.

    Filter by team

    To add more projects or teams, select Add row icon to add a new row and then select the project and team.

  3. Under Backlogs and work items, select Add row icon to add a new row, and then select Bug from the list of work items types.

    Filter by "bug"

  4. Under Field criteria, choose the State field set to Active.

    filter active bugs

    Choose Continue to move to the next tab.

  5. In the next tab, Fields, we'll keep the most common fields selection. This selection will also include any custom fields that have been added to your project.

    Fields

    Choose Continue to move to the next tab.

  6. In the History tab, select Rolling period in days and enter 60 days. Keep the Granularity set to Daily. These selections will generate 60 snapshot rows of data for each active bug. Each day will include all the details of that work item as it was at the end of that day.

    60 days of history with Daily granularity

    To learn more about these defining trend data options, see Create an Analytics view, Select trend data options.

    Choose Continue to move to the next tab.

  7. On the last tab, Verification, select Verify view. The system will verify your view by running a test query against the dataset it defines and validate all your filter criteria.

    Verify the view

    Note

    Verification time will vary based on the amount of data defined in your view. Verify your view to make sure all the definitions are correct.

    Once your view successfully verifies, choose Save. You can then start using it in Power BI. Verification also returns an estimate of the number of rows in the dataset and the time it will take to load in Power BI.

    successful verification

    If your view fails to verify successfully, you'll get an error explaining the issue and pointing to a possible fix. Try changing the options you selected in the Work Items and History tabs to include less data, and then verify the view again.

Open Power BI desktop and load your view

  1. Open Power BI Desktop.
  1. Choose (1) Get Data, (2) Online Services, (3) Azure DevOps (Beta), and then (4) Connect.

    Connect to work tracking data

  2. Enter your organization name, the same Azure DevOps name you used to create the Active Bugs view, and the same project for which you defined the Active Bugs view.

    Azure DevOps dialog, Organization and project name

  1. Choose (1) Get Data, (2) Online Services, (3) Azure DevOps Server (Beta), and then (4) Connect.

    Connect to work tracking data

  2. Enter the URL for your server and collection, the same project for which you defined the Active Bugs view.

    Azure DevOps Server dialog, Collection and project name

  1. Expand the Private Views folder, choose the Active Bugs view you saved in the previous section, and then select Load.

    Active bugs load in Power BI

Need help with connecting? See Connect with Power BI Data Connector.

Create a daily bug trend report

  1. In your report, (1) select the Line chart visual, (2) enter work item id in the search field, and then (3) check the box for Work Item Id.

    Select work item field

    It will change your chart to a single dot.

    Tip

    To change the chart size, choose the View tab, Page View, and then select the Adjust Size option as shown. You can then resize the chart to your desired dimensions.

    Adjust chart size

  2. Select the context menu icon next to the Work Item Id field and change it from Count to Count (Distinct).

    Change Work Item Id field from Count to Count (Distinct).

  3. Next, (1) select the Date field in the fields list as your axis. To see a daily trend, (2) select the context menu icon next to the field and change it from Date Hierarchy to Date.

    Select Date, and change date hierarchy to date

    To view trends over time, you want to use Date and not Date Hierarchy. The Date Hierarchy in Power BI rolls ups everything into a simple number for the period. The day level in the hierarchy rolls all days to a number between 1-31. For example, April 3 and May 3 both roll up into number 3. This isn't the same as counting items per actual date.

    Tip

    If you need to modify your Analytics view, you can do so and then return to your Power BI report and refresh the data. Simply select the Refresh option as shown.

    Refresh report data

Filter your trend by Priority

  1. To group your bugs by Priority, search for the field in the list (a). Drag the Priority field into the Legend of your trend chart (b)

    Group by priority

  2. The chart now shows a daily distinct count of bugs, grouped by Priority.

  3. To just show only bugs with a priority of 0 and 1, select the context menu icon next to the Priority field to set the filters.

    Filter top priority bugs

    The trend chart is now grouped and filtered by high priority bugs.

Create a card for latest bug count

  1. Add a new page by clicking the plus sign (+) at the bottom of the page.

  2. Select the card visual, add the Work Item Id field, add the Work Item Type field, and select the context menu icon next to the field and change it from Count to Count (Distinct).

    The card now shows the number of active bugs times the number of days they existed within the selected time period. For this example, that number is 1066.

    Distinct count card, interim step

  3. To get the latest count, add Is Current as a page level filter, and select the True checkbox. The Is Current field is added automatically to all datasets, marking the rows that contain the latest revision of the work items.

    Latest active bug count

    The value on this card should match the count on the last day of the trend chart. You can get the value by returning to Page 1 and hovering over the last day as shown.

    Check of latest active bug count against trend report

Save your report

  1. Choose File > Save As to save your report to your local workspace.

  2. To publish your report to Power BI, choose the Publish tab. For more information, see Collaborate in your Power BI app workspace.

    Choose the Publish tab.

Continue to explore your data

Analytics views provide you with a great deal of power and flexibility to filter your data and generate useful reports quickly and easily using Power BI. Within a custom view, you can create datasets that span multiple teams or projects.

Next step