CrossTab on large database not calculating correctly

[apelecanus]

Hi,

I have a 600k record foxpro 2.5 (.dbf) database with roughly the following structure

job number
product
coverage
amount

My cross tab rows are based on product + coverage, columns are sums of amount based on job number.

the product/coverage group totals are correct, but the individual subtotals (per coverage are not) For example

job 1 job 2
product1
coveragea 100 should be zero 100 correct
coverageb 200 correct 200 correct
coveragec 300 correct 300 correct
total 600 correct 600 correct

I have verified in the database that for product1 coveragea the total really is zero. There are approximately 90,000 records that are in job 1.

Any ideas why crystal is finding numbers that aren't there?

Thanks to all

 

[apelecanus]

... oops! by the way I am doing the above with CR8

 

[kenhamady]

Crystal doesn't usually make up numbers. What I would do is select only records that are Job1, CoverageA, Product1, and look at the details to see where the number is coming from.

According to your example, the total for column 1 should be 600, but the subtotals are 0,200 and 300. That doesn't add up to 600. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[apelecanus]

I checked the totals for job one in the database with sql and in CR8 and they are correct. And cr is still showing erroneour numbers... you're right my example should have read


job 1 job 2
product1
coveragea 100 should be zero 100 correct
coverageb 200 correct 200 correct
coveragec 400 correct 300 correct
total 600 correct 600 correct

so I'm left thinking that CR is making up the number?!

Thanks though!

 

[kenhamady]

You missed my point. Crystal is somehow getting these vales from the recordset it gets. You need to look at the recordset that Crystal is using. You can only do that within Crystal Reports.

Change the select expert to only include records that are Job1, CoverageA, Product1. Then put the summarized field from the cross-tab also onto the detail band and make the detail band visible. Then preview and check the details to see which records Crystal is using to generate this value. You can sort the details on this field to bring the problem records to the top or bottom of the details. This should tell you where the problem is coming from.

BTW, what is the summarized field, and what operation are you using in the Cross-tab

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[apelecanus]

Thanks for your help.

I violated the first rule "know thy data"... and there was actually a category that only two records fell into that the cross tab didn't have a case for. These two accounted for the stray numbers.

Thanks again.