Incident - How Many Times In A Status

Monday, February 15, 2016
Avatar
All

My report writing skills within SpiraTest are sadly not the strongest. If I had DB access I could probably produce the report i require.

For a project i am currently on it would be fantastic to generate a custom report which outputs all the incidents within a project, the name and the description and the current status and how many times it has changed status and output the historic status names 

I know we can see this within each incident. A report to show it would be great
3 Replies
Tuesday, February 23, 2016
Avatar
inflectra.sarahx
re: itqatestmanagement_impellam Monday, February 15, 2016
Hi Scott

I just dug an example report (that we created for another customer)

It generates a report of requirements and the number of times they have been modified:

select H.ARTIFACT_ID as RequirementID, R.NAME as RequirementName, X.VERSION_NUMBER as ReleaseVersionNumber, count(H.ARTIFACT_ID) as ModifiedCount from SpiraTestEntities.R_HistoryChangeSets as H inner join SpiraTestEntities.R_Requirements as R on R.REQUIREMENT_ID = H.ARTIFACT_ID inner join SpiraTestEntities.R_Releases as X on X.RELEASE_ID = R.RELEASE_ID where H.PROJECT_ID = ${ProjectId}

and H.ARTIFACT_TYPE_ID = 1 group by H.ARTIFACT_ID, R.NAME, X.VERSION_NUMBER

RequirementIDRequirementNameReleaseVersionNumberModifiedCount
91new test req1.0.0.02
92Ability to edit editions1.0.0.03
4Ability to add new books to the system1.0.0.0.00017
5Ability to edit existing books in the system1.0.0.0.00014
6Ability to delete existing books in the system1.0.0.0.00024
26Ability to create new users in the system1.0.1.0.00012
9Ability to associate books with different editions1.1.0.0.00022
72Ability to archive data in the system1.1.0.0.00034
10Ability to completely erase all books stored in the system with one click1.2.0.02

Regards
Sarah
Tuesday, February 23, 2016
Avatar
inflectra.sarahx
re: inflectra.sarahx Tuesday, February 23, 2016
To some something similar for incidents you could use:

select H.ARTIFACT_ID as IncidentID, INC.NAME as IncidentName, count(H.ARTIFACT_ID) as ModifiedCount from SpiraTestEntities.R_HistoryChangeSets as H inner join SpiraTestEntities.R_Incidents as INC on INC.INCIDENT_ID = H.ARTIFACT_ID where H.PROJECT_ID = ${ProjectId}
and H.ARTIFACT_TYPE_ID = 3 group by H.ARTIFACT_ID, INC.NAME

IncidentIDIncidentNameModifiedCount
1Cannot log into the application 23
6The book listing screen doesn't sort4
7Cannot add a new book to the system3
62Bug on archiving system2
63just found a bug2
64Need to support Windows 106
66bug when clicking link.2
68This is a new bug in the system2
69bug on create book page2
72defect when using the admin tools2


Regards

Sarah
Tuesday, February 23, 2016
Avatar
inflectra.sarahx
re: inflectra.sarahx Tuesday, February 23, 2016
Also if you wanted to add on Description and current status you could use:

select H.ARTIFACT_ID as IncidentID, INC.NAME as IncidentName, INC.DESCRIPTION as Description,INC.INCIDENT_STATUS_NAME as CurrentStatus, count(H.ARTIFACT_ID) as ModifiedCount from SpiraTestEntities.R_HistoryChangeSets as H inner join SpiraTestEntities.R_Incidents as INC on INC.INCIDENT_ID = H.ARTIFACT_ID where H.PROJECT_ID = ${ProjectId}
and H.ARTIFACT_TYPE_ID = 3 group by H.ARTIFACT_ID, INC.NAME,  INC.INCIDENT_STATUS_NAME, INC.DESCRIPTION

Spira Helps You Deliver Quality Software, Faster and With Lower Risk

And if you have any questions, please email or call us at +1 (202) 558-6885

 

Statistics
  • Started: Monday, February 15, 2016
  • Last Reply: Tuesday, February 23, 2016
  • Replies: 3
  • Views: 9333