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.
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.
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.
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.