How can I use the current Iteration instead of a fixed value on query creating a new report using Report Builder
Hello everybody,
I'm trying to build a new report on Jazz Report Builder and I need help to get the Current Iteration instead of an iteration fixed in the query.
I put the query below and underline the point when I need to use the current Iteration.
Please, someone could help me?
Thanks in advance!
SELECT T3_1.LITERAL_NAME AS LITERAL_NAME1,
SUM(((T2.ACTUAL_WORK)/60)/60) as ACTUAL_WORK
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) AND LT1.NAME = 'com.ibm.team.workitem.linktype.parentworkitem'
LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T3
ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='vsm'
LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T3_1
ON T3_1.EXTERNAL_ID=T3.VAL AND T3_1.PROJECT_ID=T1.PROJECT_ID
WHERE T1.PROJECT_ID = 2734 AND
T2.PROJECT_ID = 2734 AND
( T1.REQUEST_TYPE = 'Story' AND
T1.ITERATION_ID = 127726 AND
(T2.REQUEST_TYPE = 'Task' OR T2.REQUEST_TYPE = 'Task Maintenance' )
) 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 T3_1.LITERAL_NAME
I'm trying to build a new report on Jazz Report Builder and I need help to get the Current Iteration instead of an iteration fixed in the query.
I put the query below and underline the point when I need to use the current Iteration.
Please, someone could help me?
Thanks in advance!
SELECT T3_1.LITERAL_NAME AS LITERAL_NAME1,
SUM(((T2.ACTUAL_WORK)/60)/60) as ACTUAL_WORK
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) AND LT1.NAME = 'com.ibm.team.workitem.linktype.parentworkitem'
LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T3
ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='vsm'
LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T3_1
ON T3_1.EXTERNAL_ID=T3.VAL AND T3_1.PROJECT_ID=T1.PROJECT_ID
WHERE T1.PROJECT_ID = 2734 AND
T2.PROJECT_ID = 2734 AND
( T1.REQUEST_TYPE = 'Story' AND
T1.ITERATION_ID = 127726 AND
(T2.REQUEST_TYPE = 'Task' OR T2.REQUEST_TYPE = 'Task Maintenance' )
) 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 T3_1.LITERAL_NAME
One answer
I even cannot locate the Current iteration in the GUI instead I have to choose specific iteration, although if I specify "Unlocked + required: user must edit when running the report" seems to work as I can select the current iteration when running.
Comments
There seems to be no "Variable" for current iteration, so you can only make the planned for a parameter the user has to select. The second issue is that there is no way to select a timeline (where the current iteration would be picked), so it would not be easy to calculate the current iteration if a project area has more than one timeline.