[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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

Using Crystal Reports Professional XI
I have a report that has the following structure:
Group Header 1
Group Header 2
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
bmickey
Asked:
bmickey
  • 14
  • 11
1 Solution
 
mlmccCommented:
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
 
bmickeyAuthor Commented:
Do you have declare the shared variable in both the subreport and main report?
0
 
mlmccCommented:
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
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.

 
bmickeyAuthor Commented:
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
 
bmickeyAuthor Commented:
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
 
bmickeyAuthor Commented:
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
 
mlmccCommented:
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
 
bmickeyAuthor Commented:
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
 
mlmccCommented:
Put both resets in group header 2

mlmcc
0
 
bmickeyAuthor Commented:
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
 
mlmccCommented:
What are you grouping by in each group?

mlmcc
0
 
bmickeyAuthor Commented:
GroupHeader #1: LeadSource.Listname - A
GroupHeader #2: LeadSourceDesc.Listname - A

Both of these are text fields declared as NVarchar length of 50
0
 
mlmccCommented:
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
 
bmickeyAuthor Commented:
If I upload the report does it make it viewable to all or just you?
0
 
mlmccCommented:
It is available to all.  If there is no data with it that shouldn't be a problem

mlmcc
0
 
James0628Commented:
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
 
bmickeyAuthor Commented:
Ok, I've uploaded the file
0
 
mlmccCommented:
You need to do the totaling  in the detail section not the group footer.

mlmcc
0
 
bmickeyAuthor Commented:
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
 
mlmccCommented:
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
 
bmickeyAuthor Commented:
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
 
mlmccCommented:
Try this
Right click the details section in the left margin
Click INSERT SECTION BELOW
Move the calculation to the new section

mlmcc
0
 
bmickeyAuthor Commented:
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
 
bmickeyAuthor Commented:
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
 
mlmccCommented:
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
 
bmickeyAuthor Commented:
Thanks again for all of your help. You truly deserve more than 500 points for this one!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 14
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now