Use of LISTAGG function and/or use of Crystal concatenation formulas with dynamic parameters
Posted on 2011-04-26
I think I'm trying to do the impossible.
I need to use dynamic parameters in my Crystal report. I also need to concatenate row values in one field for multiple fields on the report.
I'm using the Oracle LISTAGG function to concatenate the row values. The LISTAGG function works great by itself in the SQL in Oracle but Crystal complains about character limits when I run the report. I have the SQL in a command object in the report. The report seems to run the command object first (which probably brings up all criteria and therefore it's trying to cram all possible values into the fields) and then displays the dynamic parameters which would allow restrictions on the field criteria (and therefore just some of the values are concatenated into the field(s)).
I've tried taking the LISTAGG function out of the SQL and concatenating on the Crystal side. I've created three concatenation formulas for one of the fields as a test. I've placed the concatenation formulas respectively in the group header, details and group footer section. Then I suppressed the group header and details section. Crystal complained again about the character limit. I think it's because the command object is run first before the dynamic parameter list is displayed.
Is there any way to have dynamic parameters AND field value concatenation in the same report?
Many, many thanks in advance for your thoughts!