¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030328 |
| Summary | Is a root cause narrative missing for this CA where the incremental SV percent is tripping the unfavorable percent threshold? |
| Error message | DS03.SVi abs((BCWPi - BCWSi) / BCWSi) > abs(DS07.threshold_schedule_inc_pct_unfav) & DS11.narrative_RC_SVi 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 "Incremental SV 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 Control Account (CA) that has an incremental Schedule Variance (SV) percentage exceeding the unfavorable threshold.
The SV percentage is calculated as the absolute value of the difference between the Budgeted Cost of Work Performed (BCWP) and the Budgeted Cost of Work Scheduled (BCWS), divided by the BCWS. This percentage is then compared to the unfavorable threshold value from the DS07 IPMR Header table.
The DIQ check groups the data by the Control Account Work Breakdown Structure ID (WBS_ID_CA) and the period date. If the SV percentage for a CA exceeds the unfavorable threshold and there is no corresponding root cause narrative in the DS11 Variance table, the DIQ check will flag this as an error.
The likely cause of this error is either a missing or blank narrative in the DS11 Variance table for a CA with an unfavorable SV percentage. To resolve this issue, a root cause narrative should be provided in the DS11 Variance table for each CA where the SV 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 whenever there is an unfavorable incremental Schedule Variance (SV) percent that exceeds the threshold. The Schedule Variance is calculated by subtracting the Budgeted Cost of Work Scheduled (BCWS) from the Budgeted Cost of Work Performed (BCWP). If the absolute value of this difference divided by BCWS is greater than the unfavorable threshold, a root cause narrative should be provided.
The importance of this check is to ensure that any significant deviations from the planned schedule are properly documented and explained. This is crucial for understanding the reasons behind any delays or accelerations in the project, which can inform future planning and decision-making. The severity level is set to MAJOR, which means that while the absence of a root cause narrative may not prevent the data from being reviewed, it is likely to cause problems during analysis. This is because without a proper explanation, it may be difficult to interpret the significance of the unfavorable SV percent and to take appropriate corrective actions.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsSViPctMissingDS11RCNarrUnfav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_schedule_inc_pct_unfav,0)) thrshld
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), CASV as (
SELECT
WBS_ID_CA CAWBS,
ABS((SUM(BCWPi_dollars) - SUM(BCWSi_dollars)) / NULLIF(SUM(BCWSi_dollars),0)) SViPct
FROM DS03_cost C
WHERE upload_ID = @upload_ID
AND period_date = CPP_status_date
AND WBS_ID_CA NOT IN (
SELECT WBS_ID
FROM DS11_variance
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(narrative_RC_SVi,'')) <> ''
)
GROUP BY WBS_ID_CA
)
SELECT
C.*
FROM
DS03_cost C INNER JOIN CASV SV ON C.WBS_ID_CA = SV.CAWBS
WHERE
upload_ID = @upload_ID
AND SV.SViPct > (SELECT TOP 1 thrshld FROM threshold)
AND period_date = CPP_status_date
)