It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Follow this question
Question details

×191
×84

question asked: May 24 '20, 9:05 a.m.
question was seen: 951 times
last updated: May 26 '20, 9:11 a.m.

Related questions

FAQ - How this Forum works

progressive report in JRS.

0
Raj Kapoor (27●4●19●30) | asked May 24 '20, 9:05 a.m.

I 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

Most liked answers ↓|Newest answers|Oldest answers

0
permanent link
Francesco Chiossi (5.7k●1●11●19) | answered May 26 '20, 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 '20, 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 '20, 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.


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.