Data Setup

One of the pre-requisite for this to ensure that there is a custom list set up. For the purpose of this article, I have set up a Custom Property, called "System" on "Test Case" (Spira Artifact). This custom property is of the "List" type. The data for this list comes from the pre-existing Custom List, called "Operating System." This is shown in Figure 1 below. Please note that the "Single-Select" list option is used. 

NOTE: Please note that the "System" property is added in the first position. That means the custom property will be accessed by the CUST_01.

Review the "Custom Properties Overview" reference link for more information.

 

Figure 1: Custom Property on Test Case

Custom Property Setup

 

The data for this Operating System custom list has two values as shown in Figure 2 below.

Figure 2: Custom List Configuration

Custom List Values Setup

 

Let us also confirm that some of the test cases have this "System" custom property appropriately setup. This is show in Figure 3 below showing that there are a total of 10 test cases with 6 test cases set up for "Windows" and 4 test cases set up. 

Figure 3: Test Case Setup

Test Case Setup with Custom Property

 

Writing the SQL Query

  1. Follow the reference link "Custom Graphs Tutorial" to create a new graph
  2. Use the following SQL Query
  3. Please review the "Query Explanation" section
  4. Click on "Display Data Grid" to check the results
  5. If the results are satisfactory, use one of the pre-existing chart formats

SQL Query

select 
  CV.NAME, 
  COUNT(TC.TEST_CASE_ID) as MY_COUNT 
from 
  SpiraTestEntities.R_TestCases as TC 
left join SpiraTestEntities.R_CustomListValues as CV on 
  CV.CUSTOM_PROPERTY_VALUE_ID = CAST(TC.CUST_01 AS INT32) and 
  TC.PROJECT_ID = CV.PROJECT_ID
left join SpiraTestEntities.R_CustomPropertyDefinitions as CP on 
  CP.PROJECT_ID = TC.PROJECT_ID and 
  CP.IS_DELETED = False and 
  CP.ARTIFACT_TYPE_NAME="Test Case" and 
  CP.CUSTOM_PROPERTY_LIST_ID = CV.CUSTOM_PROPERTY_LIST_ID
where 
  TC.PROJECT_ID = ${ProjectId} and 
  TC.CUST_01 is not null
group by CV.NAME
order by CV.NAME

Query Explanation

Please note the following.

  1. The CUSTOM_PROPERTY_VALUE_ID is an integer. It is compared with CUST_XY that is established as text (as we support a number of custom property types). In this case, casting is required to ensure correct datatype comparison.
  2. The CUST_01 is used here because the System property is set up on the first position. If the custom property is set up on a different position, then, please update as necessary. Remember that Spira supports 99 custom properties at the artifact level.
  3. The IS_DELETED is used to limit the data set to active custom properties.
  4. The ARTIFACT_TYPE_NAME is used to filter only "Test Case" (Case sensitive). If the custom property is set up on a different artifact, update as necessary. 
  5. If the custom property is not set up as required field, it can be empty. For this graph, the "is not null" criteria is applied on CUST_01 to only bring those that have data filled.

 

Graph Output

  1. The Chart would like the following. This can be added to the Reporting Portal also.
  2. Note that output of both the "Display Data Grid" and the actual chart (Donut chart) are shown for illustration in Figure 4.

Figure 4: Graph of Custom Values

Graph by Custom List Values