Print Page | Close Window

Show SQL Query missing some report criteria

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2485
Printed Date: 28 Apr 2024 at 11:45pm


Topic: Show SQL Query missing some report criteria
Posted By: kbirchmore
Subject: Show SQL Query missing some report criteria
Date Posted: 29 Feb 2008 at 5:49am
I have a report that is based off of 4 large tables (smallest table has 3.3 million rows) and was taking a very long time to run (hours!).  I have found that one of the problems is that Crystal is discarding some of the report criteria when creating the sql statement.
 
the criteria it is discarding is using an IN clause like this:
{MPATPFRM.FORMID} in ('HO 04 11''HO 05 02')
 
If I change this to be
({MPATPFRM.FORMID}='HO 04 11' or {MPATPFRM.FORMID}='HO 05 02')
 
it will add this criteria to the sql statement and it goes from running for hours to running in under 5 minutes.
 
I am using crystal Reports 9.2.3.1699 and Crystal Reports XI 11.0.0.1994 and am having this issue in both versions.
 
I have many other reports that are using the IN clause the same as above and have found these reports are also not including this criteria in the sql statement Crystal creates but I never looked into this as these reports ran in under 5 minutes to start with as the tables they run against are much smaller.
 
In some of these reports there is many criteria in the IN clause
 
Is there a way to have Crystal create the sql statement to include this criteria in the sql statement without having to break out each item in the list into its own statement?
 
Kris
 
 


Replies:
Posted By: rahulwalawalkar
Date Posted: 29 Feb 2008 at 7:20am

Hi,

The best way to do is code view or stored procedure at backend and then use that in crystal which is the most efficient way of doing reports with large DataSets.

 

Cheers

Rahul

Posted By: kbirchmore
Date Posted: 29 Feb 2008 at 7:48am
Problem is that the developers do not have the ability to create stored procedures or views, so I am looking for way that is similar to Oracle sql's IN clause where columnvalue in ('A','B','C') without them actually having to use Add Command and type all the sql directly in.
 
 
Kris

Posted By: hilfy
Date Posted: 29 Feb 2008 at 2:26pm
Unfortunately, this is one of Crystal's limitations.   Some things, like the "IN" structure don't make it into the where clause of the SQL that Crystal generates when you're using just tables.
 
However, you don't have to enter ALL of your SQL in a Command in order to use a command.  You might try a command for just the table you're trying to filter and then link it to your other tables.  I don't know for sure if this will work, but it's an option to try.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: kbirchmore
Date Posted: 29 Feb 2008 at 2:31pm

Thanks.  I will have them give that a try.

 
Kris

Posted By: rvink
Date Posted: 02 Mar 2008 at 3:31pm
When designing a report I often use Database | Show SQL Query, to see how crystal is forming the query. Crystal can include some criteria and send it through to the server, but other criteria are not filtered until after the data is read, which can result is poor performance. Sometimes you need to change the way the criteria are specified, as in you example above. Just use what works.

If the report is a summary and does not print line details, sometimes crystal will send aggregate functions through [Sum(), Count() ...] if Data | Perform Grouping on Server is checked, but the conditions for that to work seem rather limited and I'm never used it successfully. In that case sometimes I write the query by hand Database | Database Expert | Write a command. This can dramatically improve performance.

It is possible to write part of the SQL in a command and link it to the other tables. The problem with this is that crystal is less able to optimize criteria  for the other tables in the query. Also, the join between the command and other tables cannot be optimized and performance may be worse. You could write the entire query as a command, but this approach is less flexible. It makes the report harder to maintain, and if the report accepts different parameters, crystal is unable to optimize the query according to the parameters. Sometimes you can't win.

In some cases a query will run faster if you change the order of links Database | Database Expert | Links | Order Links


Posted By: kbirchmore
Date Posted: 04 Mar 2008 at 10:36am

Database | Show SQL Query is how I found that all the criteria specified in the Record Selection formula was not being used in the SQL query.

We had our in clause formatted like this ('A''B''C') and we found by changing it to ['A','B','C'] this criteria is now used in the sql query. 
 
We have also been unsuccesful using group on server to return aggregate functions and just have the database return all rows and have crystal perform the sums and just suppress detail when users want just totals.
 
We also prefer not to write too many commands as this does make the report slightly more difficult to maintain if the end user decides they want additional criteria from another table on the report.  By just adding the table and linking it to existing tables, it is easier to add this additional information.
 
Many times, I find Crystal does not do auto joins well when you add new tables and we clear all links and then link tables, but it is possible this is not always done.   We have never tried changing the order of our joins, but we will give this a shot.
 
Thanks for all the advice.
 
Kris

Posted By: hilfy
Date Posted: 04 Mar 2008 at 10:56am
I turn off Automatic Smart Linking on the Database tab of Options so that I never have to remember to remove the automatic links.  I have our other developers set this way as well.  As I like to say, smart linking isn't smart.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: kbirchmore
Date Posted: 04 Mar 2008 at 11:46am
I see that option in Crystal Xi but don't see it in Crystal 9. 

Posted By: hilfy
Date Posted: 04 Mar 2008 at 1:30pm
You may have to look for it on another tab.  I've been working in Crystal since version 4.5 and I've always been able to turn it off.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: kbirchmore
Date Posted: 05 Mar 2008 at 11:17am
From what I can find this can not be turned off in 9 and in 10 you needed to do a registry hack to turn it off.  All versions prior to 9 allowed you to turn off auto link and it came back again in XI.
 
We will be moving to Crystal XI sometime during this fiscal year, so I can live with it for now.
 
Kris

Posted By: hilfy
Date Posted: 05 Mar 2008 at 2:41pm
Ahh...Ok.  I never worked in 9 or 10 - moved straight from 8.5 to XI - so I never had to deal with it.
 
Thanks for the info!
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: lmarchan
Date Posted: 13 Oct 2008 at 9:05am
When I tried using ['A','B','C'], I found it only worked when hardcoding the values into the report.  It did not work with a parameter.  Crystal would surround the value with double-quotes.
 
With some help I ended up creating a Command and was able to pass a parameter to that.


Print Page | Close Window