It's all about the answers!

Ask a question

How Do I Get Percentage Of Business Requirements Linked to Functional Requirements


Richard Kissel (30113) | asked Nov 16 '17, 7:39 p.m.

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

  

 


 

One answer



permanent link
Richard Kissel (30113) | answered Nov 20 '17, 4:58 p.m.

Perseverance Wins Out…

I was able to do this with one Query

 

SELECT DISTINCT

      COUNT(DISTINCT T1.REFERENCE_ID) AS REFERENCE_ID,

      COUNT(DISTINCT CASE WHEN T2.REQUIREMENT_TYPE = 'Functional Requirement' AND LT1.LINK_TYPE = 'Link' THEN T1.REQUIREMENT_ID END ) AS REFERENCE_ID1,

      DOUBLE(COUNT( DISTINCT CASE WHEN T2.REQUIREMENT_TYPE = 'Functional Requirement' AND LT1.LINK_TYPE = 'Link' THEN T1.REFERENCE_ID END)) / DOUBLE(COUNT( DISTINCT T1.REFERENCE_ID)) AS REFERENCE_ID2

FROM RIDW.VW_REQUIREMENT T1

LEFT OUTER JOIN RIDW.VW_REQUIREMENT_HIERARCHY LT1

ON (T1.REQUIREMENT_ID = LT1.PRED_REQUIREMENT_ID) AND LT1.LINK_TYPE = 'Link'

  LEFT OUTER JOIN RIDW.VW_REQUIREMENT T2

  ON (LT1.SUCC_REQUIREMENT_ID = T2.REQUIREMENT_ID)

WHERE T1.PROJECT_ID = 61 AND

  (T2.PROJECT_ID = 61 OR (T2.PROJECT_ID=-1 OR T2.PROJECT_ID IS NULL))  AND

(  T1.REQUIREMENT_TYPE = 'Business Requirement' AND

  (T2.REQUIREMENT_TYPE = 'Functional Requirement'  OR (T2.REQUIREMENT_TYPE='Info not available' OR T2.REQUIREMENT_TYPE='Unassigned' OR T2.REQUIREMENT_TYPE=' ' OR T2.REQUIREMENT_TYPE IS NULL))

) AND

(T1.ISSOFTDELETED = 0) AND

(T1.REQUIREMENT_ID <> -1 AND T1.REQUIREMENT_ID IS NOT NULL)


Comments
Richard Kissel commented Nov 20 '17, 5:01 p.m.

I image in the future I will need to be able to Calculate amongst Multiple Queries so If anyone can answer (show) how to do that with the original question that would be Appreciated.

Your answer


Register or to post your answer.