[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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
0
skobyjay81
Asked:
skobyjay81
  • 9
  • 5
1 Solution
 
mlmccCommented:
What is the formula for the row details?

mlmcc
0
 
skobyjay81Author 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)

Open in new window


0
 
mlmccCommented:
What are the TransAmount and NumberTransDays formulas?

mlmcc
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
skobyjay81Author 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"

Open in new window


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
 
mlmccCommented:
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
 
skobyjay81Author 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
 
vastoCommented:
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
 
mlmccCommented:
You could do that.

You use shared variables to pass information between reports.

Main Report
In the report header add a formula
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
 
skobyjay81Author 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
 
mlmccCommented:
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
 
skobyjay81Author Commented:
Okay I will try this. Thank you.
0
 
skobyjay81Author Commented:
Do you have an example report? I'm not able to get this to work.
0
 
skobyjay81Author 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
 
skobyjay81Author Commented:
I  had to use conditional suppressing in the subreport fields to get the value to still be calculated.
0
 
skobyjay81Author Commented:
I  had to use conditional suppressing in the subreport fields to get the value to still be calculated.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now