It's all about the answers!

Ask a question

JRS Report Builder v6.0.2 DB2 SQL Percentage Conversion of Actual_Work divided by Planned_Work does not work


Richard Kissel (30717) | asked Apr 07 '17, 7:22 p.m.

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



permanent link
Richard Kissel (30717) | answered Apr 18 '17, 5:09 p.m.

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


permanent link
Salman Shaikh (23413875) | answered Apr 19 '17, 5:18 a.m.
edited Apr 19 '17, 5:20 a.m.

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.

Your answer


Register or to post 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.