How do I place a list variable on one line seperated by commas
I have a report definition to display information about a case. One of the columns needs to contain the active assigned operators on the case. I am using the variable pxAssignedOperatorInfo.pyUserName. The report runs with the correct information but when there are more than one assigned person the report displays two rows, one for each assigned. How do I place them on one line separated by commas.
Quest Diagnostics
US
It appears pxAssignedOperatorInfo is a standard association rule created for using in a report definition. This is perfect for me except I am trying to do this with one row per case. My cases sometimes have multiple assignments so when the report runs I have repeating rows. I guess I need a way to parse the values in the standard association and form a comma delimited list. Any ideas on how to make this work?
Optus
AU
Hi Tom,
If you are using Oracle DB, you can achieve it by writing a function alias "Embed-UserFunction" class. Oracle have "LISTAGG" DB function which can be used for this case.
The function alias should be like,
(LISTAGG({1},', ') WITHIN GROUP (ORDER BY {1}) OVER (PARTITION BY PXREFOBJECTKEY
)Try this and share your results.
Pegasystems Inc.
IN
for RD's we either need to create embed-userfunc or need to build comma separated quoted values either in activity property set or java step.
Quest Diagnostics
US
Thanks Basantsingh, as you can tell I am very new to Pega and currently working on my CSSA but this problem is work related. I like your solution but need more explanation as to how to apply the value list to the report definition. At this time for pxAssignedOperatorInfo.pyUserName I am getting two rows in my output. One for each person assigned. I would like both names to be on one row.
Quest Diagnostics
US
I wanted to close this one. Thank you for all your help and suggestions. I have decided on a solution for this. I am going to create a text property to hold my comma delimited string and place it in the same class as my report definition. I will be using declare on change to determine when to update the new property. I will use a data transform or activity to concatenate pxAssignedOperatorInfo.pyUserName. I have done this successfully with other situations like this and it worked well. Again, thanks for all your help with my issue.
Tata Consultancy Services Ltd
IN
Tom,
it can be done very easily using value list, as i said you initially, if you create a value list for example .UserList, and assign values to it using data transformation , like UserList(1) = "Tom", userList(2)= "harry", UserList(3) = "SAM", and refer the property .UserList in filter logic of report definition , Rerport definition will do the rest as it form a query with all 3 names with "and" in where clause.