Clarity: Non-Daily PRJ and RES database views (RPT_*_ALLOC_COST_V, RPT_*_ASSIGN_COST_V ) calculation of costs

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Issue:

The definition of the Allocation and Assignment 'COST' report database views utilize 'sliced' units and the Rate Matrix Extraction cost and rates data to calculate the costs of assignments and allocations for calendar periods of (D)aily, (W)eekly, (M)onthly, and (Q)uarterly.

The (D)aily report database views generate correct cost values for the total daily sliced units.

The other periods are not calculated correctly when the rate matrix configuration has different rates configured during the other period ranges.

Use Case Scenario:

Project does not start on the first day of the month, does not start on the first day of the quarter or does not start on the first day of the week. When this occurs, the database view will return an incorrect cost because the 'slice' date will not fall within the date range in the rate matrix extraction table.

The Rate Matrix Rows have multiple rates during a week, during a month and during a quarter period. When this occurs, the database view will return only one cost because the 'slice' date is only for one date (the first day of the week, month or quarter)

The steps outlined are for Team Allocation values and comparison of data for the database views were looked at for RPT_PRJ_*_ALLOC_COST_V views. The same behavior applies to the RPT_RES_*_ALLOC_COST_V views and for the PRJ and RES 'ASSIGN' cost views. The steps outlined only trace the data for a LABOR resource. The same behavior applies for the other types of resources (Equipment, Material, Expense)

Database Views Affected:

RPT_PRJ_M_ALLOC_COST_V 
RPT_RES_M_ALLOC_COST_V 
TIMESLICES USED: MONTHLYRESOURCEALLOCCURVE, MONTHLYRESOURCEHARDALLOC 
 
RPT_PRJ_Q_ALLOC_COST_V 
RPT_RES_Q_ALLOC_COST_V 
TIMESLICES USED: QUARTERLYRESOURCEALLOCCURVE, QUARTERLYRESOURCEHARDALLOC 
 
RPT_PRJ_W_ALLOC_COST_V 
RPT_RES_W_ALLOC_COST_V 
TIMESLICES USED: WEEKLYRESOURCEALLOCCURVE, WEEKLYRESOURCEHARDALLOC 
 
RPT_PRJ_M_ASSIGN_COST_V 
RPT_RES_M_ASSIGN_COST_V 
TIMESLICES USED: MONTHLYRESOURCEBASECURVE, MONTHLYBASEASSIGNCOSTS 
 
RPT_PRJ_Q_ASSIGN_COST_V 
RPT_RES_Q_ASSIGN_COST_V 
TIMESLICES USED: QUARTERLYRESOURCEBASECURVE, QUARTERLYBASEASSIGNCOSTS 
 
RPT_PRJ_W_ASSIGN_COST_V 
RPT_RES_W_ASSIGN_COST_V 
TIMESLICES USED: WEEKLYRESOURCEBASECURVE, WEEKLYBASEASSIGNCOSTS 

 

Steps to Reproduce:

1. Create a set of Rate Matrix Rows with multiple rates that change during a week or month
2. Create a Project that begins in the middle of the month, in the middle of a week
3. Set up project financial properties
4. Allocate and assign Resources to the project
5. Check Time Slice Definitions - be sure the date range covers the dates used in the project example
6. Execute 'Time Slicing' job
7. Execute 'Rate Matrix Extraction' job
8. Get the internal record ID for the project created (look at project properties URL)
9. Execute queries to see data values from database DAILY, MONTHLY, QUARTERLY and WEEKLY Allocation view for comparison and calculation verification

Expected Result: Weekly, Monthly, Quarterly calculations should equate to summation of Daily values

Actual Result: Weekly, Monthly, Quarterly calculations use 'slice' date to find the cost rate in the matrix.

Cause:

Caused by CLRT-71952 

Resolution:

This is identified as a 'Known Defect'. There are no current plans to make changes to this area of the product.

Workaround:

For On Premise Customers Only:
Define alternative database views to sum the daily calculated amounts (ie. from RPT_PRJ_D_ALLOC_COST_V) using a 'group by' clause for the reporting calendar hierarchy level such as weekly, monthly, quarterly, yearly. Refer to the Technical Reference Guide for specific details on the NBI_DIM_CALENDAR_TIME fields.

Click Here for a document that provides sample queries which have been reviewed by our development team.
If you use these queries be sure to implement them in a manner that 'requires' the use of passing a value for the PROJECT_ID and HIERARCHY_LEVEL parameters.
Details are given in the attachment showing what valid values should be passed as parameters.

Additional Information:

Reference Customer Reported Known Defects and our CA PPM Customer Defect Resolution Policy.

 

Environment

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