Knowledge Base Article

Home Page > Knowledge Base > Spira Platform

Home Page > Knowledge Base > Spira Platform > SpiraPlan

Home Page > Knowledge Base > Spira Platform > SpiraTest

Article Using the Spira v4.1+ Custom Reporting with Custom Properties

by Adam S on Wednesday, March 26, 2014

The custom reporting functionality in SpiraTest, SpiraPlan and SpiraTeam v4.1 (or later) includes the ability to write complex reports, joining various tables, using SQL aggregation (COUNT, SUM, etc.) functions and other advanced reporting features. A common needs is to display a list of artifacts (requirements, test cases, etc.) and join against the custom property definitions so that you get the custom fields displayed with the names of the value not just the IDs. This articles explains how to do this.

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.


Attachments
Article Info
Last Updated: 3/26/2014
Article ID: KB74
# Views: 2155
Powered by KronoDesk v1.1.0.15 | © Copyright Inflectra Corporation 2011-2016 | Licensed to Inflectra Corporation.