Using multilist fields in custom reports

Friday, December 11, 2020
Avatar

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
Avatar
re: dl.pie Friday, December 11, 2020

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

Tuesday, December 15, 2020
Avatar
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
Avatar
re: dl.pie Tuesday, December 15, 2020

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

https://www.inflectra.com/Support/KnowledgeBase/KB638.aspx

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

 

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