Excel report to display the number of defects created each day per defect status

Question ID: 108469
0
0

Hello. Is there a way for us to report the metric of how many defects per day are in failed, retest, or reopened status? We are wanting to report the trend of the number defects in these 2 statuses.

Marked as spam
Posted by (Questions: 217, Answers: 21)
Asked on July 6, 2018 8:53 pm
30 views
Answers (7)
0
Private answer

@jameslcoleman, Hi James,
I had similar request - glad you posted your query right on time.
I tried your original in sql developer against one of my ALM (V11) project using the project db (Test_Project_db) and it gave me an error which was added in previous comment.

Today, I again tried your original query in Project's Analysis as directed above and unfortunalty, I am getting error. Error (Fig: Analysis) attached. ![alt text][1]

I again tried to run your modified (2nd query) against one of my ALM project (v 11). I again got the same error. Error (sql_dev) attached) below.
![alt text][2]

[1]: /storage/temp/852-analysis.png
[2]: /storage/temp/853-sql-dev.png

fyi---my DB is Oracle. Detali listed as below:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
''CORE 11.2.0.4.0 Production''
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Hope to hear from you.
As always Thank you!

Marked as spam
Posted by (Questions: 30, Answers: 35)
Answered on July 10, 2018 3:48 am
@jameslcoleman, HI James, if you could post the updated sql (oracle) query, it would help me a lot including others in this community. Appreciate your feedback.
( at July 11, 2018 4:31 pm)
0
Private answer

@jameslcoleman , Hi James, I know you have not forgotten this. But, I would like to keep this alive and on top of the community page so that it keeps refresh for all of us.
Any update on this would be greatly appreciated.

Marked as spam
Posted by (Questions: 30, Answers: 35)
Answered on July 18, 2018 3:39 pm
0
Private answer

@miraclefren2 , Hello. I have converted the query, and entered it below. Give it a try to see if it works in your oracle db.

Select AP_NEW_VALUE As ''Status'', count(TOTAL_BUGS) As ''Defect Count'', Sum(D1) As ''Day 1'', Sum(D2) As ''Day 2'', Sum(D3) As ''Day 3'', Sum(D4) As ''Day 4'', Sum(D5) As ''Day 5'', Sum(D6) As ''Day 6'', Sum(D7) As ''Day 7'' FROM (
SELECT AP_NEW_VALUE, count(BG_BUG_ID) TOTAL_BUGS,
SUM(CASE WHEN systimestamp - AU_TIME <= 1 then 1 else 0 end) D1, sum(case when systimestamp - AU_TIME <= 2 and systimestamp - AU_TIME > 1 then 1 else 0 end) D2,
sum(case when systimestamp - AU_TIME <= 3 and systimestamp - AU_TIME > 2 then 1 else 0 end) D3,
sum(case when systimestamp - AU_TIME <= 4 and systimestamp - AU_TIME > 3 then 1 else 0 end) D4,
sum(case when systimestamp - AU_TIME <= 5 and systimestamp - AU_TIME > 4 then 1 else 0 end) D5,
sum(case when systimestamp - AU_TIME <= 6 and systimestamp - AU_TIME > 5 then 1 else 0 end) D6,
sum(case when systimestamp - AU_TIME <= 7 and systimestamp - AU_TIME > 6 then 1 else 0 end) D7
FROM td.BUG
INNER JOIN td.AUDIT_LOG al on AU_ENTITY_ID = BG_BUG_ID and AU_ENTITY_TYPE = 'BUG' and AU_ACTION ='UPDATE'
INNER JOIN td.AUDIT_PROPERTIES ap on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS'
INNER JOIN (
SELECT MAX(AU_TIME) MY_TIME, AU_ENTITY_ID MY_BUG_ID FROM td.AUDIT_LOG
INNER JOIN td.AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS' and AU_ENTITY_TYPE='BUG' and AU_ACTION='UPDATE'
GROUP BY TO_CHAR(AU_TIME,101), AU_ENTITY_ID
) IJ on MY_TIME = AU_TIME and MY_BUG_ID = BG_BUG_ID
WHERE systimestamp - AU_TIME <= 7 GROUP BY AP_NEW_VALUE, AU_TIME ) MAP GROUP BY AP_NEW_VALUE

Marked as spam
Posted by (Questions: 217, Answers: 21)
Answered on July 18, 2018 9:19 pm
@jameslcoleman, Hi James, Thank you so much for your prompt response with the modified query. However, the modified query stilling failing in a ''Group'' condition i.e. line # 16 as shown in below screen shot with blue highlight. I tried to run the modified qury directly in project (Analysis->excel) as well in sql developer and both platform gave me the same error. Again, as to remind, my db platform is oracle. ![alt text][1] [1]: /storage/temp/861-new-error.png
( at July 19, 2018 3:47 am)
0
Private answer

@jameslcoleman, Hi James,
Your original query must be fantastic for people who are using mysql. But, its little bit of pain for people like me who are using oracle platform. The main pain is with the time and group by condition in this query while using in mysql vs oracle.
The query which you had modified as expected to work in Oracle did not work because of wrong format of time and group condition.
So, I head down and finally got the result as expected by time and group by parameters.

Thank you so much for your hard work.

Marked as spam
Posted by (Questions: 30, Answers: 35)
Answered on July 20, 2018 5:46 pm
@miraclefren2 , Hello. I apologize for not getting you the updated script sooner. Since you have got the query to work, do you mind sharing the query so I can have for future reference? I appreciate any information that you can provide. Thanks!
( at July 20, 2018 5:53 pm)
@jameslcoleman, James, I won't mind sharing the query that work best in Oracle platform. Query is added in a new comment section. Just to make an enhancement in this query, do you think we can change ''Day 1''...''Day 7'' to Date from today's date to 7 previous date. Example: 7/20/18 ...7/14/18. This would be really great to see as an enhancement.
( at July 20, 2018 6:02 pm)
0
Private answer

Hi James,
Here is a tested query in Oracle platform.

`Select AP_NEW_VALUE As ''Status'', count(TOTAL_BUGS) As ''Defect Count'', Sum(D1) As ''Day 1'', Sum(D2) As ''Day 2'', Sum(D3) As ''Day 3'', Sum(D4) As ''Day 4'', Sum(D5) As ''Day 5'', Sum(D6) As ''Day 6'', Sum(D7) As ''Day 7'' FROM (
SELECT AP_NEW_VALUE, count(BG_BUG_ID) TOTAL_BUGS,
SUM(CASE WHEN SYSDATE- AU_TIME <= 1 then 1 else 0 end) D1, sum(case when SYSDATE- AU_TIME <= 2 and SYSDATE- AU_TIME > 1 then 1 else 0 end) D2,
sum(case when SYSDATE- AU_TIME <= 3 and SYSDATE- AU_TIME > 2 then 1 else 0 end) D3,
sum(case when SYSDATE- AU_TIME <= 4 and SYSDATE- AU_TIME > 3 then 1 else 0 end) D4,
sum(case when SYSDATE- AU_TIME <= 5 and SYSDATE- AU_TIME > 4 then 1 else 0 end) D5,
sum(case when SYSDATE- AU_TIME <= 6 and SYSDATE- AU_TIME > 5 then 1 else 0 end) D6,
sum(case when SYSDATE- AU_TIME <= 7 and SYSDATE- AU_TIME > 6 then 1 else 0 end) D7
FROM test_project_alm_db.BUG
INNER JOIN test_project_alm_db.AUDIT_LOG al on AU_ENTITY_ID = BG_BUG_ID and AU_ENTITY_TYPE = 'BUG' and AU_ACTION ='UPDATE'
INNER JOIN test_project_alm_db.AUDIT_PROPERTIES ap on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS'
INNER JOIN (
SELECT MAX(AU_TIME) MY_TIME, AU_ENTITY_ID MY_BUG_ID FROM test_project_alm_db.AUDIT_LOG
INNER JOIN test_project_alm_db.AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS' and AU_ENTITY_TYPE='BUG' and AU_ACTION='UPDATE'
GROUP BY TO_CHAR(AU_TIME,'HH24'), AU_ENTITY_ID
) IJ on MY_TIME = AU_TIME and MY_BUG_ID = BG_BUG_ID
WHERE SYSDATE - AU_TIME <= 7 GROUP BY AP_NEW_VALUE, AU_TIME ) MAP GROUP BY AP_NEW_VALUE` In above query, systimestamp is changed to SYSDATE and 101 in group by condition is changed to 'HH24'.

Marked as spam
Posted by (Questions: 30, Answers: 35)
Answered on July 20, 2018 8:35 pm
0
Private answer

I have entered the query below that may be what you are looking for. It displays the number of defects for each day, per defect status selected. The report displays the number of defects created over the period of a single week. I have also included a screenshot of the results displayed in the Excel spreadsheet. In the screenshot provided, I had created 15 defects with various statuses. You will need to run this query in the 'Analysis Module' within ALM. To create this report, you will need to create a 'New Excel Report' within the Analysis module, then enter the script below into the 'Configuration' tab. If the 'New Excel Report' option isn't available in the Analysis module, then you will need to add the 'ENABLE_CREATE_LEGACY_EXCEL_REPORT' parameter into the 'Site Configuration' tab within Site Admin, and set the value field to 'Y'. I hope this helps.

Select AP_NEW_VALUE As ''Status'', count(TOTAL_BUGS) As ''Weekly Defects'', Sum(D1) As ''Day 1'', Sum(D2) As ''Day 2'', Sum(D3) As ''Day 3'', Sum(D4) As ''Day 4'', Sum(D5) As ''Day 5'', Sum(D6) As ''Day 6'', Sum(D7) As ''Day 7'' FROM (
SELECT AP_NEW_VALUE, count(BG_BUG_ID) TOTAL_BUGS,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 1 then 1 else 0 end) D1, sum( case when datediff(DAY, AU_TIME, getdate()) <= 2 and datediff(DAY, AU_TIME, getdate()) > 1 then 1 else 0 end) D2,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 3 and datediff(DAY, AU_TIME, getdate()) > 2 then 1 else 0 end) D3,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 4 and datediff(DAY, AU_TIME, getdate()) > 3 then 1 else 0 end) D4,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 5 and datediff(DAY, AU_TIME, getdate()) > 4 then 1 else 0 end) D5,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 6 and datediff(DAY, AU_TIME, getdate()) > 5 then 1 else 0 end) D6,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 7 and datediff(DAY, AU_TIME, getdate()) > 6 then 1 else 0 end) D7
FROM BUG
INNER JOIN AUDIT_LOG on AU_ENTITY_ID = BG_BUG_ID and AU_ENTITY_TYPE='BUG' and AU_ACTION='UPDATE'
INNER JOIN AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS'
INNER JOIN (
SELECT MAX(AU_TIME) MY_TIME, AU_ENTITY_ID MY_BUG_ID FROM AUDIT_LOG
INNER JOIN AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS' and AU_ENTITY_TYPE='BUG' and AU_ACTION='UPDATE'
GROUP BY CONVERT(VARCHAR,AU_TIME,101), AU_ENTITY_ID
) IJ on MY_TIME = AU_TIME and MY_BUG_ID = BG_BUG_ID
WHERE datediff(DAY, AU_TIME, getdate()) <= 7 GROUP BY AP_NEW_VALUE, AU_TIME ) MAP GROUP BY AP_NEW_VALUE ![alt text][1] [1]: /storage/temp/851-capture.png

Marked as spam
Posted by (Questions: 2, Answers: 278)
Answered on July 9, 2018 6:25 pm
@jameslcoleman, Hi James, I tried to run above query in sql developer against of one my ALM project. Unfortunately, I am getting below error. Would you please update what am I doing wrong? Error Message: ORA-00936: missing expression 00936. 00000 - ''missing expression'' *Cause: *Action: Error at Line: 18 Column: 31 Fyi --- line 18 is GROUP BY CONVERT(VARCHAR,AU_TIME,101), AU_ENTITY_ID
( at July 6, 2018 10:09 pm)
@miraclefren2... Thank you for your response. After I read your comment, I realized that I failed to mention that the query I provided needs to be executed in the Analysis module within ALM. I have updated my response above to include the info on where the query needs to be run from. I will need to test this query in my SQL Developer to see what edits need to be made to the syntax to make it work correctly. Once I have it working correctly within the SQL Developer, I will update my post with the edits that were made.
( at July 6, 2018 10:49 pm)
0
Private answer

@miraclefren2 ... I have edited the SQL query to work in SQL Developer when using SQL Server Authentication. I have entered the query below.

Select AP_NEW_VALUE As ''Status'', count(TOTAL_BUGS) As ''Defect Count'', Sum(D1) As ''Day 1'', Sum(D2) As ''Day 2'', Sum(D3) As ''Day 3'', Sum(D4) As ''Day 4'', Sum(D5) As ''Day 5'', Sum(D6) As ''Day 6'', Sum(D7) As ''Day 7'' FROM (
SELECT AP_NEW_VALUE, count(BG_BUG_ID) TOTAL_BUGS,
SUM(CASE WHEN datediff(DAY, AU_TIME, getdate()) <= 1 then 1 else 0 end) D1, sum(case when datediff(DAY, AU_TIME, getdate()) <= 2 and datediff(DAY, AU_TIME, getdate()) > 1 then 1 else 0 end) D2,
sum(case when datediff(DAY, AU_TIME, getdate()) <= 3 and datediff(DAY, AU_TIME, getdate()) > 2 then 1 else 0 end) D3,
sum(case when datediff(DAY, AU_TIME, getdate()) <= 4 and datediff(DAY, AU_TIME, getdate()) > 3 then 1 else 0 end) D4,
sum(case when datediff(DAY, AU_TIME, getdate()) <= 5 and datediff(DAY, AU_TIME, getdate()) > 4 then 1 else 0 end) D5,
sum(case when datediff(DAY, AU_TIME, getdate()) <= 6 and datediff(DAY, AU_TIME, getdate()) > 5 then 1 else 0 end) D6,
sum(case when datediff(DAY, AU_TIME, getdate()) <= 7 and datediff(DAY, AU_TIME, getdate()) > 6 then 1 else 0 end) D7
FROM td.BUG
INNER JOIN td.AUDIT_LOG al on AU_ENTITY_ID = BG_BUG_ID and AU_ENTITY_TYPE = 'BUG' and AU_ACTION ='UPDATE'
INNER JOIN td.AUDIT_PROPERTIES ap on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS'
INNER JOIN (
SELECT MAX(AU_TIME) MY_TIME, AU_ENTITY_ID MY_BUG_ID FROM td.AUDIT_LOG
INNER JOIN td.AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS' and AU_ENTITY_TYPE='BUG' and AU_ACTION='UPDATE'
GROUP BY CONVERT(VARCHAR,AU_TIME,101), AU_ENTITY_ID
) IJ on MY_TIME = AU_TIME and MY_BUG_ID = BG_BUG_ID
WHERE datediff(DAY, AU_TIME, getdate()) <= 7 GROUP BY AP_NEW_VALUE, AU_TIME ) MAP GROUP BY AP_NEW_VALUE

Marked as spam
Posted by (Questions: 2, Answers: 278)
Answered on July 9, 2018 6:34 pm