It's all about the answers!

Ask a question

progressive report in JRS.


Raj Kapoor (2541926) | asked May 24, 9:05 a.m.

am looking for progressive report in JRS.
Report sholuld contain-
1. Module requirement validated by test case.
2. Object Type attribute = Req
3. Time - Last 6 month.

I am able to create a report with above requirement but the line chart is not progressive, it display count of specific date . Example- If requirement count is 10 for May 12, 15 For May 13, and 12 for May 15 then it display the count against specific date.

I am looking for progressive chart which display sum of requirement on specific date- Example
May 12 - 10,
May 13 - 25,
May 15- 37

I tried to edit SQL query but it display random number in table column- Object Type
SQL-

SELECT DISTINCT T2.REFERENCE_ID AS REFERENCE_ID1,
       T2.NAME,
       T1.CREATION_DATE,
       T5.LITERAL_NAME AS LITERAL_NAME,
       T3.LITERAL_NAME AS LITERAL_NAME1,
       SUM (CASE WHEN T4.LITERAL_NAME = 'Req'  THEN T1.REQUIREMENT_ID ELSE 0 END ) AS LITERAL_NAME2
FROM RIDW.VW_REQUIREMENT T1
INNER JOIN ( RIDW.VW_REQUIREMENT_TESTCASE_LOOKUP LT1
  INNER JOIN RIDW.VW_TESTCASE T2
  ON (LT1.TESTCASE_ID = T2.TESTCASE_ID) AND T2.PROJECT_ID IN (65, 116)  AND T2.ISSOFTDELETED = 0
) ON (T1.REQUIREMENT_ID = LT1.REQUIREMENT_ID) AND LT1.LINK_TYPE = 'com.ibm.clm.validatesRequirement'
LEFT OUTER JOIN RICALM.VW_RQRMENT_ENUMERATION T3
ON T3.REQUIREMENT_ID=T1.REQUIREMENT_ID AND T3.NAME='Requirement Status'
LEFT OUTER JOIN RICALM.VW_RQRMENT_ENUMERATION T4
ON T4.REQUIREMENT_ID=T1.REQUIREMENT_ID AND T4.NAME='Object Type'
LEFT OUTER JOIN RICALM.VW_RQRMENT_ENUMERATION T5
ON T5.REQUIREMENT_ID=T1.REQUIREMENT_ID AND T5.NAME='Implementation state'
WHERE T1.PROJECT_ID IN (65, 116)  AND
((
T1.CREATION_DATE >= '2018-04-01' AND
T1.REQUIREMENT_TYPE = 'Requirement' 
) AND
(
T3.LITERAL_NAME = 'accepted' OR
T4.LITERAL_NAME = 'Req' OR
T5.LITERAL_NAME = 'Implemented' 
)
) AND
T1.ISSOFTDELETED = 0 AND
(T1.REQUIREMENT_ID <> -1 AND T1.REQUIREMENT_ID IS NOT NULL) AND
(T2.TESTCASE_ID <> -1 AND T2.TESTCASE_ID IS NOT NULL)GROUP BY T2.REFERENCE_ID,
         T2.NAME,
         T1.CREATION_DATE,
         T5.LITERAL_NAME,
         T3.LITERAL_NAME

result - Under Object type column-
Numbers are 32144, 56788
It should be in single digit

One answer



permanent link
Francesco Chiossi (5.7k1719) | answered May 26, 4:25 a.m.
Hello Raj,

if I read this line right:
SUM (CASE WHEN T4.LITERAL_NAME = 'Req'  THEN T1.REQUIREMENT_ID ELSE 0 END )
you are summing the requirement ids while if you want a count you might want to sum a fixed amount, e.g. 1.

Best Regards,

Francesco Chiossi


Comments
Raj Kapoor commented May 26, 7:48 a.m.

Thanks for response.

That's correct, initially I was counting number of requirement with object type = Req and it displayed correct count to me.
So I tried the same for for sum.

Please suggest what should I query So that I can get sum correctly.


Raj Kapoor commented May 26, 9:11 a.m.

 I have made changes in query but even that not seem to give correct information

SUM(CASE WHEN T4.LITERAL_NAME = 'Req'  THEN 1 ELSE 0 END) OVER (PARTITION BY T1.CREATION_DATE) AS LITERAL_NAME2

Your answer


Register or to post your answer.