Solved

Adding a total of a formula field

Posted on 2003-11-06
13
353 Views
Last Modified: 2008-02-01
Hi people...
I got an issue at hand. The field @Invocie is a formula consisting of the following:

If Not IsNull({@From To Hours}) Then
$(ToNumber({SLA.Onsite_Rate}) * {@From To Hours})
Else $0.00

It displays the invoice total of each client. My boss wants a total revenue field displaying the SUM of all invoices. However, I can't do a running total on that sum (due to the formula?).
What's the best way to add the value of each result?

Thanks,

Gian
0
Comment
Question by:gian_the_man
  • 6
  • 2
  • 2
  • +2
13 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 9696282
Make your formula like this:

If Not IsNull({@From To Hours}) Then
   (ToNumber({SLA.Onsite_Rate}) * {@From To Hours})
Else
   0

This will give you a numeric result so you can add a total.  Use the field format to display the currency symbol instead of adding it to your result manually.

0
 

Author Comment

by:gian_the_man
ID: 9696653
I still get 0 as the total if I take out the $.
It doesn't even let me create a Running Total for that fomula...
0
 
LVL 42

Expert Comment

by:frodoman
ID: 9696731
Maybe this:

If Not IsNull({@From To Hours}) Then
   (ToNumber({SLA.Onsite_Rate}) * ToNumber({@From To Hours}))
Else
   0


Another possibility - will {SLA.Onsite_Rate} ever be null? Maybe need to add a check for that also?


Sorry, but that's all I can think of.  I've never not been able to create a running total so I suspect is has to do with non-numeric results.
0
 
LVL 6

Expert Comment

by:Ken Turner
ID: 9700428
I've just tried reproducing your problem - I re-created your original formula changing nothing except the variables, and made sure it was error-free.  I also confirmed that it produced a numneric rather than a text result.

I then successfully created a running total of this formula.

So what am I doing that's different?  What flavour of Crystal Reports are you using?  (I'm on CR8.0).   What happens when you attempt to create the running total?
0
 

Author Comment

by:gian_the_man
ID: 9716997
I'm running Crystal 8.0...
The problem I have is that I just can't see the formula when trying to create a Running Total field. It just won't let me do it...
I don't know why, but the formula @Invoice is not listed as an option to do a running total of it.

Gian
0
 
LVL 6

Expert Comment

by:Ken Turner
ID: 9720799
I suspect {@From To Hours}.  What does this do for its living?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:gian_the_man
ID: 9722374
Hello Ken,

You may be right... There is a chain of formulas that goes further. This is the @From To Hours formula:

If Not ISNull({@From To Total})
Then If (ToNumber({SLA.Calls Allotted}-{@From To Total}) >= 0)
Then 0
Else (ToNumber({@From To Total}-{SLA.Calls Allotted}))
Else 0.00

This is the From To Total formula:

NumberVar TotalSecs := {#From To Total Duration};
NumberVar TotalHours;
// NumberVar FracHours := Truncate(TotalSecs / 3600);

If (TotalSecs / 3600) < 1 Then
    TotalHours := 0
Else
    TotalHours := Truncate(TotalSecs / 3600);

NumberVar FracHours := (TotalSecs / 3600) - Truncate(TotalSecs / 3600);
NumberVar TotalMinutes := (FracHours * 60);

If TotalMinutes = 60 Then
    (
        TotalHours := TotalHours + 1;
        TotalMinutes := 0;
    );

ToNumber(Truncate (TotalSecs / 3600, 2));

I increased the points on this since it seems it'll take some more time to figure out.

Thanks for the help...

Gian
0
 
LVL 13

Expert Comment

by:EwaldL
ID: 9735599
The problem seems to be related with its evaluation time. As far as I know only formulas that are declared as
WhileReadingRecords; in the first line can be used in a running total.

In general, better avoid referencing one formula in the other. as this might lead to multiple execution of the same formula for the same section. Rather have the formula store a result in a shared variable. you can then reference this variable in another formula without having the multiple execution of formula which is bad for performance and could also bring back unwanted results. Then use the evaluateafter() function at the top of the second (and of any more formulas) to specify the sequence the formulas are to be evaluated in.
0
 

Accepted Solution

by:
isamu_2000 earned 200 total points
ID: 9736514
This is an easy thing to solve.
First, in your formula, you don't want the '$' sign.  take that out and put that in a text box where you put $ and drop your invoice formula number field into it.

Then, create a formula called 'SumInvoices0'

In that you will put
'
WhilePrintingRecords;
global numbervar SumInvoices:= 0;
'
Place this in the report header.  I am assuming you want the invoice total at the report footer.
Then make another formula 'SumInvoices1'
'
WhilePrintingRecords;
global numbervar SumInvoices;
SumInvoices := SumInvoices + {@From To Hours}
'

Create a section below where you are listing {@From To Hours} and drop SumInvoices1 in there.  Suppress it.

Now, create one more formula 'SumInvoices2'
'
WhilePrintingRecords;
global numbervar SumInvoices;
SumInvoices := SumInvoices;
'

Place this in your report footer.  It will now display a grand total of all your subtotaled invoice {@From To Hours}.

isamu
0
 

Author Comment

by:gian_the_man
ID: 9743080
It made sense what you said, but it still returned a value of 0.
@FromToHours does have values in it, but SumInvoices1 and 2 showed 0.

Thanks,

Gian
0
 

Expert Comment

by:isamu_2000
ID: 9743208
Hmm ... where are you putting the formulas?  The first one should go in the header somewhere, the second should go in the same section that you are subtotaling your @FromToHours to get an individual invoice.  The last one should go in the footer.

I've used this technique several times and have never had too much trouble with it.

Maybe somebody else can see an error I might be making in explaining it?
0
 

Author Comment

by:gian_the_man
ID: 9770695
What if there is a group and a details section on it?
0
 

Author Comment

by:gian_the_man
ID: 9772256
It did work... I played around withthe three fields and it worked.

Thanks a lot Isamu.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now