Need help to build custom report to pull the modified testcases

Monday, January 31, 2022
Avatar

Can we build a Custom Report to pull the modified testcases in a given project. We looked into tables for SpiraTestEntities.R_HistoryChangeSets and SpiraTestEntities.R_HistoryDetails but none of the fields are matching to get the modified Testcase id.

4 Replies
Tuesday, February 1, 2022
Avatar
re: kiran3122 Monday, January 31, 2022

Hi Kiran

The following ESQL custom report query will display a list of all the field changes for test cases in a specific product/project:

select HC.CHANGESET_ID, HC.ARTIFACT_TYPE_NAME, HC.CHANGE_DATE, HC.ARTIFACT_ID as TEST_CASE_ID, HC.ARTIFACT_DESC as TEST_CASE_NAME, HD.FIELD_NAME, HD.OLD_VALUE, HD.NEW_VALUE
from SpiraTestEntities.R_HistoryChangeSets as HC
join SpiraTestEntities.R_HistoryDetails as HD on HC.CHANGESET_ID = HD.CHANGESET_ID 
where HC.PROJECT_ID = ${ProjectId} and HC.ARTIFACT_TYPE_ID = 2

(Artifact Type = 2 is test cases)

Regards
David

Tuesday, February 1, 2022
Avatar
re: inflectra.david Tuesday, February 1, 2022

Thanks David, Can I filter it further to pull the modified testcases in the past 1 week.

Wednesday, February 2, 2022
Avatar
re: kiran3122 Tuesday, February 1, 2022

Sure just add a where clause on one the date fields....

https://spiradoc.inflectra.com/Reporting/Custom-Report-Tables/

History Change-SetsΒΆ

  • CHANGESET_ID
  • USER_ID
  • ARTIFACT_TYPE_ID
  • ARTIFACT_ID
  • CHANGE_DATE
  • CHANGETYPE_ID
  • PROJECT_ID
  • REVERT_ID
  • ARTIFACT_DESC
  • CHANGETYPE_NAME
  • USER_NAME
  • ARTIFACT_TYPE_NAME
  • SIGNATURE_HASH
  • MEANING
Wednesday, February 2, 2022
Avatar
re: inflectra.david Wednesday, February 2, 2022

In this case, CHANGE_DATE.

Statistics
  • Started: Monday, January 31, 2022
  • Last Reply: Tuesday, April 19, 2022
  • Replies: 4
  • Views: 369