Solved

# Using subreports and calculating subtotals based off that data, need help.

Posted on 2007-10-18
372 Views
Using Crystal Reports Professional XI
I have a report that has the following structure:
Detail
Group Footer 2
Group Footer 1

On the detail is a call to a subreport, the subreport uses a running total to return a total invoice amount.  In my main report I'm trying to create Subtotal by Group Footer 2 and Grand Totals by Group Footer 1.

It seems like I just can't get my subtotals to work at all.  In the subreport I have created a formula called TotInvoice which looks like this:
shared numbervar TotalInvoiceSourceDetail := {#TotInvoiceDollars}

The data returns back into the main report on the detail line where i've placed the sub report correctly but when totalling in Group Footer 2 it seems like my data is out of sync and off by 1 record.

Can someone help me solve this issue?
0
Question by:bmickey

LVL 100

Expert Comment

Make sure you have a formula in the report header that declares the shared variable

shared numbervar TotalInvoiceSourceDetail;

Also be sure to declare the global variable for the totals,

mlmcc
0

Author Comment

Do you have declare the shared variable in both the subreport and main report?
0

LVL 100

Expert Comment

Yes.  It must be declared in the main report before you use it in the subreport.  That is why i generally declare them in the report header.

mlmcc
0

Author Comment

Ok, in my Subreport I have everything suppressed except the Report Footer A which has the following formula:
shared numbervar TotalInvoiceSourceDetail := {#TotInvoiceDollars}

It gets returned to the main report with the correct values.

At this point though, not sure what to check next.
0

Author Comment

What I mean is in the main report the information is returned correctly to the Detail layout in the main report.  I know in the Group Footer 2 I want to sum up the details, and then in group footer 1, want to sum up the various group footer 2's.

I do have a formula in both Group Header 1 and Group Header 2 to reset
Group Header 1 Formula = shared numbervar InvTotBySource := 0
Group Header 2 Formula = shared numbervar InvTotBySourceDetail := 0

I guess I need help writing the formulas that go into the Group Footer 1 and 2.

0

Author Comment

In Group Footer 2 I have the following formula that is suppressed and i'm trying to use to tally up the source details:
shared numbervar TotalInvoiceSourceDetail;
shared numbervar InvTotBySource := InvTotBySource + TotalInvoiceSourceDetail
0

LVL 100

Expert Comment

I would write that as

shared numbervar TotalInvoiceSourceDetail;
shared numbervar InvTotBySource;
InvTotBySource := InvTotBySource + TotalInvoiceSourceDetail

I am not sure it makes a difference or not.

If you unsuppress it does it work?

mlmcc
0

Author Comment

Ok, that gets me close for the Group Footer 2 calculation after making your suggested change.
However it's not working when I have multiple detail lines.
For example:
GH2 COLD CALL  10.00
Detail 1  10.00
GF2 COLD CALL SUBTOTAL 10.00 ( Worked Fine with only 1 detail line)

GH2 SELF GENERATED
Detail 1   5.00
Detail 2   7.00
GF2  SELF GENERATED Subtotal = 17.00  ???  Should be 12.00
0

LVL 100

Expert Comment

Put both resets in group header 2

mlmcc
0

Author Comment

Ok, I put both resets in GH2 and now I get:
GH2 COLD CALL  10.00
Detail 1  10.00
GF2 COLD CALL SUBTOTAL 10.00 ( Worked Fine with only 1 detail line)

GH2 SELF GENERATED
Detail 1   5.00
Detail 2   7.00
GF2  SELF GENERATED Subtotal = 7.00    Should be 12.00
0

LVL 100

Expert Comment

What are you grouping by in each group?

mlmcc
0

Author Comment

Both of these are text fields declared as NVarchar length of 50
0

LVL 100

Expert Comment

I am missing something.  It should work the way I suggested.

Can you upload the report to www.ee-stuff.com
Change the extension to txt

mlmcc
0

Author Comment

If I upload the report does it make it viewable to all or just you?
0

LVL 100

Expert Comment

It is available to all.  If there is no data with it that shouldn't be a problem

mlmcc
0

LVL 34

Expert Comment

Could there be additional details that the main report is suppressing or otherwise not showing, but the subreport is including?  When you said that you got a total of 17 instead of 12, if there was, for example, another detail line for 5.00 that the main report did not show, but the subreport included, that would explain it.

Just a thought.

James
0

Author Comment

0

LVL 100

Expert Comment

You need to do the totaling  in the detail section not the group footer.

mlmcc
0

Author Comment

I think I know what you mean but still am a little unsure.  Can you show me what totaling should be in detail and then how to display it in the 2 group footers?
0

LVL 100

Expert Comment

Just move the formula you are using in the group2 footer to the detail section.  You also need to add a formula to the footer to display the final value

mlmcc
0

Author Comment

Ok, by moving the @InvTotBySource formula to the Detail it aways displays the Revenue Since from the Previous record. It's like its 1 behind.  So each Detail record has the formula and the value is the Revenue Since from the Previous Detail Line.
0

LVL 100

Expert Comment

Try this
Right click the details section in the left margin
Click INSERT SECTION BELOW
Move the calculation to the new section

mlmcc
0

Author Comment

Sweet, after doing that the subtotal is working now for GF2 BUT
The Grand Total for GF1 is not working.
I'll give you an example of what its doing now.
GH1
GH2  ( Has reset in this section)
D -  4.00
GF2 Sub Total 4.00

GH2
D - 5.00
D-  5.00
GF2 - Sub Total 10.00
GF1 - Grand Total 10.00 (Not taking into consideration the first one for 4.00) - Should be 14.00
0

Author Comment

Ok I got it to work. I just had to put another accumulator out there to add up the invoices and then make it reset based off of when the GH1 changed!

I'd really like to know why adding the additional Detail Section fixed the one problem!!!

Thanks for all your help! A+++++++
0

LVL 100

Accepted Solution

It forced the formula to be evaluated after the subreport was run.

Crystal uses a strange algorithm on the order of execution within a section.  Basically it is positional top to bottom but it also depends on when the formula is added in comparison to the other objects.

mlmcc
0

Author Comment

Thanks again for all of your help. You truly deserve more than 500 points for this one!
0

## Featured Post

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â€¦
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater â€¦
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods youÂ´d like to investigate in more detail.  The methods are covered in more detail in oâ€¦