¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030315 |
| Summary | Is a root cause narrative missing for this CA where the CV percent is tripping the unfavorable percent threshold? |
| Error message | DS03.CVc % abs((BCWP - ACWP) / BCWP) > abs(DS07.threshold_cost_cum_dollar_unfav) & DS11.narrative_RC_CVc is missing or blank (by DS03.WBS_ID_CA & DS11.WBS_ID). |
¶ What causes this error?
The following text was generated by an AI tool and hasn't been reviewed for accuracy by a human! It might be useful, but it also might have errors. Are you a human? You can help by reviewing it for accuracy! Edit it as needed then remove this message.
The Data Integrity and Quality (DIQ) check titled "CV Percent without Root Cause Narrative (Unfavorable)" is designed to identify any instances in the DS03 Cost table where a root cause narrative is missing for a cost account (CA) that has a Cost Variance (CV) percentage exceeding the unfavorable threshold.
The CV percentage is calculated as the absolute value of the difference between the Budgeted Cost of Work Performed (BCWP) and the Actual Cost of Work Performed (ACWP), divided by the BCWP. This is represented in the test as (|(BCWP - ACWP) / BCWP|).
The test checks if this calculated CV percentage is greater than the unfavorable cost cumulative dollar threshold, represented as |DS07.threshold_cost_cum_dollar_unfav|. If it is, the test then checks if a root cause narrative for this CA is missing or blank in the DS11 Variance table. This is represented as DS11.narrative_RC_CVc is missing or blank.
The error is likely to be caused by missing or blank entries in the DS11 Variance table for the root cause narrative (narrative_RC_CVc) for a CA where the CV percentage exceeds the unfavorable threshold.
To resolve this issue, ensure that a root cause narrative is provided in the DS11 Variance table for each CA where the CV percentage exceeds the unfavorable threshold.
¶ Why do we check this?
This test is being performed to ensure that there is a root cause narrative provided for any cost variance (CV) that exceeds the unfavorable percent threshold. The test checks if the absolute value of the cost variance percent (|(BCWP - ACWP) / BCWP|) is greater than the unfavorable cost cumulative dollar threshold (|DS07.threshold_cost_cum_dollar_unfav|) and if the root cause narrative for the cost variance (DS11.narrative_RC_CVc) is missing or blank. This check is grouped by the Work Breakdown Structure ID for the Control Account (WBS_ID_CA).
The importance of this check is to ensure that any significant cost variances are properly documented with a root cause narrative. This is crucial for understanding why the variance occurred and for making informed decisions on how to address it. The severity level is set to MAJOR, which means that missing root cause narratives for significant cost variances are likely to cause problems during the analysis of the project data. It is important to address this issue to ensure the integrity and quality of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsCVPctMissingDS11RCNarrUnfav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_cum_pct_unfav,0)) thrshld
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), CACV as (
SELECT
WBS_ID_CA CAWBS,
ABS((SUM(BCWPi_dollars) - SUM(ACWPi_dollars)) / NULLIF(SUM(BCWPi_dollars),0)) CV
FROM DS03_cost C
WHERE upload_ID = @upload_ID
AND WBS_ID_CA NOT IN (
SELECT WBS_ID
FROM DS11_variance
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(narrative_RC_CVc,'')) <> ''
)
GROUP BY WBS_ID_CA
)
SELECT
C.*
FROM
DS03_cost C INNER JOIN CACV CV ON C.WBS_ID_CA = CV.CAWBS
WHERE
upload_ID = @upload_ID
AND CV.CV > (SELECT TOP 1 thrshld FROM threshold)
)