ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Risk Calculation under Risk Factors is incorrect in SQL Server


Article ID: 17458


Updated On:


Clarity PPM SaaS Clarity PPM On Premise



The categories under Risk Factors in Risk Rating sub page is not calculated correctly in MS SQL Server but works fine in Oracle.

INV_PROJECTS table has the scores for the risk showing up in the risk factor page under RCF_IMPLEMENTATION column. By following the Steps to Reproduce below, rcf_implementation in Oracle shows score 50 but in SQL Server it is 0 and hence the discrepancy in the UI.

Steps to Reproduce:

  1. Login to Clarity which runs on SQL SERVER database.
  2. Open a project and navigate to the RISKS/ISSUES/CHANGES tab.
  3. Create 3 RISKS as below:
    Name: Sql Server Risk A - ID: riska, Category: Implementation, Priority: Low, Probability: Low, Impact: Low (Calculated risk will be LOW)
    Name: Sql Server Risk B - ID: riskb, Category: Implementation, Priority: Low, Probability: Low, Impact: Low (Calculated risk will be LOW)
    Name: Sql Server Risk C - ID: riskc, Category: Implementation, Priority: Low, Probability: Medium, Impact: Medium (Calculated risk will be MEDIUM)

    <Please see attached file for image>

    Figure 1
  4. Perform exactly the same steps above by logging into a Clarity instance that runs on Oracle database.

    <Please see attached file for image>

    Figure 2
  5. Navigate to Project Properties, Risk Rating Subpage.
  6. Check the RISK for Implementation category under RISK factors for both environments.

Expected Result: The Implementation under RISK factors shows MEDIUM. From the backend, INV_PROJECTS.RCF_IMPLEMENTATION should be 50.

Actual Result: The environment running on SQL Server shows Implementation Risk factor as LOW. From the backend, INV_PROJECTS.RCF_IMPLEMENTATION is 0.

<Please see attached file for image>

Figure 3


Caused by CLRT-75093

The problem here is due to the query ITL.GETLOOKUPENUMEXACTVALUE which is using a string datatype for the lookup_enum parameter (this is the average value for impact/probability obtained earlier). MSSQL is rounding the value somehow so that when it is 1.333333 it is treated as 1 while 1.666666 is treated as 2.


Resolved in CA PPM 14.2




Release: ESPCLA99000-13.3-Clarity-Extended Support Plus


1558709540525000017458_sktwi1f5rjvs16ri6.gif get_app
1558709538381000017458_sktwi1f5rjvs16ri5.gif get_app
1558709536335000017458_sktwi1f5rjvs16ri4.gif get_app