CLM 6.0.4 (iFix004)
JRS_6.0.4-I20170926-0438
I am attaching snapshots of Report Builder report setup but they appear blank so I am not sure if they will be seen. I will describe my situation as well.
I created a report builder report with the following setup;
CHOOSE DATA:
Choose a report Type: Current Data
Limit the scope: Select (1) RDNG project that has 292 Business requirements and 73 of those Business requirements are linked to Functional requirements.
Choose an artifact: Select "Requirements Management >> Requirement >> Business Requirement" checkbox
Trace relationship and add artifacts: Note: Requirement is already added by default.
Select "enable multiple paths or add other source artifacts" checkbox
Click "ADD AN ARTIFACT" button
Select "Requirements Management >> Requirement >> Business Requirement" checkbox
Click "OK" button
Click "Add a relationship" button
Select "Link To" list item
Click "OK" button
Result: You should not see "Requirement" append to "Requirement1" and "Requirement1" Link To "Requirement2".
Set conditions:
(Requirement) Type is Business Requirement
(Requirement 1) Type is Business Requirement
(Requirement 2) Type is Functional Requirement
Format Results:
Remove all Column Labels and add two Calculated values.
1.) Count total number of artifacts for "Requirement"
2.) Count total number of artifacts for "Requirement1"
The results are 292 total Business Requirements for the (Count total number of artifacts for "Requirement")
and
The results are 73 linked Business Requirements to Functional Requirements for the (Count total number of artifacts for "Requirement1")
These results are correct but what I need is the percentage of Business Requirements linked to Functional Requirements which would be a calculation of (73/292) which would be 0.25 or 25%
I have tried doing this with a single Trace but I cannot get the Total Business requirements as well as the Number of Business Requirements linked to Functional Requirements from a single Trace (query). I believe the only way to get the percentage is through the advanced feature. I have done calculations in the advanced section with a single Trace (query) but never with multiple Traces (queries).
Can someone please help me with how I would re-code the SQL Query so that It will display
The Total Business Requirements, The Number of Business Requirements Linked to Functional Requirements, and what the percentage is of the Linked Business Requirements (as compared to the total overall Business Requirements)
It would be nice to see a single Row with three columns but if that cannot be done then Three rows with the single value in each is Greatly Acceptable...THANK YOU
------------------------------------------------------------------------------------------------------
- Total Number of BRs | # of BRs Linked to FRs | % of BRs Link To FRs -
------------------------------------------------------------------------------------------------------
- 292 | 73 | 25% -
------------------------------------------------------------------------------------------------------
SQL code:
SELECT U1.REFERENCE_ID, U1.REFERENCE_ID1
FROM (
SELECT DISTINCT COUNT( DISTINCT T1.REFERENCE_ID) AS REFERENCE_ID,
-9999999.0 AS REFERENCE_ID1
FROM RIDW.VW_REQUIREMENT T1
WHERE T1.PROJECT_ID = 61 AND
( T1.REQUIREMENT_TYPE = 'Business Requirement'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUIREMENT_ID <> -1 AND T1.REQUIREMENT_ID IS NOT NULL)
UNION ALL
SELECT DISTINCT -9999999.0 AS REFERENCE_ID,
COUNT( DISTINCT T2.REFERENCE_ID) AS REFERENCE_ID1
FROM RIDW.VW_REQUIREMENT T2
INNER JOIN RIDW.VW_REQUIREMENT_HIERARCHY LT1
ON (T2.REQUIREMENT_ID = LT1.PRED_REQUIREMENT_ID) AND LT1.LINK_TYPE = 'Link'
INNER JOIN RIDW.VW_REQUIREMENT T3
ON (LT1.SUCC_REQUIREMENT_ID = T3.REQUIREMENT_ID)
WHERE T2.PROJECT_ID = 61 AND
T3.PROJECT_ID = 61 AND
( T2.REQUIREMENT_TYPE = 'Business Requirement' AND
T3.REQUIREMENT_TYPE = 'Functional Requirement'
) AND
(T2.ISSOFTDELETED = 0 AND T3.ISSOFTDELETED = 0) AND
(T2.REQUIREMENT_ID <> -1 AND T2.REQUIREMENT_ID IS NOT NULL) AND
(T3.REQUIREMENT_ID <> -1 AND T3.REQUIREMENT_ID IS NOT NULL)
) U1
