Restricting scope of the "Feature Progress Measure" (SAFe advanced Report)
Hello,
I'm using the SAFe 6.0.3 process templates in the context of a program with several development teams. All teams manage the program backlog in the same RTC project area. I wanted to use the SAFe advanced report entitled "Feature Progress Measure" to follow-up up per team the progress of the features they own. Unfortunately, the report template does not permit to restrict the list of features based on the team (better the 'Filled Against' field) so I see all features of all teams.
Can someone explain me how I should tailor the query of the report (in JSR) to allow me to see only the feature for which the 'Filled Against" field has a certain value. Here is the logic of the report (as seen in JSR):
<<
SELECT DISTINCT T1.PROJECT_NAME,
T1.NAME AS URL1_title ,
T1.URL AS URL1,
SUM(CASE WHEN (T2.REQUEST_STATUS = 'Closed' AND T2.STORY_POINTS IS NOT NULL) THEN T2.STORY_POINTS ELSE 0 END) AS DONE,
SUM(CASE WHEN (T2.REQUEST_STATUS = 'Open' AND T2.STORY_POINTS IS NOT NULL) THEN T2.STORY_POINTS ELSE 0 END) AS NOTSTARTED,
SUM(CASE WHEN (T2.REQUEST_STATUS = 'InProgress' AND T2.STORY_POINTS IS NOT NULL) THEN T2.STORY_POINTS ELSE 0 END) AS INPROGRESS,
T3.VAL AS EPICTOTALEST
FROM RIDW.VW_REQUEST T1
INNER JOIN RIDW.VW_REQUEST_RELATIONAL_LINK LT1
ON (T1.REQUEST_ID = LT1.REQUEST2_ID) AND LT1.ISSOFTDELETED = 0 AND LT1.NAME = 'com.ibm.team.workitem.linktype.parentworkitem'
INNER JOIN RIDW.VW_REQUEST T2
ON (LT1.REQUEST1_ID = T2.REQUEST_ID)
LEFT OUTER JOIN RICALM.VW_RQST_INT_EXT T3
ON T1.REQUEST_ID=T3.REQUEST_ID AND T3.NAME='com.ibm.team.workitem.attribute.estimatedStoryPoints'
WHERE (T1.REQUEST_TYPE = 'Feature' AND T2.REQUEST_TYPE = 'Story') AND
(T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) AND
(T2.REQUEST_ID <> -1 AND T2.REQUEST_ID IS NOT NULL)
GROUP BY T1.PROJECT_NAME,
T1.NAME,
T1.URL,
T3.VAL
>>
Thx
Regards,
Olivier.
|
Accepted answer
Hi Olivier,
Olivier Béghain selected this answer as the correct answer
Comments
Olivier Béghain
commented Aug 01 '17, 4:32 p.m.
Hi Amy! Thanks a lot it helped. In the SQL of my report, I had to mention T1.REQUEST_CATEGORY_NAME (not T2). I also did not need the team name since I'm limiting the scope of the features based on the "Filed Against" field.
In addition, I wanted to restrict the scope for those of a given PI (and its "child" iterations). As I forgot how to work with inner/outer joins :-), in the WHERE clause I had to specify the id of the iterations something like this...
|
2 other answers
Hello Amy,
I'm using the SAFe 6.0.3 process templates, Can you please help, the feature progress report in RTC (code below) -
This code is not showing the features if there is no story linked to it. We want to show features irrespective if it has a story linked or not. Thanks in advance,.
SELECT DISTINCT T1.PROJECT_NAME,
T1.NAME AS URL1_title ,
T1.URL AS URL1,
SUM(CASE WHEN (T2.REQUEST_STATUS = 'Closed' AND T2.STORY_POINTS IS NOT NULL) THEN T2.STORY_POINTS ELSE 0 END) AS DONE,
SUM(CASE WHEN (T2.REQUEST_STATUS = 'Open' AND T2.STORY_POINTS IS NOT NULL) THEN T2.STORY_POINTS ELSE 0 END) AS NOTSTARTED,
SUM(CASE WHEN (T2.REQUEST_STATUS = 'InProgress' AND T2.STORY_POINTS IS NOT NULL) THEN T2.STORY_POINTS ELSE 0 END) AS INPROGRESS,
T3.VAL AS EPICTOTALEST
FROM RIDW.VW_REQUEST T1
INNER JOIN RIDW.VW_REQUEST_RELATIONAL_LINK LT1
ON (T1.REQUEST_ID = LT1.REQUEST2_ID) AND LT1.ISSOFTDELETED = 0 AND LT1.NAME = 'com.ibm.team.workitem.linktype.parentworkitem'
INNER JOIN RIDW.VW_REQUEST T2
ON (LT1.REQUEST1_ID = T2.REQUEST_ID)
LEFT OUTER JOIN RICALM.VW_RQST_INT_EXT T3
ON T1.REQUEST_ID=T3.REQUEST_ID AND T3.NAME='com.ibm.team.workitem.attribute.estimatedStoryPoints'
WHERE (T1.REQUEST_TYPE = 'Feature' AND T2.REQUEST_TYPE = 'Story') AND
(T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) AND
(T2.REQUEST_ID <> -1 AND T2.REQUEST_ID IS NOT NULL)
GROUP BY T1.PROJECT_NAME,
T1.NAME,
T1.URL,
T3.VAL
|
Hi,
|
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.