Looking for Better Report Performance

[shaleen7]

My report is made up of 16 different subreports because each column (there are 16 columns) has a different criteria. It takes a very very very long time for the report to generate.

Is there anyway I can improve the report performance?
Would changing each column, that is each subreport, to a SQL command improve performance?

 

[synapsevampire]

It would likely improve performance, but it's impossible to know unless we understand at least a little about the environment, database and requirements.

Try posting meaningful information:

Crystal version
Database connectivity used
Example data
Expected output

Columns with different criteria doesn't mean that you need a subreport, just use a Running Total field and in the Evaluate->Use a formula place the appropriate criteria.

-k

 

[shaleen7]

Crystal version is 9
Database connectivity used is Oracle
Example data

# of apps received criteria = {VRPT_TABAPP.DATREC} in DateTime (2004, 02, 01, 00, 00, 00) to DateTime (2004, 02, 29, 00, 00, 00)and {VRPT_TABAPP.APPNO }startswith ["USA"]

# of apps closed criteria = {VRPT_TABAPP.DATREC} in DateTime (2004, 02, 01, 00, 00, 00) to DateTime (2004, 02, 29, 00, 00, 00)and {VRPT_TABAPP.DATREC}and {VRPT_TABAPP.APPNO }startswith ["USA"]AND {VRPT_TABAPP.ARCHIVE}= CLOSED

# of apps abandoned criteria =
{VRPT_TABAPP.DATREC} in DateTime (2004, 02, 01, 00, 00, 00) to DateTime (2004, 02, 29, 00, 00, 00)and {VRPT_TABAPP.DATREC}and {VRPT_TABAPP.APPNO }startswith ["USA"]AND {VRPT_TABAPP.ARCHIVE}= ABANDONED


Expected output

#of apps # of apps # of apps
received closed abandoned
Smith 10 13 0

Thomas 5 7 9


I didn't know about placing different criteria in the running total field. I will try and get back to you.
Thanks

 

[synapsevampire]

You don't need subreports.

I would suggest that you NOT hardcode the date ranges, instead use a data range parameter (Insert->Field Object->Right click parameters, select date type and check the Range box) in the record selection formula something like:

(
{VRPT_TABAPP.DATREC} = {?MyDateParm}
)
and
(
{VRPT_TABAPP.APPNO }startswith ["USA"]
)
and
(
(
{VRPT_TABAPP.DATREC} startswith ["USA"]
AND
{VRPT_TABAPP.ARCHIVE}= CLOSED
)
or
(
{VRPT_TABAPP.DATREC} startswith ["USA"]
AND
{VRPT_TABAPP.ARCHIVE}= ABANDONED
)
)

This will limit the rows returned to the report to those of interest, which should improve performance.

Now use the Insert->Field Objects->Right click Running Totals->Evaluate Use a Formula and create the conditions for each required column.

You originally stated 16 columns, this is only 3, but hopefully this will help you understand how to optimize selecting rows from the database, and then conditionally performing aggreate functions.

-k

 

[shaleen7]

It doesn't seem to be working. Instead of trying to do all 16 at once I'm trying just to get 2 of the 16 columns to work.

First I place the following statement in the record selection of my select expert.

{VRPT_TABAPP.DATREC} = {?date}
and
{VRPT_TABAPP.APPNO }startswith ["USA"]AND {VRPT_TABAPP.ARCHIVE}= CLOSED
or
{VRPT_TABAPP.APPNO }startswith ["USA"]AND {VRPT_TABAPP.ARCHIVE}= active

Then I go to my Field explorer and right click on Running totals:
In the field to summarize I select the app number which by the way is an alphanumeric number.
In type of summary I place count
In the evaulate I place the following criteria
{VRPT_TABAPP.APPNO }startswith ["USA"]AND {VRPT_TABAPP.ARCHIVE}= CLOSED

Then I go back to my Field explorer and right click on Running totals and create a second running total field:
In the field to summarize I select the app number which by the way is an alphanumeric number.
In type of summary I place count
In the evaulate I place the following criteria
{VRPT_TABAPP.APPNO }startswith ["USA"]AND {VRPT_TABAPP.ARCHIVE}= active
When I place the the employee name field along with my total running total fields in the group header the values are not correct. It seems to be doing a running total of some sort.

For example:

# of apps # of apps
active closed

Smith 0 1

Thomas 39 260

Hunt 39 1329



BTW- I have Crystal 9.

Any suggestions on what I'm doing wrong.

 

[Ngolem]

well I'd change your record select to this for starters

{VRPT_TABAPP.DATREC} = {?date} and
{VRPT_TABAPP.APPNO }startswith ["USA"]AND {VRPT_TABAPP.ARCHIVE} in [ "CLOSED","ACTIVE" ];

I'll Assume Closed and Abandoned are the same thing.

How is your report grouped??? I ASSUME THERE IS ONLY ONE TABLE as that is all you show

For a single date there would not be a grouping on date

So there should be something like

Page Header (put the column headers)
Group 1 header VRPT_TABAPP.NAME (suppressed)
Details (Suppressed)
Group 1 footer (where the numbers are revealed)

In the header section you would have a formula like

//@Init Suppressed in Group 1 header

WhilePrintingRecords;
Numbervar active := 0;
NumberVar closed := 0;


In the details you would have

//@CollectData

WhilePrintingRecords;
Numbervar active ;
NumberVar closed ;

if {VRPT_TABAPP.ARCHIVE} = "ACTIVE" then
active := active + 1
else if {VRPT_TABAPP.ARCHIVE} = "Closed" then
closed := closed + 1
else
0;


Then in the Group1 footer you would have

//@DisplayActive (displayed under active header)

WhilePrintingRecords;
Numbervar active ;

active ;

//@DisplayClosed (displayed under active header)

WhilePrintingRecords;
Numbervar closed ;

closed;

that shud work....given all my assumptions



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[synapsevampire]

Your record selection formula is wrong.

I'd given you an example, you elected to change it.

Jim's also looks wrong as it doesn't guarantee the first criteria you had posted as it requires that the dat be in Closed or Active, which the first criteria did not.

If you'd like to retry with what I'd supplied, I'll gladly assist, but if you're going to change it, you're on your own.

-k

 

[shaleen7]

I've changed my select statement back to this:
(
{VRPT_TABAPP.DATREC} = {?MyDateParm}
)
and
(
{VRPT_TABAPP.APPNO }startswith ["USA"]
)
and
(
(
{VRPT_TABAPP.DATREC} startswith ["USA"]
AND
{VRPT_TABAPP.ARCHIVE}= CLOSED
)
or
(
{VRPT_TABAPP.DATREC} startswith ["USA"]
AND
{VRPT_TABAPP.ARCHIVE}= ABANDONED
)
)

I don't believe I'm doing the second part correctly. This is what I did.
Then I go to my Field explorer and right click on Running totals:
In the field to summarize I select the app number which by the way is an alphanumeric number.
In type of summary I place count
In the evaulate I place the following criteria
{VRPT_TABAPP.APPNO }startswith ["USA"]AND {VRPT_TABAPP.ARCHIVE}= CLOSED

Then I go back to my Field explorer and right click on Running totals and create a second running total field:
In the field to summarize I select the app number which by the way is an alphanumeric number.
In type of summary I place count
In the evaulate I place the following criteria
{VRPT_TABAPP.APPNO }startswith ["USA"]AND {VRPT_TABAPP.ARCHIVE}= ABANDONED

The numbers still do not calculate correctly. Any advice?