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

The only way I can think of doing this without a second run through the data would be by creating some formulas to do running totals per group. The problem I assume you are running into with the existing running totals is that they are intended to follow each of the groups that they are totaling. Since you seem to want the subtotals to follow after all of the 'raw' data this won't work.

If you create your own formulas for each group that simply adds on the total from those rows matching the group you should be able to place them at the end of the report. The downside to this approach is that the resulting subtotals will not be dynamic in relationship to the groups. In other words if you had a new 'source' it would not show up in the subtotals until you added it or if you had no 'dog walking' data you would still have a subtotal for it.

Answer 2

Can I just ask a couple of questions so I might be able to come up with an alternate solution... are you accessing this report from within an ASP.NET application?

If so, does the report make the call on the database directly and "pull" the data or does it have data "pushed" to if from an underlying business layer?

Answer 3

@lomaxx yep, it's accessed through a ASP.NET application and the report has a database stored procedure as its datasource. Currently, I have this stored procedure processing the data and writing the results to a table (which the subreport can use) as well as returning the result set, that way I don't have to process the data twice, just read it twice. I hope that makes sense!

@N8g you've hit the nail on the head here:

The problem I assume you are running into with the existing running totals is that they are intended to follow each of the groups that they are totaling. Since you seem to want the subtotals to follow after all of the 'raw' data this won't work.

The problem with writing my own formulae is, as you pointed out, the source list is dynamic and could be different every time the report is run; hence the use of a subreport.

Answer 4

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.