We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
2,845 Views
Last Modified: 2012-05-11
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

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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:

https://www.experts-exchange.com/Database/Oracle/Q_24914739.html?#a25864822
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
W DData Analyst

Author

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?
W DData Analyst

Author

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?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
W DData Analyst

Author

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?
W DData Analyst

Author

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?
W DData Analyst

Author

Commented:
Wait, I think I can use RIGHT(myfieldname,4000) AS Notes
W DData Analyst

Author

Commented:
no, wait, it's substr(myfieldname,1,4000 AS Notes. Sorry, work with too many languages.
W DData Analyst

Author

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?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
The second filtering should only affect whether a record is selected for the report not the actual data in the record.

mlmcc
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
W DData Analyst

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
W DData Analyst

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
mlmcc,
Does wdelaney05's version of CR support CLOBs?  If so, can you help them add it to their report?
W DData Analyst

Author

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?
W DData Analyst

Author

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?
W DData Analyst

Author

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?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.

W DData Analyst

Author

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.
W DData Analyst

Author

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.


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
W DData Analyst

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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?
W DData Analyst

Author

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



Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.