Using the Spira v4.x Custom Reporting with Custom Properties

In this example we shall first create a list of all the requirements and their related test cases in the current project. To do this go to the Edit Reports, create a new report and add a new custom section. Then add the 'Requirements' entity to the query. That will auto-generate the following:

SELECT value R FROM SpiraTestEntities.R_RequirementTestCases AS R 
WHERE R.PROJECT_ID = ${ProjectId}

This will display a list of all the requirement coverage fields. To display the test case custom fields as well as the basic fields, we need to join against the main test case entity:

SELECT R.REQUIREMENT_ID, R.REQUIREMENT_NAME, R.TEST_CASE_ID, R.TEST_CASE_NAME, T.CUST_02
FROM SpiraTestEntities.R_RequirementTestCases AS R
INNER JOIN SpiraTestEntities.R_TestCases AS T ON R.TEST_CASE_ID = T.TEST_CASE_ID
WHERE R.PROJECT_ID = ${ProjectId}

However it will not include the display names of any custom list fields, only text ones. Custom list fields will display just the ID of the value, not the display name.

To display the name, we will need to join the R_CustomListValues entity to the results. We also will need to specifically select the columns we want from the main requirement entity:

SELECT R.REQUIREMENT_ID, R.REQUIREMENT_NAME, R.TEST_CASE_ID, R.TEST_CASE_NAME, T.CUST_02, C.NAME
FROM SpiraTestEntities.R_RequirementTestCases AS R
INNER JOIN SpiraTestEntities.R_TestCases AS T ON R.TEST_CASE_ID = T.TEST_CASE_ID
LEFT JOIN SpiraTestEntities.R_CustomListValues AS C ON T.CUST_02 = cast (C.CUSTOM_PROPERTY_VALUE_ID
as string)
WHERE R.PROJECT_ID = ${ProjectId}

This will now display the requirement ID, requirement name, test case ID, test case name and one of the custom properties (ID and name).

Note that we had to explicitly cast the custom property value ID field to a string to match the way they are stored in the test cases entity.