# Pre Calculated Report Total Crystal Reports

I have a complex custom formula that calculates my row detail amounts. Please see my attached screenshot.

I need to be able to divide my row detail totals by the report total to get a percent value. (I.E. 837.50/1793.75 and 956.25/1793.75 ). The problem is that I don't know the report total until all of the row details have been added up(i'm using a custom currency variable to add them together because crystal isn't letting me SUM the row detail amounts I assume because its a complex custom formula).

There are many complex formulas and calcuations to get to the row detail values or I would just calculate that in the SQL stored procedure.

Any Thoughts on precalculating the report total prior to display the row details so I can use that to divide them? Thanks in advance.
total-example.png
###### Who is Participating?

Commented:
You could do that.

You use shared variables to pass information between reports.

Main Report
WhilePrintingRecords;
Shared NumberVar myTotal;
""

In the subreport to calculate the total add a formula to the report footer
WhilePrintingRecords;
Shared NumberVar myTotal;
myTotal := {RunningTotal};
""

In the subreport to use the total
EvaluateAfter ({TotalCalculation});
Shared NumberVar myTotal;
YourTotal / myTotal

mlmcc
0

Commented:
What is the formula for the row details?

mlmcc
0

Author Commented:
``````if {Command.facility} = "Facility 1" then
(tonumber({@TransAmount}) *({Command.fee_rate}/100)) * (tonumber({@NumberTransOfDays}) / 360)
else

if {Command.facility} = "Facility 2" then
(tonumber({@TransAmount}) *(0.375/100)) * (tonumber({@NumberTransOfDays}) / 360)
else

if {Command.facility} = "Facility 3" then
(tonumber({@TransAmount}) *(0.30/100)) * (tonumber({@NumberTransOfDays}) / 360)
``````

0

Commented:
What are the TransAmount and NumberTransDays formulas?

mlmcc
0

Author Commented:
NumberTransDays
``````if not NextIsNull({Command.action_dt}) then

if Next({Command.action_dt}) > {?EndDate} then
totext({?EndDate} - {Command.action_dt},"0")
else
(
if (Next({Command.action_dt}) - {Command.action_dt}) > 0 then
totext(Next({Command.action_dt}) - {Command.action_dt},"0")
else
"0"
)
else
"0"
``````

TransAmount

if {Command.facility} = "Facility 1" then
(

if({Command.action_dt} <= Cdate("05-02-2003")) then
153325235235
else if({Command.action_dt} >= Cdate("03-03-2001") and {Command.action_dt} <= Cdate("01-09-2006")) then
253325235235
else
{Command.total_limit_amount}

)
else if {Command.facility} = "Facility 2" then
(
if({Command.action_dt} <= Cdate("08-02-2003")) then
353325235235
else if({Command.action_dt} >= Cdate("01-03-2001") and {Command.action_dt} <= Cdate("06-14-2000")) then
353325235235
else
{Command.total_limit_amount}
)

else if {Command.facility} = "Facility 3" then
(
{Command.total_limit_amount}
)
0

Commented:
Since you are using Next and NextIsNull functions the formula is a printtime which means it can't be summarized.

One way to do this is to use a subreport to calculate the total.

mlmcc
0

Author Commented:
This report is already a subreport. Is it possible to create a another copy of this subreport in the main report to calc the total and pass to this subreport so that I can use the total in my calculations? If so can you provide an example.
0

Commented:
Stored procedure will do the calculations faster and easier (IMO) than Crystal. If you move the calculation there you will be able to add it to source control and control the changes, use the same calculations for another report etc. Although stored procedure is part of the data layer (not business layer) it still looks a better place to put the business logic than the report. You mentioned the stored proc as an option - for me this will be the right way . However if your report is not based on stored proc now , you will need almost to recreate it from scratch.
0

Author Commented:
how do I know that the subreport that is setting the value will be created prior to the one that uses the value?
0

Commented:
Make sure it is in a section before the section with the using subreport

If you need to
Right click the section with the using subreport in the left margin
Click INSERT SECTION BELOW

Left click the section in the left margin and drag it above
Insert the new subreport in the blank section

mlmcc
0

Author Commented:
Okay I will try this. Thank you.
0

Author Commented:
Do you have an example report? I'm not able to get this to work.
0

Author Commented:
I was able to get a total , however if I suppress the section the totaling subreport is in or I suppress the subreport itself. it doesn't calculate and the value is ZERO in the second subreport
0

Author Commented:
I  had to use conditional suppressing in the subreport fields to get the value to still be calculated.
0

Author Commented:
I  had to use conditional suppressing in the subreport fields to get the value to still be calculated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.