[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-26
29
Medium Priority
?
2,404 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

0
Comment
Question by:wdelaney05
  • 16
  • 8
  • 5
29 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35471387
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35471829
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
 

Author Comment

by:wdelaney05
ID: 35472229
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:wdelaney05
ID: 35472287
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35472372
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
 

Author Comment

by:wdelaney05
ID: 35472391
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
 

Author Comment

by:wdelaney05
ID: 35472429
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
 

Author Comment

by:wdelaney05
ID: 35472445
Wait, I think I can use RIGHT(myfieldname,4000) AS Notes
0
 

Author Comment

by:wdelaney05
ID: 35472470
no, wait, it's substr(myfieldname,1,4000 AS Notes. Sorry, work with too many languages.
0
 

Author Comment

by:wdelaney05
ID: 35472497
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
 
LVL 101

Expert Comment

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

mlmcc
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35474998
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
 

Author Comment

by:wdelaney05
ID: 35476023
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35476072
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
 

Author Comment

by:wdelaney05
ID: 35476104
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35476176
>>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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35476311
Your article on string length limit is 254 characters is referriing to versions pre-CR9.

mlmcc
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35476334
mlmcc,
Does wdelaney05's version of CR support CLOBs?  If so, can you help them add it to their report?
0
 

Author Comment

by:wdelaney05
ID: 35477307
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
 

Author Comment

by:wdelaney05
ID: 35477367
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
 

Author Comment

by:wdelaney05
ID: 35477448
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35477664
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35478501
>>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
 

Author Comment

by:wdelaney05
ID: 35479300
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
 

Author Comment

by:wdelaney05
ID: 35489082
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35490626
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
 

Author Comment

by:wdelaney05
ID: 35495157
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35495236
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
 

Author Comment

by:wdelaney05
ID: 35782728
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses
Course of the Month18 days, 14 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question