Multiple values in Process Report column

Question

Hello everyone, 
 I am having issues with querying process report which has multiple (different) values in one column. The column is filled with data from a list of CDTs, for example: 
 
 I've tried setting it as a list of integers and strings, but either way, when trying to query all rows which have a value "12345" in that column (by using a!queryProcessAnalytics), I get no results. 
 a!queryFilter(
 field: "c4",
 operator: "includes",
 value: tostring("12345")
) 
 I also tried all different combinatios (in, includes, strings, integers....). 
 
 Thanks in advance!

Mike Schmitt · Accepted Answer

I fear you're not quite understanding the full capability of the solution I've detailed here. Sorry if my description thus far has been misleadingly vague or something. 
 The array is concatenated such that the value ends up like "[123][1234][234][456]" (etc). See my previous configuration screenshot for the expression code used to generate this string. 
 When querying, you use "includes", and the value you pass into the filter is done thus: 
 operator: "includes", value: "[" & ri!myValue & "]" 
 This will find exact matches , as I previously said.

Nurudin Lujinovic · Answer

anak3061 you will have to convert the value in column from array into one value, for example tostring("12345; 7166"). 
 a!queryFilter expects in the field to have only one value per row, and not an array. 
 In your case value in the field c4 is an array {12345; 7176}, but it should be one value. If you concatenate them, or cast them to string, it will be considered as one value. 
 After that filter will work. 
 Beware that, if you use tostring , it will include all rows which have that value in that column. 
 You will need to check which one you really need from the returned rows, or add additional filter(s) based on the business logic. 
 I hope it helps you in this endeavor.

Dimitris Soulovaris · Answer

What you could do more is to build a logic and additionally filter the result set of the initial queryProcessAnalytics. 
 You could try this one: 
 a!localVariables(
 local!resultData: a!queryProcessAnalytics(
 report: cons!DS_TASK_ASSIGMENT_PBPM,
 contextProcessModels: cons!DS_PM_TASK_ASSIGNMENT,
 query: a!query(
 filter: a!queryFilter(
 field: "c3",
 operator: "includes",
 value: ri!text
 ),
 pagingInfo: a!pagingInfo(1, 10)
 )
 ).data.c3,
 local!splitText: a!forEach(
 items: local!resultData,
 expression: fn!split(fv!item, ";")
 ),
 local!searchIndexes: wherecontains(
 ri!text,
 touniformstring(local!splitText)
 ),
 index(
 local!resultData,
 local!searchIndexes,
 null()
 )
) 
 This is the result

Dimitris Soulovaris · Answer

In the process report make the C4 field as string with tostring() function and use includes the same way you have posted in the code.

Mike Schmitt · Answer

The way I've handled this sort of thing before is to force the column's data into a flattened string (though usually i'll create a brand new column for this, even if it seems redundant), because once it's a flattened string (instead of list), the query should work on it. 
 edit: see the example I posted in another comment of mine in this post, here .

Mike Schmitt · Answer

The solution to this might be to join on a delimiter - that way, you can just do your "includes" operator on the value concatenated with that delimiter, and it should work. I'll need to double-check whether you can easily do the text flattening in the report configuration to join on a delimiter, but I think this is one of the few functionalities that it actually does support.

Mike Schmitt · Answer

e.g. for this array-type CDT PV, i just join them in such a way that every value ends up included in [square] brackets - meaning an "includes" would merely need to concat square brackets to your value as well to safely find the whole value you want.

Multiple values in Process Report column

Top Replies