Subtotaling Values From a Subreport Based on a Group
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
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
Use a formula
WhilePrintingRecords;
Shared NumberVar LE4000Value;
Shared NumberVar PB4000Value;
LE4000Value + PB4000Value
mlmcc
WhilePrintingRecords;
Shared NumberVar LE4000Value;
Shared NumberVar PB4000Value;
LE4000Value + PB4000Value
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Does the total have to go in the group footer?
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
mlmcc
> 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
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
ASKER
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.
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.
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.
One idea I just had.
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
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.
One idea I just had.
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
ASKER
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.
Thanks for your help.
Thanks for your help.
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
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
ASKER
Open in new window
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.