JRS Report Builder v6.0.2 DB2 SQL Percentage Conversion of Actual_Work divided by Planned_Work does not work
CLM v6.0.2
SELECT DISTINCT
T3_1.LITERAL_NAME AS LITERAL_NAME2,
T1.NAME AS URL1_title,
T1.URL AS URL1,
T2_1.LITERAL_NAME,
T1.REQUEST_STATE,
T1.ACTUAL_WORK/3600.0 AS ActualWork,
T1.PLANNED_WORK/3600.0 AS PlannedWork,
T1.PLANNED_WORK/3600.0 - T1.ACTUAL_WORK/3600.0 AS Variance,
CASE WHEN T1.ACTUAL_WORK > 0 THEN
(T1.ACTUAL_WORK/3600.0)/(T1.PLANNED_WORK/3600.0) --This Does Not Work
ELSE .22 END AS Percentage,
CASE WHEN T1.ACTUAL_WORK > 0 THEN -- This Case shows that it is getting inside the case and performing the calculation on the Decimals
8.0/12.0
ELSE .33 END AS Percentage1,
CASE WHEN T1.ACTUAL_WORK > 0 THEN -- This Case shows that it can convert the T1.ACTUAL_WORK value into a Decimal
T1.ACTUAL_WORK/3600.0
ELSE .44 END AS Decimal1,
CASE WHEN T1.ACTUAL_WORK > 0 THEN -- This Case shows that it can convert the T1.PLANNED_WORK value into a Decimal
T1.PLANNED_WORK/3600.0
ELSE .55 END AS Decimal2
FROM RIDW.VW_REQUEST T1
LEFT OUTER JOIN RICALM.VW_RQST_STRING_M_EXT T2
ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='migrateorarchive'
LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T2_2
ON T2_2.REQUEST_TYPE_ID=T1.REQUEST_TYPE_ID AND T2_2.NAME=T2.NAME
LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T2_1
ON T2.VAL LIKE CONCAT(CONCAT('%|',T2_1.EXTERNAL_ID), '|%') AND T2_1.PROJECT_ID=T1.PROJECT_ID AND T2_1.ENUMERATION_NAME=T2_2.DATATYPE
LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T3
ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='Complexity2'
LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T3_2
ON T3_2.REQUEST_TYPE_ID=T1.REQUEST_TYPE_ID AND T3_2.NAME=T3.NAME
LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T3_1
ON T3_1.EXTERNAL_ID=T3.VAL AND T3_1.PROJECT_ID=T1.PROJECT_ID AND T3_1.ENUMERATION_NAME=T3_2.DATATYPE
WHERE T1.PROJECT_ID = 52 AND
( T1.REQUEST_TYPE = 'CLM Migration Inventory' AND
T2_1.LITERAL_NAME = 'Migrate' AND
T1.REQUEST_STATE <> 'Completed'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) ORDER BY T3_1.LITERAL_NAME asc
this question will not allow me to insert a picture of my results...Anyway
Time Spent = 8.00
Estimated = 12.00
Variance = 4.00
Percentage = 0%
Test_Percentage = 66.7%
Test_Decimal1 = 8.00
Test_Decimal2 = 12.00
basically I am getting into the case statement, I can obtain the values, but for some reason the values (T1.ACTUAL_WORK/3600.0)/(T1.PLANNED_WORK/3600.0) (Percentage) will not divide to give me the percentage I need to display instead it gives me 0% although I can use 8.00/12.00 and it works (Test_Percentage) and gives me 66.7%
2 answers
Answer from IBM Support: Use a Double and check for both Actual and Planned
SELECT DISTINCT
T3_1.LITERAL_NAME AS LITERAL_NAME2,
T1.NAME AS URL1_title,
T1.URL AS URL1,
T2_1.LITERAL_NAME,
T1.REQUEST_STATE,
T1.ACTUAL_WORK/3600 AS TimeSpent,
T1.PLANNED_WORK/3600 AS Estimate,
T1.PLANNED_WORK/3600-T1.ACTUAL_WORK/3600 AS Variance,
CASE WHEN (T1.ACTUAL_WORK > 0 AND T1.PLANNED_WORK > 0)
THEN DOUBLE(T1.ACTUAL_WORK/3600)/DOUBLE(T1.PLANNED_WORK/3600)
ELSE 0 END As Percentage
FROM RIDW.VW_REQUEST T1
LEFT OUTER JOIN RICALM.VW_RQST_STRING_M_EXT T2
ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='migrateorarchive'
LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T2_2
ON T2_2.REQUEST_TYPE_ID=T1.REQUEST_TYPE_ID AND T2_2.NAME=T2.NAME
LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T2_1
ON T2.VAL LIKE CONCAT(CONCAT('%|',T2_1.EXTERNAL_ID), '|%') AND T2_1.PROJECT_ID=T1.PROJECT_ID AND T2_1.ENUMERATION_NAME=T2_2.DATATYPE
LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T3
ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='Complexity2'
LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T3_2
ON T3_2.REQUEST_TYPE_ID=T1.REQUEST_TYPE_ID AND T3_2.NAME=T3.NAME
LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T3_1
ON T3_1.EXTERNAL_ID=T3.VAL AND T3_1.PROJECT_ID=T1.PROJECT_ID AND T3_1.ENUMERATION_NAME=T3_2.DATATYPE
WHERE T1.PROJECT_ID = 52 AND
( T1.REQUEST_TYPE = 'CLM Migration Inventory' AND
T2_1.LITERAL_NAME = 'Migrate' AND
T1.REQUEST_STATE = 'Completed'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) ORDER BY T3_1.LITERAL_NAME asc
Don't go like this but you can modify the query as per your requirement. I have implemented the same type of report in BIRT and is working perfectly.
Example to calculate Values first
SUM(T1.PLANNED_WORK/3600) as PLANNED_WORK,
SUM(T1.ACTUAL_WORK/3600) as ACTUAL_WORK,
Then go for Percentage.
In birt there is option to add computed columns where you can add values and calculate percentage.