?
Solved

Subtotaling Values From a Subreport Based on a Group

Posted on 2011-05-04
10
Medium Priority
?
415 Views
Last Modified: 2012-05-11
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
Comment
Question by:Clif
  • 4
  • 4
  • 2
10 Comments
 
LVL 10

Author Comment

by:Clif
ID: 35693036
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

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.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35693562
Use a formula

WhilePrintingRecords;
Shared NumberVar LE4000Value;
Shared NumberVar PB4000Value;

LE4000Value + PB4000Value

mlmcc
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 35693577
If you need to get totals for the group then

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

GroupSalesTotal := GroupSalesTotal  + LE4000Value + PB4000Value;

In the group header
WhilePrintingRecords;
Global NumberVar GroupSalesTotal;
GroupSalesTotal := 0;


In the group footer
WhilePrintingRecords;
Global NumberVar GroupSalesTotal;
GroupSalesTotal

mlmcc

0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 10

Author Comment

by:Clif
ID: 35693810
Does the total have to go in the group footer?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35694172
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 35

Expert Comment

by:James0628
ID: 35697023
> 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 10

Author Comment

by:Clif
ID: 35697540
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 101

Expert Comment

by:mlmcc
ID: 35699804
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
0
 
LVL 10

Author Closing Comment

by:Clif
ID: 35700007
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.
0
 
LVL 35

Expert Comment

by:James0628
ID: 35705059
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Integration Management Part 2
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question