Crystal Reports - Query too Complex

Beth Romano

I have report that I am in the process of upgraded from Crystal 8.5 to Crystal 11.5, that is returing the following error message:

failed to retrieve data from database, query is too complex.


I am trying to recreate the report from scratch hoping it would fix the problem it did not.  I have searched the knowledge base and looked over KB45743 and it did not fix the problem.  I know it has to do with the number of summary fields in the database, has anyone found a work around to solve this problem.  I don't understand if the report works in 8.5 why it will not work in 11.5.

Chester Bammel

I did a quick search on this and found a couple of possible solutions.  I am not sure about the nature of your query , but perhaps ne of these will work.

1.   Instead of having a bunch of individual lines like this:     

 {FLYER.SOITM}="181378" OR

{FLYER.SOITM}="181379" OR

{FLYER.SOITM}="181395" OR

{FLYER.SOITM}="181396" OR

{FLYER.SOITM}="181397" OR

{FLYER.SOITM}="181397" OR and so on,

have something like this:  FLYER.SOITM} in ("181378", "181379","181395" ,"181396","181397","181397")


2.   Add your query to a formula.  Then include the formula in the select statement.


Hope this helps.


 

Beth Romano

Chester Bammel:

I did a quick search on this and found a couple of possible solutions.  I am not sure about the nature of your query , but perhaps ne of these will work.

1.   Instead of having a bunch of individual lines like this:     

 {FLYER.SOITM}="181378" OR

{FLYER.SOITM}="181379" OR

{FLYER.SOITM}="181395" OR

{FLYER.SOITM}="181396" OR

{FLYER.SOITM}="181397" OR

{FLYER.SOITM}="181397" OR and so on,

have something like this:  FLYER.SOITM} in ("181378", "181379","181395" ,"181396","181397","181397")


2.   Add your query to a formula.  Then include the formula in the select statement.


Hope this helps.


 

Thanks for your suggestions, I will try that and see what happens.

James Andrews

Beth Romano:

I have report that I am in the process of upgraded from Crystal 8.5 to Crystal 11.5, that is returing the following error message:

failed to retrieve data from database, query is too complex.


I am trying to recreate the report from scratch hoping it would fix the problem it did not.  I have searched the knowledge base and looked over KB45743 and it did not fix the problem.  I know it has to do with the number of summary fields in the database, has anyone found a work around to solve this problem.  I don't understand if the report works in 8.5 why it will not work in 11.5.

If it really has to do with the number of summary fields in the database, you could probably work around it by exporting all of the records you're summarizing and letting Crystal do the math for you.  


For example, I had a report that summarized giving by fiscal year and exported them -- for a couple of categories of gifts, and I think 10 fiscal years, so there were like 20 summaries being exported, and the query and export took forever. So I changed it and instead of exporting summaries, I just exported all the gifts for the donors. Then in the report I recreated the criteria for the summaries as formulas and running totals.


Maybe something like that would help?

Chester Bammel

I would also look to see if you have "daisy-chained" formulas.  That can cause problems.  Not to discount the summaries as suggested previously, I have had reports in the past that had well over 100 summaries in the report and it would run in 30 to 45 seconds.  But that same report would take forever, or even crash until I fixed the formulas. 

The report they wanted was a week by week report for a half year (26 weeks) and there were as I recall 4 summaries for each week.  The original report had 8 weeks, but they wanted it expanded to 26.  It worked fine as I added each subsequent week, but got slower and slower until it crashed at about 14 weeks. 

What the original report was doing was calculating the date for the new week by adding 7 days to the previous week. This daisy-chaining degraded the report.  I changed it so the date for each week was individually calculated off of the starting week.  This allowed all 26 weeks to run and very fast - with as I said, a minimum of 104 summaries.

This may not apply at all to your situation, but is something you might be on the lookout for.