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,037 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now