Use of LISTAGG function and/or use of Crystal concatenation formulas with dynamic parameters

Hi,
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!
Best regards,
WDelaney

wdelaney05Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
First CLOBs aren't all that scary.  With every new release of Oracle they are basically becoming just like varchar2.

I'm confused.  I'm not suggesting ANY database changes.

You shouldn't have to store/save anything in a CLOB.  What I'm suggesting is pivot your data and generate a CSV just like LISTAGG but using an XML trick.

The output of this XML trick generates a CLOB as output to be passed back to the report.

Where does storing them come into the mix?

Check out the example below.  The resulting 'string' is 9,999 characters of a CSV.  This exceeds the 4,000 character limit of a varchar2.
drop table tab1 purge;
create table tab1(col1 char(1));

--generate 5000 rows
insert into tab1 (select 'a' from xmltable('1 to 5000'));
commit;

select dbms_lob.getlength(myCSV), myCSV from
(
SELECT 
       RTRIM(
           EXTRACT(XMLAGG(XMLELEMENT("s", col1 || ',')), '/s/text()').getclobval(),
           ','
       )
           myCSV
FROM tab1
)
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I'm not a Crystal person but there is a limit in what listagg can return.  I believe you are limited to the limits of a varchar2 which is 4000 characters.

Can crystal handle an Oracle CLOB?  Try the XML version of column concatenation:

http://www.experts-exchange.com/Database/Oracle/Q_24914739.html?#a25864822
0
 
mlmccCommented:
What version of Crystal?

It sounds like you are using CR 9 or later which should have a limit of 64k for the string length.

You are correct.  Commands are run just like stored procedures, before the report is run and parameters are selected.  Commands can have parameters but they can't be dynamic.  Are there any parameters you can use that could be static to at least start filtering the data?

mlmcc
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
wdelaney05Author Commented:
I'm using Crystal 2008 version 12.1.0.892.

I'm not sure I have any parameters I can use that could be static to start filtering the data. Can I hard code something - would the dynamic parameter override it?
0
 
wdelaney05Author Commented:
Can I "Add Command" and make a direct sql command? Can I somehow give the SQL command with parameters like:
Select *from employees where employeeid = ?
How would something like this offer the user a drop down list to choose a value?
0
 
mlmccCommented:
Commands allow you to add parameters.  There is a parameter button when you create the command.  You can add the current set of values to the list.  The only problem is if you have a constantly changing list or rapidly growing list.  If the list of values doesnt change very often then you could use statuc.

If you filter with somethin hardcoded then the filter from the report would apply only to the records passed and it wouldn't be overridden.

mlmcc
0
 
wdelaney05Author Commented:
Aargh. I have a growing list (it's also modified on a regular basis). Can I add another command object to my report, link it to my main command object and somehow have the results of the second one be used to select from and then be passed on as the parameter value(s) to the main command object?
0
 
wdelaney05Author Commented:
There's a Notes field from my table that seems to be a major culprit. All of the comments concatenating in the Notes column make it quite large. It's ok if this field is truncated. How do I truncate a varchar field in my SELECT statement?
0
 
wdelaney05Author Commented:
Wait, I think I can use RIGHT(myfieldname,4000) AS Notes
0
 
wdelaney05Author Commented:
no, wait, it's substr(myfieldname,1,4000 AS Notes. Sorry, work with too many languages.
0
 
wdelaney05Author Commented:
What I want to know is, and this may sound like an eejit question, is when you have truncation in the SQL in the SELECT statement in the command object, it truncates the data when it runs before the dynamic parameter, it must truncate it again after the filtering? Does it truncate twice? Once for the first time pulling everything in and then a second time after the filtering?
0
 
mlmccCommented:
The second filtering should only affect whether a record is selected for the report not the actual data in the record.

mlmcc
0
 
slightwv (䄆 Netminder) Commented:
Are you sure Crystal Reports cannot handle an Oracle CLOB?  Seems like the easiest approach and will allow you to not truncate data on a report.
0
 
wdelaney05Author Commented:
I've been trying to find this out, slightwv. Sounds like from Google that CR2008 does not….also sounds like we would need to be on Oracle 11, and we are on 10, I think.
0
 
slightwv (䄆 Netminder) Commented:
Oracle 10 will work for what I posted.  LISTAGG is 11gR2 only.

I Googled around and saw a few threads that talk about CR2008 and Oracle CLOBS before I posted.  I just hated to comment on them because, well, it's Internet info...

For example:
http://forums.sdn.sap.com/thread.jspa?threadID=1798095&tstart=105

Hopefully mlmcc will be back sometime and can confirm.  They are about the only Expert I've seen on the site that works with Crystal.
0
 
wdelaney05Author Commented:
Looks like there is a limit of 254K characters, havent tried this just yet. http://www.crystalkeen.com/articles/crystalreports/string254.htm

Also noticed in another forum that CLOBs may have an issue in Crystal; they may display as blank fields:
http://forums.sdn.sap.com/thread.jspa?threadID=1798095
0
 
slightwv (䄆 Netminder) Commented:
>>Looks like there is a limit of 254K characters, havent tried this just yet.
I think the important word here is  "string".  The other link talks about "memo" field object with a 64K limit.

Also talks about a work-around with creating a stored procedure.

>>they may display as blank fields:

Same link I was reading....

But, it may or may not occur for you.  I wouldn't worry about potential issues with CLOBs right now.

0
 
mlmccCommented:
Your article on string length limit is 254 characters is referriing to versions pre-CR9.

mlmcc
0
 
slightwv (䄆 Netminder) Commented:
mlmcc,
Does wdelaney05's version of CR support CLOBs?  If so, can you help them add it to their report?
0
 
wdelaney05Author Commented:
mlmcc,
If I use substr(myfieldname,1,4000) AS Notes in my SQL does this mean that it will retrieve 4000 char when the command objects runs first and then, when I apply the dynamic parameter filters, it's possible that the data I need won't be part of the retrieved 4000?
0
 
wdelaney05Author Commented:
For one of my fields, I have this code in a WITH statement in my command object:
LISTAGG(To_char(di.scan_time,'MM/DD/YYYY HH24:MI:SS'), ', ') WITHIN GROUP (ORDER BY pe.contact_date) AS "Date Scanned"

In my final SELECT statement, I have this:
 substr(docs."Date Scanned",1,4000) AS "Date Scanned"
but Crystal does not like this. It displays an err msg: Failed to retrieve data from the database. Details: ORA-12801: error signaled in parallel query server P005. ORA-04189: result of string concatenation is too long
Wouldn't you think the 4000 is ok in the substr statement?
0
 
wdelaney05Author Commented:
Is there a memo field option in Crystal or is this just through Oracle?
How would I concatenate substring statements to divide the data out into multiple columns in Crystal?
0
 
mlmccCommented:
Crystal can use memo fields but they can't be used in the record selection.

That doesn't look like a Crystal error.
Does the SQL run in an Oracle query tool like TOAD?

I don't know what a CLOB is.  I don't use Oracle.

mlmcc
0
 
slightwv (䄆 Netminder) Commented:
>>Wouldn't you think the 4000 is ok in the substr statement?

The issue is LISTAGG has to build the string BEFORE substr can do it's thing.  It's the call to LISTAGG causing the error.

If you use the XMLAGG call I mentined in http:#a35471387, the XML call returns a CLOB.  If CR cannot handle that natively, you can call substr for that.

>>I don't know what a CLOB is.  I don't use Oracle.

Character Large Object.  I believe SQL Server has something similar.

0
 
wdelaney05Author Commented:
slightwv:
Re: It's the call to LISTAGG causing the error.
Ah! Got it!
Currently, I'm working on a suggestion previously mentioned by mlmcc: I'm creating a parameter in the command object. This then calls the dynamic parameter window to select a value for that parameter. So, this could be a workaround.
0
 
wdelaney05Author Commented:
ok, I tried a few things out but it all comes down to one thing. I definitely need dynamic parameters and because of this I cannot use LISTAGG in the SQL in the command object.
It's definitely the call to LISTAGG causing the error.  
And apparently, CLOBS haven't been used by anyone here and I was told to look into possible storage issues when one is using CLOBS, there seemed to be some hesitation for me to be using them.
I've notified the user that they'll have to have multiple of the same record but that I can suppress the columns with multiple values in the Details section if they so choose.

In conclusion, I gave it my best shot. I tried and tested as many alternatives as I could but for my particular situation, I'm keeping the dynamic parameters but losing the LISTAGG statements.


0
 
wdelaney05Author Commented:
Sorry I left you for awhile. We created a function that then puts all of the data values for the field into a CLOB. Seems to work just fine. When you select the CLOB and display it in Crystal, there are what looks like lots of carriage returns in the data.
0
 
slightwv (䄆 Netminder) Commented:
Not sure why you created a function.  The code above will generate a CSV CLOB without a function.

If the carriage returns are in the CLOB itself, I can probably help.  If from CR, sorry.

Can you call the function in sqlplus and verify the origin?
0
 
wdelaney05Author Commented:
After discussing this off and on for a week or two, we went your route, slightwv.

After discussion with our user group, they wouldn't be reading more than 50 lines of the comments fields anyway, it would make reviewing the report data take awhile. If someone wanted to see the full narrative, they can view it in the interface for any further data validation. So, we kind of lucked out on that.

Your code is quite useful. It can even be morphed into a function where there's looping logic and the data is fetched and returned into a field like so:  ncomment := ncomment || ' ' || ncomment_line until you get to the end of 50 lines, etc.

Thanks for all of your persistent help! It took awhile to discuss and test it out, but your help in the end was quite valuable!!



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.