Troubleshooting complex calculations in Jama Connect Interchange™ (JCI) XLS functions

Author: Szabolcs Agai

Date: November 2024

Audience: JCI administrators/developers

Products Applicable: Jama Connect®, Jama Connect Interchange™

Use Case

Jama Connect Interchange XLS functions (JCI XLS) offer great flexibility in extending Connect's native functionalities.

Some of those examples are shared in our community here.

We can also develop our own XLS functions; however, we often want to see how a complex calculation progresses for troubleshooting or improvement purposes.

Best Practice

This approach allows for the development of complex calculations in a controlled and incremental manner.

This approach can be used to troubleshoot an existing calculation or to compile a new complex calculation.

A complex calculation can be divided into calculations solving smaller pieces of the problem. The interim results of these smaller calculations can be displayed in the temporary item troubleshooting/debug field(s). 

For a simple example, a calculation is to calculate X = A + B + C . Then, we can have a calculation on the JCI XLS functions's XLS(X) file that is D1 = A + B, D2 = D1 + C, and X = D1 + D2. Please note that D1 and D2 will be displayed on an item temporarily as fields for troubleshooting purposes, whereas X will remain there as a field that shows the result of the calculation.

For more complex calculations, we can go gradually with the number of smaller calculations that give us the control that we need. As calculation in total produces our expected outcome, we can remove these temporary troubleshooting fields from the item and retain only the result field. At the same time, we can leave the XLS file for the calculation intact or unchanged to ensure consistency in the expected outcome when removing the troubleshooting fields from the item.

This approach is also useful if we want to tune our calculation or if we are uncertain at the beginning about how to approach our calculation of a given problem.

Implementation

Let's discuss the implementation with an example from a real customer project.

The calculation itself is not discussed here in detail; this example is it helps us understand how the JCI XLS complex calculation troubleshooting concept could work.

STEP 1.

We want to define the JCI XLS calculation so that smaller pieces of it support our troubleshooting purpose.

It will result in a multiple-step calculation when each cell leverages the result of the previous cells' calculations.

Example:

Cell G3's "SET field value of Reduced P" is the final result of the calculation; however, cells A2/B2/C2/D2/E2/F2 provide the sequential partial results of more minor calculations towards the final G2 result.

STEP 2.

Prepare the item type to display troubleshooting/debug fields.

We can create fields on the item type where we would like to display the overall calculation result.

Example:

Please note the Debug/Troubleshooting fields here correspond to the more minor complexity calculations of the XLS cells in STEP 1.

STEP 3.

Configure JCI XLS mapping to populate the DEBUG/Troubleshooting fields of the items in the calculation.

Example:

STEP 4.

You can just run the integration, preferably on a sandbox project.

It is recommended to use sandbox project to check the expected outcome to avoid discrepancies in production project. When the calculation working as intended, this technical solution can be applied to the production project.

Example:

STEP 5.

When the calculation works as intended in the sandbox project, the calculation can be applied to the production project.

If needed, all debug/troubleshooting fields could be deleted from the item type.

Also, the JCI XLS data mapping can be updated by removing the mappings for the temporary debug/troubleshooting fields.

References

• Jama Connect Interchange™ Help
• Jama Connect Interchange™ Appendix
• JCI XLS functions example templates

Please feel free to leave feedback in the comments below.