How to get the names of Custom List values instead of their

Background

In this example we added:

1. Multi-select list as Custom Property for Incidents artifact:

First we need to create a Custom List from the Custom Properties section of Admin menu, give it a name and add some values:

Then add a Custom Property type multi-select list and as a list choose the one we have created before:

Since the multi-select custom properties in Spira are stored as comma-separated strings, so writing a general select statement will not help here.
Running the basic SELECT query

select R.Incident_ID, R.Name, R.PROJECT_Name, R.Cust_01 from SpiraTestEntities.R_Incidents as R
WHERE R.CUST_01 is not null

will generate output similar to

So, to display the actual name of the custom property not its internal ID we need to create a new query using specific set of functions.

To create the report you need to:

Go to Administration > Edit Reports
Create a new Report
Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
Choose to add a Custom Section:

Click Edit and insert the following ESQL query:

SELECT INC.Incident_ID AS ID, INC.Name AS IncidentName, CLV.Custom_Property_List_Name as ListName, CLV.NAME AS Value
from SpiraTestEntities.R_Incidents AS INC
INNER JOIN SpiraTestEntities.R_CustomListValues AS CLV
  ON Contains(INC.CUST_01, Concat(Concat('0', CAST(CLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String)), ','))
  or EndsWith(INC.CUST_01, Concat('0', CAST(CLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String)))
  or StartsWith(INC.CUST_01, CAST(CLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String))
ORDER BY INC.NAME, CLV.Custom_Property_List_Name

Click [Save] twice and you can run your report.

Execution of this query will display the expected result - using normal name of the values:

To get the full list of incidents having a multi-select list value as a Custom Property please run the saved report.
Before leaving the Report configuration page - don't forget to click on Create Default Template or just copy below text into the corresponding section:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:template match="/RESULTS">
    <table class="DataGrid"><tr><th>ID</th><th>IncidentName</th><th>ListName</th><th>Value</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="ID"/></td><td><xsl:value-of select="IncidentName"/></td><td><xsl:value-of select="ListName"/></td><td><xsl:value-of select="Value"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

2. Multi-select lists in system custom properties:

This feature became available started from version 7.2.

To create the Custom Property with multi-select list - first we need to create a multi-select list itself:
1. Go to the Product navigation menu;
2. Select the Program your Product is a member of;
3. Select Admin menu;
4. Locate the Edit Custom lists under the Custom Property section
5. Create a list and add the values.

Once you will assign a values to System Custom Property for each of the active Products those will appear in the Products list page:

Just like for the artifacts, multi-select custom properties in Spira are stored as comma-separated strings.

For instance, you can run below query to return the custom field:

SELECT R.Project_ID, R.NAME, R.Cust_01 FROM SpiraTestEntities.R_Projects as R

And you'll see this: the value of the new custom property:

We need to create a new custom query that joins comma-separated list of ID values onto the list of Logins, so to display name of the custom property not its internal ID. Our query will use a special Contains, EndsWith and StartsWith set of functions to do this join correctly:

SELECT PR.Name AS ProjectName, GCLV.Custom_Property_List_Name as ListName,  GCLV.NAME AS Value
from SpiraTestEntities.R_Projects as PR
INNER JOIN SpiraTestEntities.R_GlobalCustomListValues AS GCLV
  ON Contains(PR.CUST_01, Concat(Concat('0', CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String)), ','))
  or EndsWith(PR.CUST_01, Concat('0', CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String)))
  or StartsWith(PR.CUST_01, CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String))
ORDER BY PR.NAME, GCLV.Custom_Property_List_Name

Execution of this query will display the expected result - using normal name of the values:

In case you have multiple (3 in example) multi-select list as Global custom properties

And you need to extract them all, please use (or modify) the following query:

(
SELECT PR.Name AS ProjectName, GCLV.Custom_Property_List_Name as ListName,  GCLV.NAME AS Value
from SpiraTestEntities.R_Projects as PR

INNER JOIN SpiraTestEntities.R_GlobalCustomListValues AS GCLV
  ON Contains(PR.CUST_01, Concat(Concat('0', CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String)), ','))
  or EndsWith(PR.CUST_01, Concat('0', CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String)))
  or StartsWith(PR.CUST_01, CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String))
ORDER BY PR.NAME, GCLV.Custom_Property_List_Name
)
UNION
(
SELECT PR.Name AS ProjectName, GCLV.Custom_Property_List_Name as ListName,  GCLV.NAME AS Value
from SpiraTestEntities.R_Projects as PR

INNER JOIN SpiraTestEntities.R_GlobalCustomListValues AS GCLV
  ON Contains(PR.CUST_02, Concat(Concat('0', CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String)), ','))
  or EndsWith(PR.CUST_02, Concat('0', CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String)))
  or StartsWith(PR.CUST_02, CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String))
ORDER BY PR.NAME, GCLV.Custom_Property_List_Name
)
UNION
(
SELECT PR.Name AS ProjectName, GCLV.Custom_Property_List_Name as ListName,  GCLV.NAME AS Value
from SpiraTestEntities.R_Projects as PR

INNER JOIN SpiraTestEntities.R_GlobalCustomListValues AS GCLV
  ON Contains(PR.CUST_03, Concat(Concat('0', CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String)), ','))
  or EndsWith(PR.CUST_03, Concat('0', CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String)))
  or StartsWith(PR.CUST_03, CAST(GCLV.CUSTOM_PROPERTY_VALUE_ID as Edm.String))
ORDER BY PR.NAME, GCLV.Custom_Property_List_Name
)

The result would be the list of all three available multi-list values selected for each active product:

To display the full list of filtered items, click "Create Default Template" (or copy it from below code snipplet)

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:template match="/RESULTS">
    <table class="DataGrid"><tr><th>ProjectName</th><th>ListName</th><th>ValueID</th><th>Value</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="ProjectName"/></td><td><xsl:value-of select="ListName"/></td><td><xsl:value-of select="ValueID"/></td><td><xsl:value-of select="Value"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

And then click "Save" twice.

Now you can run and use the report:

How to get the names of Custom List values instead of their IDs

Background

1. Multi-select list as Custom Property for Incidents artifact:

2. Multi-select lists in system custom properties:

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

How to get the names of Custom List values instead of their IDs

Background

1. Multi-select list as Custom Property for Incidents artifact:

2. Multi-select lists in system custom properties:

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

Search Results