CR9 saved report not to refresh every time 1

[PDAnalyst]

Hello,

I have designed a report in CR9 that is hitting an MS SQL database through ODBC connection. It has a subreport that brings in certain calculations that basically looks at each incident and for each unit that responded to that incident, calculates the time that they have spent and then by using a shared variable, writes this information to the main report.

I have this report running for each year that creates nearly 85-90,000 records (and subreports). As you can imagine, this takes a very long time to run. My problem is that even after I save the report, everytime when I try to open the report or even if I want to move to the next page or any other page in the report, Crystal starts refreshing the report (thus have to wait again for 4-5 hours). Is there a way that when I save this report, to disable the report refresh?

Thanks,

S. Egilmez,
Crime Analyst

 

[IdoMillet]

If you place one of the special fields, such as Page N of M on the report, it would force Crystal to format the whole report before displaying the 1st page.

It sounds to me like you should re-consider the approach you have taken. There is a very good chance that by using a View, Stored Procedure, or Crystal Command as the data source, you would be able to avoid the CrossTabs and reduce processing time drastically...

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[kwalters135]

There is an option in crystal XI to turn off the "Save data with report". To get there, go to File -- Options then click on the "Reporting" tab. Not sure if this is in version 9 or if it will help, but could be worth a try.

 

[PDAnalyst]

Thanks Ido,

unfortunately I am still not well versed in CR so I don't know how to create a Stored procedure or Crystal command but would like to learn it.

I have designed this report in order to understand the call workload for our department. What the main report does is to pull all of the Calls that we recieved for the time period by each hour and the subreport then grabs all of the units that responded to these calls and then calculates the number of units that responded to the call and then also extracts the number of minutes that each responding unit spent on that call. The subreport then passes the information to the main report and at the end of each hour time period, the main report calculates the numer of calls that we recieved for that hour period, amount of time that it took us to respond to that call (this info is coming from the main report) and how many back-up units also responded to the same call and how much time they spent on the call (subreport).

This is the part that takes the longest (subreport). I am open to all suggestions on how to make this run faster (and also since I am working with a years worth of data, my machine sometimes crashes with crw32.exe error)

Thanks again,
S. Egilmez

 

[IdoMillet]

Sounds like you can simply join the call to the resonses table (instead of using a subreport). Without more detail about the tables involved and how they are linked, it is tough to provide more specific help.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[PDAnalyst]

Hi Ido,

thanks for the reply.

Here is how the Database is set up.

I have a table named Calls for Service that stores the incident index number (unique) for the incident and the time that the call came in as well as what time we arrived at the location and the primary unit number that responded.
I have a second table named Unit_Status that contains the information about each single unit. The fields in this table are the timestamp (which is unique for each entry), incident index number (that corresponds to the incident index number at the Calls for Service table and gets repeated multiple times for each unit and each action), unit ID which tells me the unit number that responded, unit status date and time (tells the exact date and time of the action) and a command code (that tells me what type of action that they took) This command code tells me when they have received the call and then when they cleared the call.

Most of the time there are more than one unit responding to the same incident number. I have to discern each of these units and do a calculation that derives the total time that each unit spent on that specific incident number.
By doing the sub-report, I can calculate this there and then pass it on to the main report. The subreport is linked to the main report by the Incident index number.

I have tried to link the Unit-Status table directly to the Calls for Service table but then I get multiple entries of the same call (I get one for each unit responded) which creates a chaos.

I will appreciate any guidance how to circumvent this.

Thanks,

S. Egilmez

 

[IdoMillet]

Join CALLS to UNIT STATUS and then once again to UNIT STATUS (aliased). Add a record selection condition that retricts the records in the first instance to those where Command Code is "Received" and retricts the records in the second instance to those where Command Code is "Cleared".

Compute the difference in time between the time stamp in the Received instance and the time stamp in the cleared instance.

From here on, it's clear sailing...

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[PDAnalyst]

Thanks Ido,

I will construct it that way and give feedback,

S. Egilmez