Problem with Crystal Reports

Question

Hi

I have a problem with Sum fields in Crystal Reports, under VS 2003. I am using VS 2003 and Crystal Reports to generate a report with data retrieved from a SQL Server 2000 database and kept during runtime inside a strongly typed dataset.

The dataset contains several tables, one of which, the orders table, receives the results of a SQL query, that joins records from two different tables. After checking the contents of the orders table during runtime - just before passing the dataset to the Report object, I have verified that the SQL query in question is working correctly, always returning the expected data from the database.

The dataset is iterated over, each iteration merging the results of the SQL query, executed against a different orderID, into the dataset. The SQL query also retrieves records from other tables related to each orderID, and puts them into the appropriate tables (other than orders) in the dataset.

The problem appears when I try to sum a particular column, the orders.order_amount column, onto a Crystal Report. The sum I get is incorrect.

For example, the orders table contains 3 records at the time it is bound as the report's datasource, each with an order_amount = $10.00. Instead of returning $30.00, the value of the sum field is something like $50.00.

I can't see how this could happen, I assume that this is either due to a bug in Crystal Reports, or a result of a missing item from my report. The dataset is correctly populated, but it appears that Crystal Reports somehow confuses the related records retrieved based on the orderIDs as being records of the orders table, and adds to the order_amount sum.

If I disable the SELECT statement that populates the related records, the order_amount sum is shown correctly.

Anybody got any idea as to why this happens? Thanks in advance.

Ray

Answer 1

Best guess is that the 'related records' you mention which you're disabling, is causing order_amount to appear several times as a result of parent-child relationships with your query. Can the Select statement that you're enabling/disabling cause a return of 5 rows instead of 3? If two of the 3 rows have 2 related records instead of one Crystal would read those as 5 rows and sum accordingly.

Answer 2

Hi Theo

Thanks for your response. The select statement responsible for obtaining the related records does indeed return more rows than the number of records inside the orders table, i.e. 3 orders records are associated with 5 more records from another table. However, those records are persisted in another table within the dataset. Shouldn't the behaviour you're describing in your answer be considered a bug?

You see, CR is not doing what I'm asking it to do, which is simply to sum the order__amount values of the records found under dataset.orders. What CR is doing instead is sum the order__amount values of the 3 desired records, plus amount values from the related records which are not even found inside the orders table.

In the summary field definition, I clearly specify that the sum should come from the orders.order_amount field, but clearly the result of the sum operation is incorrect. By disabling the query that retrieves the related records, the result appears correctly. Any ideas?

Cheers.

Answer 3

Hi Theo Thanks again for your reply. I solved the problem by adding some CR code at the report level to check the orders data and update some shared variables with the order amount totals, as well as some other sums relevant to the report.

I still don't understand why CR adds the orders table records with related records from other tables from the dataset, when I clearly specify that all I want is the records from the orders table. It seems that CR does not do what is logically implied by the Sum operation, which is Sum(orders.order_amount). Anyway, problem solved. Thanks for all your help. Ray