Skip Navigation LinksHome Page > Forums > SpiraTeam Forums > Spira Custom Reports > Using multilist fields in...
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!
Hi
In general you can cast the strings as integers:
https://www.inflectra.com/support/knowledgebase/kb369.aspx
https://www.inflectra.com/Support/Forum/spirateam/reports/2024.aspx
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:
https://spiradoc.inflectra.com/Reporting/Custom-Graph-Tutorial/#understanding-entity-sql-esql
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/string-canonical-functions
Regards
David
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...
Thanks for the query, we have just published a new KB that uses your join :-)
https://www.inflectra.com/Support/KnowledgeBase/KB638.aspx
And if you have any questions, please email or call us at +1 (202) 558-6885
