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
January, 2025 (v.1.10)
Improvements:
- Enhanced User Interface: Input fields have been changed to textareas for a better user experience when creating tables. This allows for easier entry of longer, multi-line text values, especially useful in tables that do not require calculations.
- Font Size Adjustment: The font sizes in tables have been reduced for a better user experience when dealing with larger tables, ensuring better readability and reducing unnecessary scrolling.
New Features:
New Mathematical Function - sumproduct: A new mathematical function sumproduct has been added, allowing users to multiply corresponding values in two or more arrays and then sum those products. This is useful for more complex data analysis within tables.
Table Cell Resizing: Added support for both horizontal and vertical resizing of table cells, allowing users to adjust the size of columns and rows for better data visibility and interaction.
How to Use
Once the extension is installed, you can create dynamic HTML tables for work items with simple yet powerful calculations.
- Define Columns: List the column names for your table as a semi-colon separated string.
- Field References: Use work item fields within table formulas to perform real-time calculations (e.g.,
[$FieldName]).
- CSV Output: Define a multi-line text field in your work item where the table content will be saved in CSV format.
- 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 export table content to a multi-line CSV format for further analysis or reporting.
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
Test changes for column functions:
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.
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!
Change Log
February, 2025 (v.1.11)
Improvements:
- Text Area Resizing: The height of textareas now automatically adjusts based on the resized table cells, ensuring a more responsive and cohesive layout.
January, 2025 (v.1.10)
Improvements:
- Enhanced User Interface: Input fields have been changed to textareas for a better user experience when creating tables. This allows for easier entry of longer, multi-line text values, especially useful in tables that do not require calculations.
- Font Size Adjustment: The font sizes in tables have been reduced for a better user experience when dealing with larger tables, ensuring better readability and reducing unnecessary scrolling.
New Features:
New Mathematical Function - sumproduct: A new mathematical function sumproduct has been added, allowing users to multiply corresponding values in two or more arrays and then sum those products. This is useful for more complex data analysis within tables.
Table Cell Resizing: Added support for both horizontal and vertical resizing of table cells, allowing users to adjust the size of columns and rows for better data visibility and interaction.
January, 2025 (v.1.9)
Improvements:
- Updated the calculation logic to ensure non-numeric values default to
0 in mathematical operations, preventing errors and improving consistency.
- Improved formula evaluation to handle dependencies between calculated fields more effectively.
Bug Fixes
- Fixed issues where mathematical functions did not process correctly under certain conditions.
- Resolved an issue where referenced columns containing calculations resulted in incorrect values during evaluation.
January, 2025 (v.1.8)
Bug Fixes
- Enhanced Column Function Parsing: Fixed an issue where column functions containing work item field references ([$FieldName]) were not correctly parsed in certain cases. The parser now ensures accurate recognition and calculation of referenced fields, improving reliability.
- Consistent Function Handling: Adjusted column function evaluation to handle empty or missing values more gracefully, preventing calculation errors and ensuring smooth table updates.
January, 2025 (v.1.7)
Improvements:
- Default Column Functions Handling: Improved the handling of column functions by automatically initializing them to a list of empty strings when not provided in the input. The number of empty strings now matches the number of defined column names, preventing incorrect table structure creation.
January, 2025 (v.1.6)
Bug Fixes:
- Table Calculation Errors: Fixed an issue where tables without any work item field references were causing unexpected errors.
January, 2025 (v.1.5)
New Functionalities:
Enhanced Math Function Support:
*The extension now supports the following mathematical functions:
- min(): Returns the smallest number in a set of values.
- max(): Returns the largest number in a set of values.
- sum(): Calculates the total sum of all provided values.
- avg(): Computes the average of the given values.
- abs(): Returns the absolute value of a number.
- round(): Rounds a number to the nearest integer.
- ceil(): Rounds a number up to the nearest integer.
- floor(): Rounds a number down to the nearest integer.
- modulus (%): Returns the remainder of division between two values.
- exponentiation (^ or **): Raises one value to the power of another.
The extension now supports the following 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.
January, 2025 (v.1.4)
New Functionalities:
- Auto-fit Table Height: Added support for automatically adjusting the table height based on the number of rows. This ensures a better user experience by eliminating unnecessary whitespace, optimizing the use of space within the work item interface, and displaying all rows at once without requiring scrolling.
Bug Fixes:
- Temporary Function Testing: Fixed an issue where temporary function testing failed to work for empty tables when no data was saved to the output CSV field. The functionality now handles empty tables gracefully, allowing users to define and test column functions even without pre-existing data.
January, 2025 (v.1.3)
New Functionalities:
- Temporary Function Testing: Introduced the ability to test and apply temporary changes to column functions without permanently saving them. These changes will reset upon reloading the page or work item, allowing users to experiment and refine functions more easily before committing to changes in the custom control settings.
Improvements:
- Enhanced Field Referencing: Non-numeric fields can now be referenced in table columns without being treated as zero. If the content can be converted to a numeric value, it will be converted; otherwise, the original text value will be preserved for display.
January, 2025 (v.1.2)
New Functionalities:
- Tooltip enhancements: Added raw functions and calculated values to the tooltip text of the calculated fields, providing better context and real-time information for users interacting with calculated values.
Improvements:
- Enhanced CSV input handling: Added improvements to handle cases where the CSV string is manually modified within the multiline text field, ensuring smoother user experience and error handling.
January, 2025 (v.1.1)
New Functionalities:
- Added support for work item field references within table formulas, enabling dynamic updates based on field values from the work item itself.
| |
