Fetching totals on the report

[crazylad]

Hi

I am using version 8.5 and I am using ODBC drivers to connect to my AS400 database.

Problem Statement - I have 2 million records which meet the criteria for my report. But I need to display only 5-6,000 records on the screen (One record per employee).

The records are as follows -

Emp Id Check Amount
12021 $489.00
12527 $1681.00
12021 $4.50
12617 $500.00
12617 $4327.00
12021 $400.00

The report should look like
12021 $893.50
12527 $1681.00
12617 $500.00


It is quite evident that we have multiple records for each employee.
On report I need to display the total check amount against an employee, therefore from database I do not want to fetch the complete 2 million records. Rather I wish to fetch totals directly.

As I am not using queries or stored procedures, I do not know how can I do this ??
Please help me out on this issue

Thanks in advance
Crazy

 

[GMcNamara]

It appears you have two options. First is to do the aggregation on the server and then return a single record for each employee with the total check amount. You said you are not using stored procedures but I don't know if that means you can't or you just don't know how.

The second option is to return all of the records and allow Crystal to do the aggregating. This can be done relatively easily by creating a group on the employee and then inserting a sum on the check amount for each group. This will be rather slow though.

If it is at all possible, I would recommend option 1.

 

[crazylad]

Option 1 is ruled out because as a design guideline, I am not using a stored proc.

Second option is very slow and that is the reason of concern. Using the second option, how can I maximize the performance? can't I select the sum of Amounts for an employee from the database directly?