Report Builder to excel: Make the results dynamic takes very long time to complete
Hello All,
We have built a report in Report Builder V6.0.4 using the Time Series option where we are trying to list all the Features belonging to a Release along with Feature's state transition states, Country columns, etc.
We have downloaded this report into excel with Live Data option and we have added a new tab into the report and have written macros to read data from QueryResults tab and write the data into the new tab as per client requirements.
Problem here is, whenever we click on 'Make the results dynamic' in the RunQuery tab, it take anywhere between 30 to 45 minutes for the data to refresh. Thats a very long wait time for us to get the live data from CLM to Excel.
Is there a way we can make this quick.
Also, its strange sometimes that the first run is quick sometimes and the subsequent runs are very slow. Not sure if this is a product defect, or if we have to do any changes on the server side, etc.
Below is the query, but we have used the GUI to build it.
Please help.
SELECT DISTINCT T2.PROJECT_NAME,
T3.REFERENCE_ID,
T3.NAME AS URL1_title,
T3.URL AS URL1,
T3.CREATION_DATE,
T3.CLOSED_DATE,
T3.CREATOR_NAME,
T2.REFERENCE_ID AS REFERENCE_ID1,
T2.NAME AS URL2_title,
T2.URL AS URL2,
T4_1.LITERAL_NAME AS LITERAL_NAME,
T2.ITERATION_NAME,
T2.REQUEST_STATE,
T1.REQUEST_STATE AS REQUEST_STATE1,
T1.DAYS_IN_STATE,
T1.PREV_REQUEST_STATE,
T1.REC_DATETIME
FROM RIDW.VW_REQUEST_STATE_HISTORY T1
INNER JOIN RIDW.VW_REQUEST T2
ON (T2.REQUEST_ID = T1.REQUEST_ID)
INNER JOIN RIDW.VW_REQUEST_RELATIONAL_LINK LT1
ON (T2.REQUEST_ID = LT1.REQUEST1_ID) AND LT1.ISSOFTDELETED = 0 AND LT1.NAME = 'com.ibm.team.workitem.linktype.trackedworkitem'
OR (T2.REQUEST_ID = LT1.REQUEST2_ID) AND LT1.ISSOFTDELETED = 0 AND LT1.NAME = 'com.ibm.team.workitem.linktype.blocksworkitem'
INNER JOIN RIDW.VW_REQUEST T3
ON ((LT1.REQUEST2_ID = T3.REQUEST_ID OR LT1.REQUEST1_ID = T3.REQUEST_ID) AND T2.REQUEST_ID <> T3.REQUEST_ID)
LEFT OUTER JOIN RICALM.VW_RQST_STRING_M_EXT T4
ON T4.REQUEST_ID=T2.REQUEST_ID AND T4.NAME='custom.ikea.workitem.attribute.country'
LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T4_2
ON T4_2.REQUEST_TYPE_ID=T2.REQUEST_TYPE_ID AND T4_2.NAME=T4.NAME
LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T4_1
ON T4.VAL LIKE CONCAT(CONCAT('%|',T4_1.EXTERNAL_ID), '|%') AND T4_1.PROJECT_ID=T2.PROJECT_ID AND T4_1.ENUMERATION_NAME=T4_2.DATATYPE
WHERE (T2.PROJECT_ID = 5 OR T2.PROJECT_ID = 87 OR T2.PROJECT_ID = 23 OR T2.PROJECT_ID = 44) AND
(T3.PROJECT_ID = 5 OR T3.PROJECT_ID = 87 OR T3.PROJECT_ID = 23 OR T3.PROJECT_ID = 44) AND
( T1.CHANGE_DATE >= '2017-01-01' AND
DAYS(CURRENT_TIMESTAMP ) - DAYS(T1.CHANGE_DATE) >= 0 AND
T2.REQUEST_TYPE = 'Feature' AND
T3.REQUEST_TYPE = 'Release'
) AND
(T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0 AND T3.ISSOFTDELETED = 0) AND
(T1.REQUEST_STATE_HISTORY_ID <> -1 AND T1.REQUEST_STATE_HISTORY_ID IS NOT NULL) AND
(T2.REQUEST_ID <> -1 AND T2.REQUEST_ID IS NOT NULL) AND
(T3.REQUEST_ID <> -1 AND T3.REQUEST_ID IS NOT NULL)
T3.REFERENCE_ID,
T3.NAME AS URL1_title,
T3.URL AS URL1,
T3.CREATION_DATE,
T3.CLOSED_DATE,
T3.CREATOR_NAME,
T2.REFERENCE_ID AS REFERENCE_ID1,
T2.NAME AS URL2_title,
T2.URL AS URL2,
T4_1.LITERAL_NAME AS LITERAL_NAME,
T2.ITERATION_NAME,
T2.REQUEST_STATE,
T1.REQUEST_STATE AS REQUEST_STATE1,
T1.DAYS_IN_STATE,
T1.PREV_REQUEST_STATE,
T1.REC_DATETIME
FROM RIDW.VW_REQUEST_STATE_HISTORY T1
INNER JOIN RIDW.VW_REQUEST T2
ON (T2.REQUEST_ID = T1.REQUEST_ID)
INNER JOIN RIDW.VW_REQUEST_RELATIONAL_LINK LT1
ON (T2.REQUEST_ID = LT1.REQUEST1_ID) AND LT1.ISSOFTDELETED = 0 AND LT1.NAME = 'com.ibm.team.workitem.linktype.trackedworkitem'
OR (T2.REQUEST_ID = LT1.REQUEST2_ID) AND LT1.ISSOFTDELETED = 0 AND LT1.NAME = 'com.ibm.team.workitem.linktype.blocksworkitem'
INNER JOIN RIDW.VW_REQUEST T3
ON ((LT1.REQUEST2_ID = T3.REQUEST_ID OR LT1.REQUEST1_ID = T3.REQUEST_ID) AND T2.REQUEST_ID <> T3.REQUEST_ID)
LEFT OUTER JOIN RICALM.VW_RQST_STRING_M_EXT T4
ON T4.REQUEST_ID=T2.REQUEST_ID AND T4.NAME='custom.ikea.workitem.attribute.country'
LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T4_2
ON T4_2.REQUEST_TYPE_ID=T2.REQUEST_TYPE_ID AND T4_2.NAME=T4.NAME
LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T4_1
ON T4.VAL LIKE CONCAT(CONCAT('%|',T4_1.EXTERNAL_ID), '|%') AND T4_1.PROJECT_ID=T2.PROJECT_ID AND T4_1.ENUMERATION_NAME=T4_2.DATATYPE
WHERE (T2.PROJECT_ID = 5 OR T2.PROJECT_ID = 87 OR T2.PROJECT_ID = 23 OR T2.PROJECT_ID = 44) AND
(T3.PROJECT_ID = 5 OR T3.PROJECT_ID = 87 OR T3.PROJECT_ID = 23 OR T3.PROJECT_ID = 44) AND
( T1.CHANGE_DATE >= '2017-01-01' AND
DAYS(CURRENT_TIMESTAMP ) - DAYS(T1.CHANGE_DATE) >= 0 AND
T2.REQUEST_TYPE = 'Feature' AND
T3.REQUEST_TYPE = 'Release'
) AND
(T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0 AND T3.ISSOFTDELETED = 0) AND
(T1.REQUEST_STATE_HISTORY_ID <> -1 AND T1.REQUEST_STATE_HISTORY_ID IS NOT NULL) AND
(T2.REQUEST_ID <> -1 AND T2.REQUEST_ID IS NOT NULL) AND
(T3.REQUEST_ID <> -1 AND T3.REQUEST_ID IS NOT NULL)