Is there some way of recycling a Crystal Reports dataset?

Question

I'm trying to write a Crystal Report which has totals grouped in a different way to the main report. The only way I've been able to do this so far is to use a subreport for the totals, but it means having to hit the data source again to retrieve the same data, which seems like nonsense. Here's a simplified example:

       date   name   earnings   source          location
-----------------------------------------------------------
12-AUG-2008   Tom      $50.00   washing cars    uptown
12-AUG-2008   Dick    $100.00   washing cars    downtown     { main report }
12-AUG-2008   Harry    $75.00   mowing lawns    around town

                    total earnings for washing cars: $150.00 { subreport }
                    total earnings for mowing lawns:  $75.00

       date   name   earnings   source          location
-----------------------------------------------------------
13-AUG-2008   John     $95.00   dog walking     downtown
13-AUG-2008   Jane    $105.00   washing cars    around town  { main report }
13-AUG-2008   Dave     $65.00   mowing lawns    around town

                    total earnings for dog walking:   $95.00
                    total earnings for washing cars: $105.00 { subreport }
                    total earnings for mowing lawns:  $65.00

In this example, the main report is grouped by 'date', but the totals are grouped additionally by 'source'. I've looked up examples of using running totals, but they don't really do what I need. Isn't there some way of storing the result set and having both the main report and the subreport reference the same data?

Answer 1

Hmm... as nice as it is to call the stored proc from the report and have it all contained in one location, however we found (like you) that you eventually hit a point where you can't get crystal to do what you want even tho the data is right there.

We ended up introducing a business layer which sits under the report and rather than "pulling" data from the report we "push" the datasets to it and bind the data to the report. The advantage is that you can manipulate the data in code in datasets or objects before it reaches the report and then simply bind the data to the report.

This article has a nice intro on how to setup pushing data to the reports. I understand that your time/business constraints may not allow you to do this, but if it's at all possible, I'd highly recommend it as it's meant we can remove all "coding" out of our reports and into managed code which is always a good thing.