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
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
ASKER
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)
What are the TransAmount and NumberTransDays formulas?
mlmcc
mlmcc
ASKER
NumberTransDays
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_amoun
)
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_amoun
)
else if {Command.facility} = "Facility 3" then
(
{Command.total_limit_amoun
)
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_amoun
)
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_amoun
)
else if {Command.facility} = "Facility 3" then
(
{Command.total_limit_amoun
)
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
One way to do this is to use a subreport to calculate the total.
mlmcc
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
how do I know that the subreport that is setting the value will be created prior to the one that uses the value?
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
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
ASKER
Okay I will try this. Thank you.
ASKER
Do you have an example report? I'm not able to get this to work.
ASKER
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
ASKER
I had to use conditional suppressing in the subreport fields to get the value to still be calculated.
ASKER
I had to use conditional suppressing in the subreport fields to get the value to still be calculated.
mlmcc