Ok, I have been using Crystal for 5+ years and I am currently using v10. I am constantly making adhoc reports but I also publish to an Enterprise server. Approx 25% of the reports I post to the server deal with historical trends and there are upwards of 2 million records to querry to gather the data for just 1 year. My problem is it can take hours to querry just 1 year worth of data which eats up system resources not only on the Enterprise server but the db server.

I need a solution to compile the historical metrics and auto populate a new monthly db with the data. At the beginning of every month I need a automatic querry to run to compile the last full month and auto populate the new monthly db. I would then need to join the 2 tables and have crystal only pull month to date data, allow for drilldown for analytical purposes, and show historical trends.

I am not sure how clear I made this however I believe you should get the gist. I just need a simple solution which I have been wracking my brain over for months.