¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030318 |
| Summary | Is a root cause narrative missing for this CA where the SV percent is tripping the favorable percent threshold? |
| Error message | DS03.SVc % abs((BCWP - BCWS) / BCWS) > abs(DS07.threshold_schedule_cum_pct_fav) & DS11.narrative_RC_SVc 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 "SV Percent without Root Cause Narrative (Favorable)" 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 Schedule Variance (SV) percentage exceeding the favorable 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 BCWS. This is compared against the favorable threshold value from the DS07 IPMR Header table.
The error is likely to occur when the calculated SV percentage for a CA in the DS03 Cost table is greater than the favorable threshold from the DS07 IPMR Header table, and the corresponding root cause narrative for the CA in the DS11 Variance table is missing or blank.
The fields causing the issue are the BCWPi_dollars and BCWSi_dollars fields in the DS03 Cost table, the threshold_schedule_cum_pct_fav field in the DS07 IPMR Header table, and the narrative_RC_SVc field in the DS11 Variance table.
The expected values for the BCWPi_dollars and BCWSi_dollars fields are numerical values representing the budgeted cost of work performed and scheduled respectively. The threshold_schedule_cum_pct_fav field should contain the favorable threshold value for the SV percentage. The narrative_RC_SVc field should contain a narrative explaining the root cause of the schedule variance.
If these fields do not contain the expected values, the DIQ check will flag an error. To resolve this, ensure that all cost accounts with a SV percentage exceeding the favorable threshold have a corresponding root cause narrative in the DS11 Variance table.
¶ Why do we check this?
This test is being performed to ensure that there is a root cause narrative provided for any cost account (CA) where the Schedule Variance (SV) percent is exceeding the favorable percent threshold. The Schedule Variance percent 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 BCWS. If this value is greater than the threshold for cumulative favorable schedule variance, and there is no root cause narrative provided, a warning is issued.
The importance of this check is to ensure that any significant favorable variances in the schedule are properly documented and explained. This is crucial for understanding the reasons behind the variance and for making informed decisions about project management. A warning severity indicates that the absence of a root cause narrative is likely to cause problems during data analysis, as it may be difficult to interpret the reasons behind the variance without this information. Therefore, it is recommended to address this issue to ensure the integrity and quality of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsSVPctMissingDS11RCNarrFav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_schedule_cum_pct_fav,0)) thrshld
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), VARs as (
SELECT WBS_ID
FROM DS11_variance
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(narrative_RC_SVc,'')) <> ''
), CASV as (
SELECT WBS_ID_CA CAWBS, ABS((SUM(BCWPi_dollars) - SUM(BCWSi_dollars)) / NULLIF(SUM(BCWSi_dollars),0)) SV
FROM DS03_cost C
WHERE upload_ID = @upload_ID
AND WBS_ID_CA NOT IN (SELECT WBS_ID FROM VARs)
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.SV > (SELECT TOP 1 thrshld FROM threshold)
AND (SELECT COUNT(*) FROM VARs) > 0 --test only if DS11 has records
)