Risk Calculation under Risk Factors is incorrect in SQL Server

Article Id: 17458
Status: Published
Updated On: 14-02-2018 07:05
Legacy Id: TEC617388
Products:
CLARITY PPM FOR ITG , CLARITY PPM FEDERAL , CA Project & Portfolio Management SAAS , STARTER PACK-CLARITY PPM
Issue/Introduction:

Problem:

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

Cause:

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.

Resolution:

Resolved in CA PPM 14.2

Workaround:

None.

Environment:

Release: ESPCLA99000-13.3-Clarity-Extended Support Plus
Component:

insert_drive_file 1558709540525000017458_sktwi1f5rjvs16ri6.gif
arrow_downward Download
insert_drive_file 1558709538381000017458_sktwi1f5rjvs16ri5.gif
arrow_downward Download
insert_drive_file 1558709536335000017458_sktwi1f5rjvs16ri4.gif
arrow_downward Download