Why does my Crystal Report XI report crash when I try to run it or why does it run so slow?

SQL Command Objects offer really great functionality, but they also have limitations.  You should know that a SQL Command Objects is conceptually the same as a stored procedure, as far as Crystal Reports is concerned.  Like a stored procedure, the SQL Command Object must be executed in entirety before any major Crystal Reports functionality occurs (this is due to the multi-pass method Crystal Reports uses to pass and retrieve data from the database).

As such, there are guidelines you should follow for using SQL Commands:

1)  Only use 1 SQL Command Object within a report.

2)  If you use parameters, they should be built into the Command (just like in a stored procedure), so that the data is filtered out on the database.  If you use Crystal Reports parameters (including Dynamic and/or Cascading parameters), then the filters won't be applied until all possible records have been returned by the Command.  This means that your'e returning a lot more data across the  network to your machine than is necessary.  This is horribly inefficient.

3)  Don't base parameters off a SQL Command Object.  Once again, the object has to be executed first, before values can be returned.  These values, in turn, won't be passed into the other command.  Instead (as mentioned above), they will be used to filter the data out of the client after all possible records have been returned from the primary command.

My recommendation for better performance is this:

If possible, create database views for both the main report and the parameter list.  You can then add the views to the report as if they were tables.  You only need a SQL Command or Stored Procedure if you're doing heavy data manipulation.

~Kurt

I tried to do the command query and parameter all into one, but the Crystal Reports system would reject it.  Here is my command query for the records populating the report.  Is there a way I can use this and use it for parameters too?

      SELECT B.TICKET_OWNER AS TICKET_OWNER, B.TXT_ASSIGNED_TO AS TXT_ASSIGNED_TO,
B.ENU_STATUS AS ENU_STATUS, B.TXT_APPLICATION AS TXT_APPLICATION,
B.TXT_SA_OUTAGE_DESCRIPTION AS TXT_SA_OUTAGE_DESCRIPTION, B.TXT_ICD_DESC AS TXT_ICD_DESC,
B.TXT_ICD_IMMED_ACTION AS TXT_ICD_IMMED_ACTION, B.TXT_ICD_PERM_RESOLUTION AS TXT_ICD_PERM_RESOLUTION,
B.TXT_ICD_TECH_NOTES AS TXT_ICD_TECH_NOTES, B.TXT_CRITICAL_TICKET_TYPE AS TXT_CRITICAL_TICKET_TYPE,
B.TXT_TICKET_TRACKING_REQUEST_ID AS TXT_TICKET_TRACKING_REQUEST_ID, B.TXT_SUBISSUE_REQUEST_ID AS TXT_SUBISSUE_REQUEST_ID,
B.ENU_ICD_PREVENTABLE AS ENU_ICD_PREVENTABLE, B.ENU_ICD_ALARMING_IN_PLACE AS ENU_ICD_ALARMING_IN_PLACE,
B.ENU_ICD_ALARM_ID_PROBLEM AS ENU_ICD_ALARM_ID_PROBLEM, B.TXT_CAUSE AS TXT_CAUSE, B.DTM_IMPACT_START_TIME AS DTM_IMPACT_START_TIME,
B.DTM_IMPACT_END_TIME AS DTM_IMPACT_END_TIME,
B.INT_LOCATION_COUNT AS INT_LOCATION_COUNT, B.ENU_PRIMARY_OWNER AS ENU_PRIMARY_OWNER, B.TXT_TIME_ZONE AS TXT_TIME_ZONE,
B.TIM_SUNDAY_START_TIME AS TIM_SUNDAY_START_TIME, B.TIM_MONDAY_START_TIME AS TIM_MONDAY_START_TIME,
B.TIM_TUESDAY_START_TIME AS TIM_TUESDAY_START_TIME, B.TIM_WEDNESDAY_START_TIME AS TIM_WEDNESDAY_START_TIME,
B.TIM_THURSDAY_START_TIME AS TIM_THURSDAY_START_TIME, B.TIM_FRIDAY_START_TIME AS TIM_FRIDAY_START_TIME,
B.TIM_SATURDAY_START_TIME AS TIM_SATURDAY_START_TIME, B.TIM_SUNDAY_END_TIME AS TIM_SUNDAY_END_TIME,
B.TIM_MONDAY_END_TIME AS TIM_MONDAY_END_TIME, B.TIM_TUESDAY_END_TIME AS TIM_TUESDAY_END_TIME,
B.TIM_WEDNESDAY_END_TIME AS TIM_WEDNESDAY_END_TIME, B.TIM_THURSDAY_END_TIME AS TIM_THURSDAY_END_TIME,
B.TIM_FRIDAY_END_TIME AS TIM_FRIDAY_END_TIME, B.TIM_SATURDAY_END_TIME AS TIM_SATURDAY_END_TIME,
B.TXT_LOCATION_NAME AS LOCATION_NAME, B.INT_FUNCTIONAL_PCT AS INT_FUNCTIONAL_PCT,
B.INT_IMPACT_DURATION AS INT_IMPACT_DURATION, B.INT_USER_PCT AS INT_USER_PCT, B.INT_SLOW_PCT AS INT_SLOWPCT,
B.INT_TOD_PCT AS INT_TOD_PCT, B.ENU_SCORABLE AS ENU_SCORABLE, B.TXT_STATE AS TXT_STATE,
B.TXT_LOCATION_TYPE AS TXT_LOCATION_TYPE, B.TXT_AREA AS TXT_AREA, B.TXT_CITY AS TXT_CITY,
B.DTE_START_DATE AS DTE_START_DATE, B.INT_DAILY_DURATION AS INT_DAILY_DURATION,
B.INT_DAILY_DURATION AS INT_DAILY_DURATION, B.INT_TOTAL_DURATION AS INT_TOTAL_DURATION,
B.TXT_REGION AS TXT_REGION, B.ENU_EXEC_REPORTING AS ENU_EXEC_REPORTING, A.COUNT_DATES
  FROM (SELECT   txt_area, txt_region, txt_state, txt_location_name,
                 COUNT (DISTINCT dte_start_date) COUNT_DATES
            FROM recur_site_analy_qry
           WHERE dte_start_date > {?FromDate}
             AND dte_start_date < {?ToDate}
        GROUP BY txt_area, txt_region, txt_state, txt_location_name) a,
       recur_site_analy_qry b  
 WHERE (    
        a.txt_area = b.txt_area
        AND a.txt_region = b.txt_region
        AND a.txt_state = b.txt_state
        AND a.txt_location_name = b.txt_location_name
        AND b.dte_start_date > {?FromDate}
             AND b.dte_start_date < {?ToDate}
       )

You  just need to make sure that these paramters (FromDate and ToDate) are built into the Command (there's a button on the right hand side of the edit window to create paramters).

Because this is basically a stored procedure, you can't use dynamic parameters AND you can't really use multiple value parameters (picking 5 values from a list of values, for example) without lots of additional work

~Kurt

I'm not sure what you mean.  Can you explain further what you are talking about based on my query?