Solved

# Subtotaling Values From a Subreport Based on a Group

Posted on 2011-05-04
363 Views
This is the second part to a problem begun HERE

I have a report which, due to the unusual nature of the report, pulls data from a couple of subreports in the detail line.  I finally got the correct data to display, but I need to subtotal the values based on the grouping in the main report.  I tried summing the formula field that displays the data from the subreport, but Crystal tells me that the field cannot be summarized.

How can I do this?

TIA
0
Question by:Clif

LVL 11

Author Comment

While I'm here, I suppose I should show you an example of the report:
``````    Comp Cd  Job Code    Cost Ctr  LE 4000   LE 2205   PB 2205   PB 4000
California - Total Sales = \$6500.00
ITC      1101058XX1  ABC       0.00      0.00      -1500.00  1500.00
ITC      1102049XX1  ABC       0.00      5000.00   -5000.00  5000.00
Nevada - Total Sales = \$6912.00
ITC      1101058XX1  ABC       0.00      0.00      -1234.00  1234.00
ITC      1102049XX1  ABC       0.00      5678.00   -5678.00  5678.00
``````
Total Sales (in the cases above) = LE 4000 + PB 4000  (so far LE 2205 and PB 2205 are just displayed not summed)

LE 4000, LE 2205, PB 2205 and PB 4000 are all values that come through shared variables from subreports.
0

LVL 100

Expert Comment

Use a formula

WhilePrintingRecords;
Shared NumberVar LE4000Value;
Shared NumberVar PB4000Value;

LE4000Value + PB4000Value

mlmcc
0

LVL 100

Accepted Solution

If you need to get totals for the group then

WhilePrintingRecords;
Shared NumberVar LE4000Value;
Shared NumberVar PB4000Value;
Global NumberVar GroupSalesTotal;

GroupSalesTotal := GroupSalesTotal  + LE4000Value + PB4000Value;

WhilePrintingRecords;
Global NumberVar GroupSalesTotal;
GroupSalesTotal := 0;

In the group footer
WhilePrintingRecords;
Global NumberVar GroupSalesTotal;
GroupSalesTotal

mlmcc

0

LVL 11

Author Comment

Does the total have to go in the group footer?
0

LVL 100

Expert Comment

If you want group totals.  If all you need is a grand total then no.  Dont reset to 0 and just put it in the report footer.

mlmcc
0

LVL 34

Expert Comment

> Does the total have to go in the group footer?

Basically, no, although the real answer depends on what total you're referring to.  You said "subtotal the values based on the grouping", which implies a group total.

You can't put the group total in the group header, if that's what you wanted, because the variables will be set as the records are read in the main report and the subreports are run.  They won't have the final totals for the group until the last records in the group have been read.

If you're thinking of listing a total for each group at the end of the report, you can do that, but you need to save each group total separately, probably using arrays.

James
0

LVL 11

Author Comment

mlmcc,
What I meant was, does the group total have to go in the group footer?  Our report design so far has been putting the group totals in the group header so that they can be reviewed first as the number of records in a group could span several pages.

In the same vein, we also have been putting grand totals in the page header.  In the process above, will that still be possible?

James0628,
Apparently, from what you say, the answer is no.
0

LVL 100

Expert Comment

If you use the method outlined aboe, the total is calculated inthe final pass through the report and isn't available until the group or report is complete.

You are correct using that method the answer is no.

One method that can work is to insert a copy of the report into its report header and get the totals but
since your report uses subreports you can't use it as a subreport to get the totals before running the report.

I think you may have to provide the total in the footer.

How volatile is the data?
I was thinking you could
Modify the report to have a parameter that is the total
Run the report, get the total then run it again and pass the total in

mlmcc
0

LVL 11

Author Closing Comment

I guess, in this instance, they will have to live with the totals being at the bottom.  So far, with this report, I've done three impossible things.  They can be happy without the fourth.

0

LVL 34

Expert Comment

Yeah, you're pretty much stuck with totals in the footers.  The variables won't have the totals until you get to the footers (and you can't use CR's built-in summary functions on variables).  And you have to use variables because you're getting the values from subreports.  And because you're using subreports, you can't use the current report as a subreport to produce the totals.

The only thing that I can think of would be if you could create a version of your current subreport that would run for a whole group at a time.  I think it's being run for each detail record now?  If you could create a version of that subreport that could be passed a group value (department or company or whatever you're grouping by) and would read all of the records in that group (instead of one record at a time), produce a total for the group and put that in a variable, that could work.  You'd put that subreport in a group header section to produce the group total.  Of course it means that you'd be reading the subreport records twice (once for each detail record in the original subreport, and once for the whole group in the new subreport), so if the subreports are reading a lot of records, that could really slow the report down.  But, that aside, it could work.

James
0

## Featured Post

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…