¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030330 |
| Summary | Is a root cause narrative missing for this CA where the VAC % is tripping the unfavorable percent threshold? |
| Error message | abs((BCWSi_dollars - ACWPi_dollars - ETCi_dollars) / BCWSi_dollars) > abs(DS07.threshold_cost_VAC_pct_unfav) & DS11.narrative_overall is missing or blank where DS11.narrative_type = 120 (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 "VAC 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 Variance at Completion (VAC) percentage exceeding the unfavorable threshold.
The VAC percentage is calculated as the absolute value of the difference between the Budgeted Cost of Work Scheduled (BCWSi_dollars), the Actual Cost of Work Performed (ACWPi_dollars), and the Estimate to Complete (ETCi_dollars), divided by the BCWSi_dollars. This percentage is then compared to the unfavorable threshold value from the DS07 IPMR Header table.
The error is likely to occur when the calculated VAC percentage for a CA in the DS03 Cost table exceeds the unfavorable threshold value, and there is no corresponding root cause narrative in the DS11 Variance table for that CA. The missing narrative is identified when the 'narrative_overall' field in the DS11 Variance table is either missing or blank, and the 'narrative_type' field is equal to 120.
To resolve this error, ensure that for each CA in the DS03 Cost table with a VAC percentage exceeding the unfavorable threshold, there is a corresponding root cause narrative in the DS11 Variance table. The narrative should be entered in the 'narrative_overall' field and the 'narrative_type' field should be set to 120.
¶ Why do we check this?
This test is being performed on the 'DS03 Cost' table to check for missing root cause narratives where the Variance at Completion (VAC) percentage is exceeding the unfavorable percent threshold. The Variance at Completion is a measure of the cost performance of the project, and it is calculated by subtracting the Actual Cost of Work Performed (ACWP) and the Estimate to Complete (ETC) from the Budgeted Cost of Work Scheduled (BCWS), then dividing by the BCWS.
The test is checking if the absolute value of this calculation is greater than the unfavorable threshold set in the DS07 table. If it is, and the overall narrative in the DS11 table is missing or blank for a narrative type of 120, then a warning is issued. The data is grouped by the Work Breakdown Structure ID for the Cost Account (WBS_ID_CA).
The importance of this check is to ensure that any significant cost variances in the project have a corresponding explanation or root cause narrative. This is crucial for understanding why the project is over or under budget and for making informed decisions about how to proceed. The severity of this check is a warning, meaning that missing narratives are likely to cause problems during data analysis. It is important to address these issues to maintain the integrity and quality of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsVACPctMissingDS11RCNarrUnfav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_VAC_pct_unfav,0)) Thrshld
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), CAVAC as (
SELECT
WBS_ID_CA CAWBS,
ABS(
(SUM(BCWSi_dollars) - SUM(ACWPi_dollars) - SUM(ETCi_dollars)) /
NULLIF(SUM(BCWSi_dollars),0)
) VACPct
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_overall,'')) <> '' AND narrative_type = 120
)
GROUP BY WBS_ID_CA
)
SELECT
C.*
FROM
DS03_cost C INNER JOIN CAVAC V ON C.WBS_ID_CA = V.CAWBS
WHERE
upload_ID = @upload_ID
AND V.VACPct > (SELECT TOP 1 Thrshld FROM threshold)
)