Custom Table Input for Work Items with Dynamic Calculations

This extension enables the creation of dynamic HTML tables within Azure DevOps work items, allowing for sophisticated calculations and seamless data integration. It empowers users to quickly reference and manipulate work item fields within the table, providing a "poor man's worksheet" for real-time data analysis.

Last Updates

March, 2025 (v.1.17)

Bug Fixes

• Fixed HTML Encoding Issues in Table Cells: Resolved an issue where special characters, such as quotation marks, were incorrectly displayed as HTML entities (e.g., "Test3"). Table cells now correctly render special characters as intended.
• Fixed Column Count Issues with Empty Cells: Addressed an issue where empty cells were causing incorrect column count calculations, leading to table layout problems. Empty cells are now properly handled, ensuring accurate column counts.

How to Use

Once the extension is installed, you can create dynamic HTML tables for work items with simple yet powerful calculations.

1. Define Columns: List the column names for your table as a semi-colon separated string.
2. Field References: Use work item fields within table formulas to perform real-time calculations (e.g., [$FieldName]).
3. CSV Output: Define a multi-line text field in your work item where the table content will be saved in CSV format.
4. Advanced Configuration: Optionally, specify the initial row count, columns for summing values in the bottom row, and custom functions for each column.

Supported arithmetic functions:

• Addition (+): Adds two or more values.
• Subtraction (-): Subtracts one value from another.
• Multiplication (*): Multiplies two or more values.
• Division (/): Divides one value by another.
• Modulus (%): Returns the remainder of division between two values.
• Exponentiation (^ or ): Raises one value to the power of another.

Comparison Operators: Less Than (<): Checks if one value is less than another. Greater Than (>): Checks if one value is greater than another. Not Equal To (!=): Checks if two values are not equal. Equal To (==): Checks if two values are equal.

Supported Mathematical Functions: min():

• Description: Returns the smallest number in a set of values.
• Example: min(5, 3, 9) returns 3.

max():

• Description: Returns the largest number in a set of values.
• Example: max(5, 3, 9) returns 9.

sum():

• Description: Calculates the total sum of all provided values.
• Example: sum(5, 3, 9) returns 17.

avg():

• Description: Computes the average (mean) of the given values.
• Example: avg(5, 3, 9) returns 5.6667.

abs():

• Description: Returns the absolute value of a number.
• Example: abs(-5) returns 5.

round():

• Description: Rounds a number to the nearest integer.
• Example: round(4.7) returns 5.

ceil():

• Description: Rounds a number up to the nearest integer.
• Example: ceil(4.3) returns 5.

floor():

• Description: Rounds a number down to the nearest integer.
• Example: floor(4.7) returns 4.

sqrt():

• Description: Returns the square root of a number.
• Example: sqrt(9) returns 3.

sumproduct():

• Description: Multiplies corresponding elements in two or more arrays and returns the sum of the results.
• Example: sumproduct({[Column1],[Column2],[Column3]}, {1,2,3}) returns the sum of the products of corresponding values from [Column1], [Column2], and [Column3] with {1,2,3}.
• Note: Arrays used in the sumproduct function should be enclosed in {} for the calculation to work correctly, allowing the use of table and work item field references inside the calculation.

When to Use

This extension is particularly valuable when you need to incorporate real-time calculations and data analysis directly within a work item, without relying on external attachments or manual calculations. Here are some practical examples:

1. Centralized Data Analysis within Work Items

• Scenario: You are managing a project with multiple work items, and each work item requires frequent calculations based on other fields (e.g., time tracking, cost estimation, resource allocation).
• Benefit: Instead of tracking these calculations in separate documents or spreadsheets attached to work items, you can perform the calculations directly within the work item itself, keeping everything in one place for easy tracking, reporting, and updates. It ensures that your team always works with the most up-to-date data without needing to open multiple files.

2. Simplifying Complex Estimations

• Scenario: You need to estimate cost savings or efficiency gains based on various parameters such as hours spent, hourly rates, and output produced.
• Benefit: With the ability to calculate values dynamically based on other fields within the work item, you can create more accurate and flexible estimations without relying on external spreadsheets. This reduces the risk of errors and ensures that estimations reflect any changes made to the work item fields in real time.

3. Automated Summing of Data

• Scenario: You manage a project where you track the progress of various tasks in terms of time or cost, and you need the sum of different values (e.g., total hours worked, total cost) calculated automatically at the bottom of each table.
• Benefit: You can define specific columns to sum automatically, reducing manual effort and errors. This makes it easy to track project progress and adjust plans quickly without worrying about maintaining separate tracking documents.

4. Quick Analysis and Decision-Making

• Scenario: You need to quickly analyze the impact of different variables (e.g., budget allocation, timeline shifts) on your project’s outcome.
• Benefit: With dynamic formulas and field references, you can change values directly in the work item and see the updated results instantly. This allows for faster decision-making, as there’s no need to open multiple documents or recalculate manually.

5. Cost Tracking and Financial Reporting

• Scenario: You need to track project costs across different activities and calculate total costs dynamically based on hourly rates and hours worked.
• Benefit: Using this extension, you can create tables that automatically calculate costs, total hours, or savings, and export the results to CSV format for easy reporting. This reduces the need for external financial tools and allows for more agile financial tracking within Azure DevOps.

Key Features

• Dynamic Tables: Create and manage dynamic HTML tables directly within work items, supporting calculations across rows and columns.
• Field References: Reference work item fields within table calculations using an intuitive field reference format (e.g., [$FieldName]).
• Real-time Calculations: Perform basic arithmetic operations such as addition, subtraction, multiplication, and division within table columns.
• CSV Output: Easily export table content to a multi-line CSV format for further analysis or reporting.
• Temporary Function Testing: Test and apply temporary changes to column functions directly in the table interface. These changes are reset when the page or work item is reloaded, providing a quick way to experiment before committing to permanent settings.
• Smart Field Handling: Automatically convert non-numeric field values to numbers when possible, or retain their original text format without quotation marks when they are purely textual.
• Tooltip Insights: Tooltips for calculated fields display both raw functions and calculated values, offering users better context and transparency about table operations.
• CSV Output: Easily extract table content to a multi-line CSV format for further analysis or reporting.
• CSV Import: Users can easily import work item data from CSV files, streamlining the process of adding multiple records at once.

Example Inputs

Column Names:
Short description;As-is (h/year);To-be (h/year);Gain/Saving (h/year);EUR/h;Annual_saving_in_KEUR

Column Functions:
;;;[As-is (h/year)]-[To-be (h/year)];;[Gain/Saving (h/year)]*[EUR/h]/1000

Row Count:
2

Columns With Sum:
Gain/Saving (h/year);Annual_saving_in_KEUR

CSV Output:
outputCSV

Decimal Separator:
,

Test changes for column functions:

Importing external CSV data:

Important Notes

• When using work item field references (e.g., [$FieldName]), please note that changes to the field values will only be reflected in the table calculations after refreshing the browser. This ensures that calculations are based on the most up-to-date field values.
• Please note that the Temporary Function Testing does not persist inputs between edits if the changes are not saved. It is under consideration to enable temporary edits to the functions to persist without saving the work item.

Feedback and Issues

If you have any questions, feel free to leave them in the Q&A section. For feedback or suggestions on new functionalities, please provide them via the Ratings & Review section.

You can also always reach us via email. We appreciate your input and strive to improve the extension based on your needs!