Print Page | Close Window

Subreport total to main report

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19360
Printed Date: 27 Apr 2024 at 4:11am


Topic: Subreport total to main report
Posted By: south
Subject: Subreport total to main report
Date Posted: 03 Apr 2013 at 6:56am
I have a subreport that has "total price" as one of the fields listed (which uses a formular to come up with the "total price").  I tried to get the grand total of the "total price" but cannot seem to get it to work.  It just list the "total price" under each of the records that it already lists.  What I was tring to accomplish is to have it give me a total then have that grand total print on the main report.  But I can't even get it give me a grand total in the subreport. 



Replies:
Posted By: hilfy
Date Posted: 03 Apr 2013 at 7:33am

How are you trying to get the Total Price in the subreport?  Here's something you can try for getting the information to the main report:

1.  Create two formulas that will each initialize a variable.  I'll call these mailto:%7b@GrandTotalInit - {@GrandTotalInit } and mailto:%7b@GroupTotalInit - {@GroupTotalInit }.

    mailto:%7b@GrandTotalInit - {@GrandTotalInit }
    Shared Numbervar GrandTotal := 0;
   
    mailto:%7b@GroupTotalInit - {@GroupTotalInit }
    Global Numbervar GroupTotal := 0;


Note the ":=" - You need to use this syntax to assign a value to a variable.  Also, but putting the ";" at the end, this value won't actually display on the report, but the formula will evaluate to set the initial value.

"Shared" means the variable will only be available in the main report, "Global" means it's available in both the main report and the sub-report.

2.  Place mailto:%7b@GrandTotalInit - {@GrandTotalInit } in a report header section in the main report.

3.  Assuming that you're grouping data in the main report, put this formula in a group header section above the section where the subreport runs but at the same group level.  This will set the variable to 0 at the start of each group.

4.  In the subreport, create a formula that will be used to sum the total price.  I'll call this mailto:%7b@TotalPrice - {@TotalPrice }:

    Global Numbervar GroupTotal;
    GroupTotal := sum({myTable.TotalPrice});
    GrandTotal 
   
Note that the last line has no ";" on the end - this will display the value of the variable.

5.  Put this formula in a report footer of your subreport.  This will then display at the end of the subreport.

6.  In the main report, create another formula that will sum the values returned from the subreports.  I'll call this mailto:%7b@TotalAdd - {@TotalAdd }:

    Global Numbervar GroupTotal;
    Shared Numbervar GrandTotal;
    GrandTotal := GrandTotal + GroupTotal;
   
7.  Place this variable in a group footer section below the subreport and at the same group level as the subreport.

8.  Create a final formula which I'll call mailto:%7b@GrandTotalShow - {@GrandTotalShow }:
   
    Shared Numbervar GrandTotal;
    GrandTotal
   
9.  Place this formula in either a report footer section or, if you have multiple groups in the main report, the footer section of a group that is outside of the group where the subreport is located.

-Dell   



-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: south
Date Posted: 04 Apr 2013 at 2:41am
Thank you for the detailed description!!!  I am still having problems with the grand total.  I have added grand totals & running totals on other reports and have not had a problem, but this report won't give me a a grand total in the report footer.  The "total price" is in the detail section of the report and when I add a formula to sum the "total price" and put it in the report footer what I get is the "total price" by line item and then that same amount under each of the line items not a grand total at the end of the report.  Any suggestions?


Posted By: hilfy
Date Posted: 04 Apr 2013 at 3:13am
So, if I understand you correctly, you're getting the same total price value for every detail record in the subreport and for the grand total as well.  Is that correct?
 
I would take a look at the field type of the total price field - it's possible that it is a string instead of a number.  In which case you'll need to convert it to a number in order to sum it.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: south
Date Posted: 04 Apr 2013 at 3:37am
Yes it's correct, this is an example of what my report looks like now:
line     total price
1             50.00
 
2             25.00
 
3             25.00
In the report footer i added the sum and was expecting to get 100.00 instead I get:
1             50.00
                    50.00
2             25.00
                    25.00
3             25.00
                    25.00
No grand total in the footer.


Posted By: hilfy
Date Posted: 04 Apr 2013 at 4:07am

If each detail line is a subreport, you won't get your grand total by putting it in the subreport footer - it has to go in the main report footer. 

 
In step 4 above, change the formula to:
 
 Global Numbervar GroupTotal;
GroupTotal := GroupTotal + {myTable.TotalPrice};
 
Place this in the same section in the subreport where your data is located and suppress the report footer section in the subreport.
 
Put the mailto:%7b@GrandTotalShow - {@GrandTotalShow } formula in the report footer or an outside group section in the main report.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: south
Date Posted: 04 Apr 2013 at 5:33am

I get an error that says the "GroupTotal+{mytable.TotalPrice}" needs to be a number.  {mytable.TotalPrice} is currency, do I have to first convert it to a number??



Posted By: hilfy
Date Posted: 04 Apr 2013 at 6:22am
That or change GroupTotal and GrandTotal from NumberVar to CurrencyVar in all of the formulas.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: south
Date Posted: 04 Apr 2013 at 9:13am

The subreport is showing the running total but the main report is showing 0.  I went back and checked all the instructions that you gave me and they were all followed and typed correctly.  The only thing I did diffrently is where I put some of the formulas.  I do not have any groups in the main report.  I put GrandTotalInit and GroupTotalInit in the report header section and i put TotalAddin in detail section C - the section under my where my subreport is.  And GrandTotalShow is in the report footer. 



Posted By: hilfy
Date Posted: 04 Apr 2013 at 9:43am
TotalAdd needs to be in a section AFTER the subreport - the value is not available until after the subreport runs.  If you don't have a detail section D, I would create one, put this formula in it, and then suppress it.  The calculation will still run but the section won't take up any space.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: south
Date Posted: 04 Apr 2013 at 10:13am
The subreport is in the detail section B and TotalAdd is in the detail section C. 


Posted By: hilfy
Date Posted: 04 Apr 2013 at 10:44am
Did you change ALL of the NumberVars to CurrencyVars or are you converting the currency to numbers?
 
If I pm you my email address, can you send me a copy of the report where you've turned on Save Data on the file menu?  That way I can take a look at it and either fix it or make suggestions for fixing it.  What version of Crystal are you using?
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: Beso90
Date Posted: 18 Jul 2018 at 7:10am
Hello hilfy,

I followed your steps and I have a question and a problem:

So I placed the GrandTotalInit in the group header where the subreport runs. Where do I place the GroupTotalInit?


On step number 4:

Global Numbervar GroupTotal;
    GroupTotal := sum({myTable.TotalPrice});
    GrandTotal

When I put that in the formula, it tells me "the remaining text does not appear to be part of the formula", and then highlights the "GrandTotal" in the last line. How do I fix this?


Posted By: Beso90
Date Posted: 18 Jul 2018 at 7:12am
I have:
Crystal Reports 2013 Support Pack 1
Version 14.1.1.1036


Posted By: kevlray
Date Posted: 18 Jul 2018 at 7:51am
FYI: on the formula 
Global Numbervar GroupTotal;
    GroupTotal := sum({myTable.TotalPrice});
    GrandTotal

GrandTotal is not defined.  Also I thought I saw on a previous post that Hiffy wanted you to suppress either the section or the sub-report.  Either way if you suppress the section or the sub-report.  The sub-report will not execute.  There are ways around this issue if you do not want to see the sub-report.



Posted By: PBSELDEN
Date Posted: 25 Jun 2019 at 12:49am
Please tell me what the ways are around an inability to suppress a subreport. I am bringing back totals to the main report and that is all I want to show, but in doing so I bring back the space required for the subreport. I have tried shrinking down the font size and reducing the subreport to a mere line, but in my grouping on my main report I still have these blocks of lines.


Posted By: kevlray
Date Posted: 25 Jun 2019 at 4:54am
There is a option to suppress a blank sub-report (Format Subreport, Subreport tab, Supprss Blank Subreport),  then in the section expert where the sub-report is, Suppress Blank Section.



Print Page | Close Window