JOIN Function limitation - Crystal 2008

I am passing the values of a multi-select prompt from a main report to a sub report's select statement via a formula which is: "'"&Join({?multi_select},"','")&"'"
 
My select statement is similar to the following
select * from table
  where ID IN(VALUES_PASSED VIA THE FORMULA)

Using the join function to create my value list will cause a problem because it has a limitation on the length of the string which is approximately 64 k, I think.  The number of selections that the user is allowed to select will exceed this length. Is there a work around?
cookiejarAsked:
Who is Participating?
 
James0628Commented:
Basically, CR will try to pass the filtering to the server if it can.  It is definitely possible for CR to pass a multi-value parameter test to a server.  I just tried it here.  I gave a parameter 2 values and checked "Show SQL Query", and CR had included (field = 'first value' OR field = 'second value') in the query.

 FWIW, I simply used {field} = {?parameter} in the record selection formula, as opposed to something like {field} IN {?parameter}.  CR knows how to interpret the parameters and translates the simple "=" test to something more appropriate, if necessary.  My point is that you don't have to use anything special, like IN, in order for CR to try to pass the test to the server.

 Whether or not CR can pass your test to the server may depend on what db you're using (I'm using MS SQL Server), and how many values you have in the parameter.  For example, there could be some basic limit on how many characters CR can, or will, include in the query, in which case it may only pass a certain number of values, and if there are more than that, it may do the filtering locally instead.

 As I said before, check "Show SQL Query".  Run the report with a few values and check the query, and run it with all values and check the query.

 Not to harp on this, but an "All" option really seems like a good idea.  But if you can't do it, you can't do it.  Maybe something to look into in the future.

 James
0
 
mlmccCommented:
As far as I know no.

WHy do you need such a lengthy string?

mlmcc
0
 
James0628Commented:
In theory, you could pass the values in several pieces.  In the simplest case, instead of one formula with Join, you could have a set of formulas with for loops, with, for example, the first formula taking the first 100 elements, the second formula the second 100, and so on.  And the select in the subreport would be something like:

select * from table
  where ID IN(VALUES_PASSED VIA 1st FORMULA) OR
  ID IN(VALUES_PASSED VIA 2nd FORMULA) OR
  ID IN(VALUES_PASSED VIA 3rd FORMULA)
   and so on


 Or you could get a bit fancier and try counting characters, so the first formula would use a loop to pull values from the parameter, until it was getting close to the string length limit, the second formula would start pulling values at that point, and so on.

 Any formulas that won't produce any values for a given report, because the formulas before that have already handled all of the parameter values, could just produce some dummy value that won't be in your data.

 James
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
cookiejarAuthor Commented:
In response to mlmcc, I need a lengthy string because instead of filtering data once it gets to Crystal, I am filtering on the database side.   Without that filter on the database side, 30,000 rows are returned to the client and Crystal filters through the results using the multi-select parameter.  Instead of doing that, I have created a formula using the join to pass the data to the sub report in this format ‘ID1’,’ID2’,’ID3’.  In our selection list,  the user can select thousand or more titles.  
0
 
cookiejarAuthor Commented:
James0628,

Could you give me sample code of what I need to do?
0
 
James0628Commented:
What kind of values do you have in your parameter?  Do you have thousands of relatively short values (like 10 or 20 characters, or less), or hundreds or less of really long values (hundreds of characters or more), or both, or something in between?

 The number and length of the values would probably affect how I'd try to do this.

 James
0
 
cookiejarAuthor Commented:
The length of the value is 36 characters.
0
 
James0628Commented:
And you can actually have more than 1600 of those values in your parameter?  The user will actually select that many values?  The string length limit in CR appears to be 65534 characters (based on a test using ReplicateString).  Adding 3 characters per value for 2 single-quotes and a comma, a 65534 character string could hold up to 1680 39 character values.

 Just trying to wrap my head around this.

 Assuming that you are actually using that many values, that seems really inefficient on the SQL side - Using IN with a list of 1680+ values.  But I guess if you thought there was a better way, you'd be using it.  :-)

 James
0
 
cookiejarAuthor Commented:
AS far as using IN in my SQL statement, I am a novice in this arena.  I will take any recommendation.  I am trying to modify the report to make it more efficient.  Currently the report brings back all of the rows for a selected organization and crystal filters out the titles in the record selection formula using the selected mult-value parameter.  My current sql is Select * from table where organization = '{?selected_organization}'

In the record selection formula, I have {?database_title} = {?multi_select_parameter}

I thought to improve the performance of the report, only bring back rows of data that is only required by using a formula to pass the mult-select parameter to a sub report sql command to return the needed rows.  
There are some cases in the list of values for titles there may be 3,421 rows in the list.  Unfortunately the user may at the end of a quarter want to run a report to filter on all of the titles for selected organization and dump to EXEL
0
 
James0628Commented:
When you say "My current sql is Select * from table where ...", where is that SQL?  Is that something that you've entered somewhere, like in a CR Command, or is that what you see in Database > "Show SQL Query"?


 > In the record selection formula, I have {?database_title} = {?multi_select_parameter}

 Are you sure about that?  {?database_title} and {?multi_select_parameter} would normally both be parameters (that's implied by the "?" at the beginning of the name).  You're comparing two parameters?  Aside from not making much sense, that's obviously something that probably couldn't/wouldn't be passed to the server, and it wouldn't really help if it was.

 I'm guessing that {?database_title} was supposed to be a field.  If so, I'd actually kind of expect CR to already be passing that test to the server, translated to something like
 ({field} = '1st param value' OR {field} = '2nd param value' OR {field} = '3rd param value' OR  etc.)

 If you haven't checked Database > "Show SQL Query", check it and see what it says.

 As for your last comment, if the users will normally only be selecting a relative few values, and the problem is that they will sometimes want to include all values, the simplest thing would be to add an "All" option, if possible.  The user would normally select the 5 or 10 or whatever values they want, or "All" if they want everything.  How you implement the "All" option depends on your parameter, etc.  With a string parameter, the simplest thing is just to include "All" (or something similar) in the parameter values and use something like the following in the record selection formula:

({?multi_select_parameter} = "All" or
{field} = {?multi_select_parameter})


 James
0
 
cookiejarAuthor Commented:
Yes {?database_title}  is supposed to be {databasefield}.  For clarification, does Crystals filters the title  on the server side versus the client side when {field} = {?multi_select_parameter} is in the record selection formula.  For example, the SQL Command in CR is
Select * from table where department like ‘%ADMINISTRATOR%’.  The result set of this query may return 50,000 rows.  The multi-select_parameter may contain 10 titles which may filter the result set down to 100 rows.  Is this filtering done on the server side or will the 50,000 rows be sent to client and filtered on the client to 100 rows?

The current design of our interface does not allow ALL as an option.  A JAVA inteface passes a collection of titles to Crystal.  The interface allows the user to select a specific title or select all of the titles.
0
 
mlmccCommented:
WHere it does the filtering depends on whether Crystal can pass it to the database.

After you run the report look at the SQL.  It will show you what was passed to the database.

This sample  Select * from table where department like ‘%ADMINISTRATOR%’ should get passed to the database so long as the database can use LIKE

A multiple value parameter may get sent to the database but I suspect it won't.

Try a couple and see what the SQL is.

mlmcc
0
 
cookiejarAuthor Commented:
Where is the 'Show SQL Query' option located?  I am using ORACLE.  In some cases we do use ALL but I in the case when they can do multiple selections, ALL would be of no value to us.  

I ran the report with the multiple select parameters.  It returns the results from the command select * from table where department like 'ADMINISTRATOR%'.  Then Crystal filters out the selected titles on the client side.  
0
 
mlmccCommented:
It should be under the DATABASE menu

mlmcc
0
 
James0628Commented:
As mlmcc said, "Show SQL Query" is in the Database menu.  This is in the CR report designer.


  > ... but I in the case when they can do multiple selections,
  > ALL would be of no value to us.

  Unless I'm missing, or misunderstanding, something, an "All" option could still be very beneficial there.  You seem to be saying that since the parameter can include multiple values, it can include every value, so they don't need an "All" option.  That may be technically true, but a test like parameter = "All" will take almost no time, while a test like (field in (list of 1000+ values)) is going to be inherently slow.

 James
0
 
cookiejarAuthor Commented:
Yes, that's an ideal, I'll recommend this to the java programmer to incorporate all in the list.
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.