Report Performance

[sewilj]

I have a created a report in v8.5 which uses Running Totals based on selection formulas. The report is created so that it is grouped by month, and then by week. The report contains about 34 Running Totals for each week, with the same number again for the months totals, and again for the overall totals. The report can be run for a complete years worth of data and reads about 750,000 records.

The report collates the data quite quickly but then spends an eternity creating the report - just over 5 hours.

As I have never been on a CR training course can anybody tell me if this is normal or if there are more efficient ways to produce a report when it reads this amount of data.

thanks for anyones help :=)

Lewis Johnson
Midlands, United Kingdom

 

[HowardHammerman]

This is not normal. But it happens. You did not mention how you connected to your database or what database you are using.

If you are using an SQL type database you might want to use a stored procedure to select the records and join the tables.

You can experiment with using indicator formulas and regular totals rather than running totals.

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[sewilj]

Howard

You are right in your assumption that we are using an SQL Database, v7 to be exact (connecting through ODBC). Unfortunately I am not experiences in creating stored procedures so would not know where to start with that one. However, I’m intrigued to know what you mean by an indicator formula as this is something that I have not heard of before.

The running totals I use enable me to perform counts where certain criteria are met; such as if a record is 'live or closed'. Would the indicator formulas allow me to perform that same counting function???

Thanks

Lewis
United Kingdom

 

[lbass]

I have the same problem of slow reports when I use many running totals. If the fields you are evaluating don't have duplicate fields, you could use conditional formulas like the following, which would process much faster:

if {table.gender} = "F" then 1 else 0

And then insert summaries on these formulas. If you have duplication because of table joins or for other reasons as in:

Name Gender Service Type
Jane Doe F 123
Jane Doe F 456

...you could try a formula like:

if {table.gender} = "F" and
{table.name} <> next({table.name}) then 1 else 0

But you can't insert a summary on this, so you would have to use a variable to sum it, which is in essence a manual running total, so you might as well use the simpler to implement method of using the running total editor. Because running totals are second-pass, they are processed locally, thus taking much longer.

-LB

 

[sewilj]

lbass

From what you have said I would be unable to create a summary on something similar to below then it looks like I am stuck with the running totals.

if {table.status} = &quot;L&quot; and
not({table.CPCODE} like &quot;TST&quot and
not({table.MAKE} like &quot;test&quot then 1 else 0

Thanks for the help

Lewis
United Kingdom

 

[lbass]

You should be able to insert a summary on that formula without a problem, and if records do not contain duplicate fields, then this will result in a speedier report. In my example, the issue was not that there were multiple criteria, but that the next/previous function (which can help deal with duplicates) refers to another record, and therefore is a second pass formula.

Separately from this issue, I wonder about your use of &quot;like&quot; rather than &quot;=&quot;. I'm not sure the like function would work correctly without the use of wildcards. Does the &quot;TST&quot; always begin a field or is it the exact field? If you are trying to detect the presence of &quot;TST&quot; anywhere within the field, then I would use the instring function instead.

-LB

 

[sewilj]

lbass

I have a lot of info to wade through but fortunately each record is individual. What I am trying to achieve is a record count of all data falling within a selected date range that does not contain any test of void data. This is identified by certain fields containing data such as TST, TEST or void.
Perhaps if I re-write the formula as

if {table.status} = &quot;L&quot; and
{table.CPCODE} <> &quot;TST&quot; and
{table.MAKE} <> &quot;test&quot; then 1 else 0

and then give it a try it will improve things.


Lewis
United Kingdom

 

[Madawc]

I've found that Crystal 8.5 can be very slow when you make complex data links. One solution is a stored procedure, as HowardHammerman suggested. I managed it without any formal training, it's probably not good SQL but it is better than what I had.

You could also try subreports, which are slow when placed in detail lines, but maybe faster than what you've got.

I'd also try doing some experimental copies of the report, to isolate the problem. Does the report become much faster if you take out most of the running totals? Does taking out one dataset improve things? Once you know where the problem lies, you can look for another method to collect the same data.

Madawc Williams
East Anglia, Great Britain