Reporting Definition Issue with combining a list of values into one reporting row
I need help with something I have been struggling with for months. We have a report definition with business constraints that make it difficult to manage. The business wants only one row per case and a case can have multiple records for some fields that we are concatenating into a list during case filings so the data can be displayed on the report in a comma delimited list. My question is: What is the best way to handle situations like this and I now have something more difficult in that it requires run time results from current date. I am thinking that a function alias might solve this but I am having issues finding example of function aliases. A good example is the operators that are assigned to a case. There can be many and the business wants them to stay on one row so I am building a list at case submit to make the list so I can use that list at reporting. I don't like this solution because if forces me to keep a property just for reporting.
Quest Diagnostics
US
No limit. However, most are 1-3 in length. For example, here are 2 variables inside Requesttypes that is a list of 3. I make these comma delimited and assign to a text property. I would like to form this string at run time so I do not have to keep updating the text property everytime the Requesttypes change. I included a picture of the Requestypes and a couple of the properties I'm reporting on that need to be in one row.
Quest Diagnostics
US
In the case of FinalizedRoyaltyChecklist and FinalizedRoyaltyDateSubmitted the list property looks like this No,01/30/2017,01/31/2017 because the first RequestTypes(1).FinalizedRoyaltyChecklist is false, RequestTypes(3).FinalizedRoyaltyChecklist is true, RequestTypes(3).FinalizedRoyaltyDateSubmitted is 20170131. So when I form the comma delimited text property, I have some logic in the data transform to put No instead of the date when false and the date in the list when true. I have other situations where I need to take the difference from the current date and the dates in the list. This is so easy when it is just one date where I use an OOTB date difference. But when I have a text property with a list of dates to compare to the current date I cannot figure out what to do. I am thinking a function alias would help me here but I can't find very good examples of sql. I have tried function alias but it does not like what I would consider normal select type sql in the source section.
Pegasystems Inc.
US
If I remember correctly, at 7.x you can nest function aliases, so if you are on a 7.x release, you should think in terms of breaking the functionality into components.
The variable/unlimited number of list items presents a problem. I've never tried to code anything like that. If there really is no hard limit, I'm not sure it can be done this way.
Quest Diagnostics
US
I like the idea of a function alias but I cannot find any documentation or examples on what is available to me in the source text box. I see some examples using case and some using some sort of p: choice but I am looking for more like taking this variable list string "date,date,date" and putting it in a temp table of 3 rows, converting to dates and subtracting from the current date, then putting the list back together in days from current date. All this can be done with SQL commands but I don't know how to include it in the source text. Do you think I could build an Oracle SQL procedure can then call it from my function alias by doing something like exec myProcedure in the source text area. myProcedure would take a text string as input and return a text string.