How to Maintain the BPM-HCM Portfolio & Ticket Operations Dashboard

Updating the BPM-HCM Portfolio & Ticket Operations Dashboard
Use this procedure to update the source files used by the BPM-HCM Power BI dashboard so that ticket operations, workload, milestones, and portfolio reporting remain current and reliable.

Purpose
This article provides the standard procedure for updating the source files used by the BPM-HCM Power BI report so the team can reliably monitor: active TeamDynamix (TDX) workload, Jira requests submitted to Workday Support Services (WSS), service intake/completions, assignment load, average time to close, milestone progress, project/change request details, the active Gantt view, and the approved annual roadmap.
Power Bi Report: BPM-HCM Portfolio & Ticket Operations Dashboard

Dashboard Overview
The dashboard is designed for daily BPM-HCM operational management and portfolio oversight. Key pages/tabs include:
  • At-a-Glance Operations: active TDX tickets, Jira/WSS submissions, service intake and completions
  • Workload & Performance: assignment distribution and average time to close
  • Portfolio Breakdowns: projects and change requests (summary and detail tabs)
  • Gantt View: active projects and change requests
  • Roadmap: year plan based on HCM director feedback and executive approval
  • Drill-Down Table: a consolidated table for deeper review and filtering

Data Sources
The dashboard uses the following reports/exports. Maintain the structure of each file to prevent refresh failures.
TeamDynamix (TDX) Reports
Report Name Used For
BPM HCM Ticket Management: HCM Tickets for Reports
Primary TDX ticket dataset		 At-a-glance ticket counts, assignment load, cycle time metrics, portfolio drill-down, and ticket-level detail pages.
BPM HCM Ticket Management: Jira Tickets, Milestones, & Instructional Materials and Report Tasks Links to Jira tickets, milestone completion tracking, instructional materials and reports completion, and milestone task tracking.
BPM HCM Ticket Management: Priority Assessment Priority grid/matrix to support governance and sequencing decisions for projects and change requests.
Jira (WSS) Export
The Jira dataset is exported by a user with Jira access from the Enhanced Ticket Search view. Because Jira saved tables are not shareable, each new employee must work with the Sr. Director to create the table view before exporting.

Files & Locations
All exported Excel files used by the dashboard must be uploaded to the BPM-HCM SharePoint site in the folder below:
SharePoint Location
Site: HCM Business Process Management
Library: Shared Documents
Folder: Power BI Reports for Ticket Audit
Link: https://uark.sharepoint.com/teams/HCMBusinessProcessManagement/Shared%20Documents/Forms/AllItems.aspx
Critical: Do not change the Jira export naming convention. The Power BI model expects the file name pattern below.
Required Jira Export File Name Pattern
_advanced-portal-requests-YYYY-MM-DD@HH_MM.xlsx
Example: _advanced-portal-requests-2026-02-25@10_53.xlsx
The date/time portion updates each export. Do not manually rename the file.

Update Workflow
Follow the steps below each time you update dashboard source files.
Step 1 — Export All Required TDX Reports
  • BPM HCM Ticket Management: HCM Tickets for Reports
  • BPM HCM Ticket Management: Jira Tickets, Milestones, & Instructional Materials and Report Tasks
  • BPM HCM Ticket Management: Priority Assessment
Export each report separately to Excel. Do not modify the report structure.
Step 2 — Export Jira Data
  1. Select Switch to the Enhanced Ticket Search view.
  2. Load the approved saved table view.
  3. Download/export to Excel.
  4. Do not rename the file.
Step 3 — Insert URL Columns & Run Macro
  • Jira: Insert new Column D, run Jira macro.
  • TDX HCM Tickets: Insert new Column B, run TDX macro.
  • Confirm URL column is populated before saving.
Step 4 — Upload to SharePoint
  • Upload all exported TDX reports and the Jira file.
  • Confirm upload to: Power BI Reports for Ticket Audit.
  • Ensure upload occurs before refresh window.

Add URL Field (Macro)
Both the Jira export and the TDX export require a URL column that contains the hyperlink address (not only the display text). This supports drill-through and navigation behavior in the Power BI dashboard.
Jira Export — URL Column
  • Insert a new column in Column D (do not overwrite any existing columns).
  • The hyperlink field must be in Column C so the macro can extract the URL into the new Column D.
  • Run the Jira macro below (copy/paste as-is).
Jira Macro (Copy/Paste)
Sub ExtractURLs_Jira()
    Dim cell As Range

    ' Jira: URLs are extracted from Column C and written to the inserted Column D
    For Each cell In Range("C2:C1220")
        If cell.Hyperlinks.Count > 0 Then
            cell.Offset(0, 1).Value = cell.Hyperlinks(1).Address
        Else
            cell.Offset(0, 1).Value = ""
        End If
    Next cell
End Sub
TDX — HCM Tickets for Reports — URL Column
  • Insert a new column in Column B (do not overwrite any existing columns).
  • The hyperlink field must be in Column A so the macro can extract the URL into the new Column B.
  • Run the TDX macro below (copy/paste as-is).
TDX Macro (Copy/Paste)
Sub ExtractURLs_TDX_HCMTickets()
    Dim cell As Range

    ' TDX HCM Tickets for Reports: URLs are extracted from Column A and written to the inserted Column B
    For Each cell In Range("A2:A1220")
        If cell.Hyperlinks.Count > 0 Then
            cell.Offset(0, 1).Value = cell.Hyperlinks(1).Address
        Else
            cell.Offset(0, 1).Value = ""
        End If
    Next cell
End Sub
Note: These macros assume you inserted the URL column first (Jira: insert Column D; TDX: insert Column B). Inserting the columns ensures no existing fields are overwritten and the URL is written to the correct adjacent column.

Refresh Schedule
The dashboard is configured for automatic dataset refresh on:
  • Monday at 11:00 AM
  • Wednesday at 11:00 AM
All updated files must be uploaded to SharePoint before the scheduled refresh time.

Manual Refresh
If updated files are not uploaded prior to the scheduled refresh time, a user with appropriate Power BI workspace access must perform a manual refresh to update the dataset.
Manual Refresh Expectations
  • Confirm the latest files are uploaded to SharePoint.
  • Run the manual refresh in the Power BI service (workspace).
  • Validate that key totals update (active tickets, completed services, and Jira submission counts).

Troubleshooting
Use the guide below to resolve common refresh and data issues.
Issue Likely Cause Resolution
Refresh failed after upload File structure changed (columns moved/overwritten) or file name altered Re-export using standard process, reinsert URL column (do not overwrite data), and confirm Jira file name pattern is unchanged
Ticket links not working URL column missing or not populated Confirm URL column added (Jira: Column D; TDX HCM Tickets: Column B) and rerun macro with correct range
Counts look outdated Files uploaded after scheduled refresh or refresh did not run Perform a manual refresh after confirming latest SharePoint uploads
New employee cannot replicate Jira export Saved Enhanced Search tables are not shareable Coordinate with the Sr. Director to build the correct Enhanced Search table view before exporting

Roles & Responsibilities
Role Responsibilities
BPM-HCM Team Member (Updater) Runs exports, adds required URL fields, uploads files to SharePoint, and confirms updates occur before refresh windows.
Sr. Director Supports Jira Enhanced Search table setup for new employees and provides oversight for dashboard standards and governance alignment.
Power BI Workspace Admin/Authorized User Performs manual refresh when required and supports troubleshooting for refresh failures or data ingestion issues.

Quick Checklist
Use this checklist to ensure each update is complete and ready for refresh.
  • Export the following TDX reports to Excel:
    • BPM HCM Ticket Management: HCM Tickets for Reports (main TDX ticket dataset)
    • BPM HCM Ticket Management: Jira Tickets, Milestones, & Instructional Materials and Report Tasks
    • BPM HCM Ticket Management: Priority Assessment
  • Export Jira data from Enhanced Ticket Search (do not rename the file)
  • Insert URL column in Jira file (new Column D), run macro
  • Insert URL column in TDX HCM Tickets for Reports file (new Column B), run macro with range A2:A1220
  • Upload all updated files to SharePoint folder: Power BI Reports for Ticket Audit
  • Upload before Monday/Wednesday 11:00 AM refresh
  • If late, coordinate a manual refresh with an authorized Power BI user
Print Article