¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030327 |
| Summary | Is a root cause narrative missing for this CA where the incremental SV percent is tripping the favorable percent threshold? |
| Error message | DS03.SVi abs((BCWPi - BCWSi) / BCWSi) > abs(DS07.threshold_schedule_inc_pct_fav) & 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 (Favorable)" is designed to ensure that a root cause narrative is provided whenever the incremental Schedule Variance (SV) percent exceeds the favorable percent threshold. This check is performed on the DS03 Cost table.
The error message "DS03.SVi (|(BCWPi - BCWSi) / BCWSi|) > |DS07.threshold_schedule_inc_pct_fav| & DS11.narrative_RC_SVi is missing or blank (by DS03.WBS_ID_CA & DS11.WBS_ID)" indicates that the check has found instances where the incremental SV percent is greater than the favorable threshold, but the root cause narrative is missing or blank. This is determined by comparing the calculated SV percent with the threshold value from the DS07 IPMR Header table and checking for the presence of a root cause narrative in the DS11 Variance table.
The fields causing this issue are the BCWPi (Budgeted Cost of Work Performed - Incremental) and BCWSi (Budgeted Cost of Work Scheduled - Incremental) fields in the DS03 Cost table, the threshold_schedule_inc_pct_fav field in the DS07 IPMR Header table, and the narrative_RC_SVi field in the DS11 Variance table.
The expected values are that if the absolute value of the calculated SV percent (|(BCWPi - BCWSi) / BCWSi|) is greater than the absolute value of the favorable threshold (|DS07.threshold_schedule_inc_pct_fav|), then a root cause narrative (DS11.narrative_RC_SVi) should be provided. If the narrative is missing or blank, this will trigger the error.
To resolve this issue, ensure that a root cause narrative is provided in the DS11 Variance table whenever the incremental SV percent exceeds the favorable threshold in the DS03 Cost table.
¶ Why do we check this?
This test is being performed to ensure that there is a root cause narrative provided whenever the incremental Schedule Variance (SV) percent exceeds the favorable percent threshold. The Schedule Variance is calculated as the difference between the Budgeted Cost of Work Performed (BCWP) and the Budgeted Cost of Work Scheduled (BCWS), divided by the BCWS. If this value is greater than the threshold set for favorable schedule variance, a root cause narrative should be provided to explain the variance.
The importance of this check is to ensure that significant variances in the project schedule are properly documented and explained. This is crucial for understanding the reasons behind the favorable variance and for making informed decisions about project management. The severity of this check is marked as a MAJOR, which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis if not addressed. This is because the lack of a root cause narrative for significant variances can lead to misunderstandings or misinterpretations of the project data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsSViPctMissingDS11RCNarrFav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_schedule_inc_pct_fav,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
)