¶ Basic Information
| Key | Value |
|---|---|
| Table | DS11 Variance |
| Severity | MAJOR |
| Unique ID | 1110480 |
| Summary | Does this project-level VAR have a root cause narrative? |
| Error message | narrative_type < 200 & narrative_RC_SVi, narrative_RC_SVc, narrative_RC_CVi, or narrative_RC_CVc. |
¶ 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 "Project-Level VAR With Root Cause Narrative" is performed on the DS11 Variance table. This check ensures that each project-level Variance at Completion (VAR) record has an associated root cause narrative.
The fields that are checked in this DIQ test are 'narrative_type', 'narrative_RC_SVi', 'narrative_RC_SVc', 'narrative_RC_CVi', and 'narrative_RC_CVc'. The 'narrative_type' field should have a value less than 200. For the other fields, at least one of them should contain a non-empty string, indicating that a root cause narrative is present.
If the DIQ test fails, it is likely because the 'narrative_type' field has a value of 200 or more, or all of the narrative fields ('narrative_RC_SVi', 'narrative_RC_SVc', 'narrative_RC_CVi', 'narrative_RC_CVc') are empty. This would mean that a project-level VAR record is missing a root cause narrative, which is required for proper project management and tracking.
To resolve this issue, ensure that each project-level VAR record has a 'narrative_type' value less than 200 and at least one of the narrative fields contains a root cause narrative.
¶ Why do we check this?
This test is being performed on the 'DS11 Variance' table to check if the project-level Variance at Completion (VAR) has a root cause narrative. The root cause narrative is a detailed explanation of the reasons behind the variance. This is important because it provides context and understanding of why the variance occurred, which can be crucial for project management and future decision-making.
The severity level of this test is 'MAJOR', which means that if this issue is not addressed, it is likely to cause problems during data analysis. The absence of a root cause narrative can lead to misinterpretation of the data, incorrect conclusions, and potentially flawed decision-making. Therefore, it is important to ensure that each project-level VAR has a root cause narrative to maintain the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS11_VAR_DoesProjectLevelVARHaveRCNarr] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM
DS11_variance
WHERE
upload_ID = @upload_ID
AND CAST(ISNULL(narrative_type,'1000') as int) < 200
AND (
TRIM(ISNULL(narrative_RC_SVi,'')) <> ''
OR TRIM(ISNULL(narrative_RC_CVi,'')) <> ''
OR TRIM(ISNULL(narrative_RC_SVc,'')) <> ''
OR TRIM(ISNULL(narrative_RC_CVc,'')) <> ''
)
)