Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Subreport total to main report Post Reply Post New Topic
Page  of 2 Next >>
Author Message
south
Groupie
Groupie


Joined: 31 Jul 2012
Online Status: Offline
Posts: 82
Quote south Replybullet Topic: Subreport total to main report
    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. 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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 {@GrandTotalInit} and {@GroupTotalInit}.

    {@GrandTotalInit}
    Shared Numbervar GrandTotal := 0;
   
    {@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 {@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 {@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 {@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 {@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   

IP IP Logged
south
Groupie
Groupie


Joined: 31 Jul 2012
Online Status: Offline
Posts: 82
Quote south Replybullet 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?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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
IP IP Logged
south
Groupie
Groupie


Joined: 31 Jul 2012
Online Status: Offline
Posts: 82
Quote south Replybullet 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.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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 {@GrandTotalShow} formula in the report footer or an outside group section in the main report.
 
-Dell
IP IP Logged
south
Groupie
Groupie


Joined: 31 Jul 2012
Online Status: Offline
Posts: 82
Quote south Replybullet 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??

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 04 Apr 2013 at 6:22am
That or change GroupTotal and GrandTotal from NumberVar to CurrencyVar in all of the formulas.
 
-Dell
IP IP Logged
south
Groupie
Groupie


Joined: 31 Jul 2012
Online Status: Offline
Posts: 82
Quote south Replybullet 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. 

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.