Solved

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

Posted on 2007-03-30
8
2,093 Views
Last Modified: 2008-01-30
I have a report that has two command queries in it, one for populating the report and the other command is for the dynamic parameter.  The tables are joined in about 8 fields, why does my report not want to work when I try to refresh the data or try to get new prompts for filtering?  It appears that when I try to run the report, it doesn't even go to the prompt to ask for date or the dynamic string parameter filters?  I feel as if Crystal Reports XI is becoming more and more bug prone as I try to use it.  Please prove me wrong and show me how is it that I am breaking the program so that it doesn't even really want to run.
0
Comment
Question by:VBBRett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
8 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 18824256
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
0
 

Author Comment

by:VBBRett
ID: 18825182
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}
       )
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 18825563
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:VBBRett
ID: 18825784
I'm not sure what you mean.  Can you explain further what you are talking about based on my query?
0
 
LVL 13

Accepted Solution

by:
crgary_tx earned 250 total points
ID: 18836953
if you want to use the dynamic parameters? strip the parameters in the sql and create a view. Then create the dynamic parameters on the report

Gary
0
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 250 total points
ID: 18837192
If you open up the database explorer and edit the command, you will see that there is a button on the right hand side of the edit form that allows you to either create or edit parameters.  Once you create the parameters within this form, specifically (as opposed to creating them in the Field Explorer parameters form), you can then refer to the parameters within your SQL Command code.

~Kurt
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Formula to "conditionally count/sum" 3 79
Syntax error with existing report; works fine in new report. 2 37
Page Break on Change of Field 2 38
Timezone help 5 45
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 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