Std Dev and subreports

[lyanch]

I am creating a report that has data from two separate databases so half of a detail line comes from one DB and half from another. I am calculating the difference between one number in the main report and one in the subreport, for around 200,000 records to give some scope feel. I can calculate the difference, the average difference, the max and min difference but they of course need more, anyone have any suggestions on how to calc the mode, mean and std dev ?

I think this is going to end up being a data dump and load between the databases (which means I have to do it - and the user can't), which is a shame, but I don't see any way to get those numbers from within crystal.

Lisa

 

[synapsevampire]

By 2 different databases, do you mean 2 different types of connections?

If Stored Procedures are an option, you might combine the data sets in there and perform whatever calcs are required.

Another option is to use an Access database and Link (not import) the data in a query and then link the report to the single data source.

Mode, Mean and Standard deviation would then be relatively simple.

Calculating them in a main report with some values being returned from a subreport might be handled by collecting each value from the main and subreport into arrays, but that will get quite complex and is of course Crystal version dependent.

-k

 

[lyanch]

Stored prcoedures are not an option. I mean completely different databases (Sybase and Oracle in this particular instance).

Crystal Arrays cannot handle the number of records (1000 is tops in Crystal 9) unless I make a huge number of arrays. (Ponders a Crystal report with over 200 arrays... eeek!) This is probably doable but I could probably dump and run this report a 100 times before I would use up the time to develop that behoemeth and by that time they probably won't care about this set of numbers any more

I had thought of using access as an itermediary between these databases before, unfortunately performance was absolutely ghastly (a simple join bringing back a few records took over an hour) unless I did an import instead of a link (one of the main tables is at 4 mil records and growing daily).

At least in this instance I have a very easy to please user. He just wants his numbers .. doesn't care how or how it looks.

Thanks for the response

Lisa

 

[synapsevampire]

Bizarre that performance is that bad, I'm going across a 160 million row database (12 tables) in Oracle and a 2 million row (single table) in DB2 and it takes less than 2 hours.

Perhaps you can build some queries to reduce the rows returned by each database first, and then build a query based on those queries to return the final numbers.

A few rows in an hour....ouch...

-k