Using multilist fields in custom reports

Friday, December 11, 2020

I was wondering if anyone ran into this issue before.

One of our products uses a custom field of the 'multilist' type.

I ran into a problem when trying to create a custom report using this.

The custom property holding multiple list values will look something like '0000000015;000000011', refering to the custom list values from the CustomListValues table.

So when I want to have the actual readable names of the values selected I have to do a table merge of my artifact table with CustomListValues, where the Custom List Value ID should be contained in the CUST property.

I can use something like 'ON CustomListValue ID IN CUST property', but the ID is obviously an integer and the multiselect property's elements are strings. I can cast the integer to a string, but that won't give me the leading zeroes... As far as I know there is no way to do string formatting in E-SQL, but I'm not really an SQL wizard so perhaps I'm overlooking something here.

On the other hand, this seems like a standard enough thing to run into with Spira so I think there should be an easy solution?

Any help is appreciated!


3 Replies
Monday, December 14, 2020
re: dl.pie Friday, December 11, 2020


In general you can cast the strings as integers:

However that is for a single value custom property. For multi-valued, you'd have to first split on a comma.

Here's the knowledge base on the string functions:



Tuesday, December 15, 2020
re: inflectra.david Monday, December 14, 2020

Ok going from this I could do

Contains(ArtifactTable_CUST_0X, Concat('0', CAST(CustomListValueTable.CUSTOM_LIST_VALUE_ID as Edm.String), ',') OR EndsWith(ArtifactTable_CUST_0X, Concat('0', CAST(CustomListValueTable.CUSTOM_LIST_VALUE_ID as Edm.String)) OR StartsWith( ArtifactTable_CUST_0X, CAST(CustomListValueTable.CUSTOM_LIST_VALUE_ID as Edm.String))

It seems like an awkward solution for such a basic thing, but I guess it does the trick...


Wednesday, March 9, 2022
re: dl.pie Tuesday, December 15, 2020

Thanks for the query, we have just published a new KB that uses your join :-)

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

And if you have any questions, please email or call us at +1 (202) 558-6885


  • Started: Friday, December 11, 2020
  • Last Reply: Wednesday, March 9, 2022
  • Replies: 3
  • Views: 4195