Running total - how to display at the beginning of a group?

[wrbodine]

Hi,

I have a running total that is computing the correct value and displaying it at the end of a group (these groups can last a few pages, and have the group header at the beginning of each page). I need to display the running total (with just the final value) in the group headers, from page one on. How do I go about this?????? I can't use a simple summary with "sum of the field" because the values I'm adding to get the running total are sometimes repeated in the detail....

Thanks,
Ray

 

[czarzecki]

It's funny that you should ask this -- I was just at the Washington Area Crystal Users Group yesterday -- Ken Hamady gave a presentation on this very topic. You might check out his website:

http://www.kenhamady.com.

I'm not sure if this is the technique he used to tell people how to do what you need to to, but give it a try. He calls it "the wormhole" On his website, it's detailed in The Crystal Reports Underground News -- June 2002. -- you can also get to it by going to

http://www.kenhamady.com/tips.html

 

[kenhamady]

czarzecki is close, but he has 2 different techniques mixed together.

The Wormhole technique will move a database field value or most formulas from the group/report footer to the group/report header. But this won't work with regular or running totals.

I also presented a technique that allows you to build a running total using values in an invisible Cross-tab. This might allow you to put your running total in the group header. Both are on the tips page mentioned above. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[chelseatech]

Another potential solution is to replace the Running Total with a Summary of a formula. Summary fields do display accurately in the Group header.

What is the calculation in the running total ? Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[wrbodine]

chelseatech:

the running total is like so:

Summary: field is a variable that just contains the field LoanBalance
Type of Summary is sum

Evaluate: on change of AcctNumber (we have to use this field because there can be multiple rows in detail for the same account number, but the LoanBalance will be the same in all of these, so we only want it once for each AcctNumber)

Reset: on change of Group (LoanOfficer) This is the group where we want to display the total in the header.

Thanks so much for your ideas!

 

[chelseatech]

Is "loanabalance" a field from the database or a "Variable" calculated with an evaluation time?

It looks like you are using the running total to add up the loan balances less frequently, because of the repeat values.

You can create a summary of the Loan Balance by using a Maximum function (that will get you just the highest value for each AcctNumber), but you can't sum this Max function. The Running total can only display accurately in the Group Footer because of when it is calculated in the processing cycle.

You should also group by AcctNumber and do the running total by change of group in case your AcctNumber goes back to a prior value, and messes up your calculations.

So the final answer to a long post is, that the running total has to be calculated in the footer.

There are two potential solutions:
1) Do something clever in SQL - create a view or stored Proc that creates the totals properly.
2) Use a subreport. If there isn't too much data, this might be reasonable. If there is a lot then it won't work.

Or of course, just keep your running total in the group footer. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[kenhamady]

Chelsea,

Or he can use a Cross-tab (instead of a subreport) in the Group Header to accumulate a running total. I just learned in December that you can do this and it would work perfectly in the "MAximum" situation that you described. Apparently this has been available since v7 and I never saw anyone use it. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[chelseatech]

Ken,

Running Totals in a Crosstab are only available in Cr9, and while I've had a look, the numbers make no sense whatever.

If someone has running totals working in a crosstab, then I'd like to know what they did.

Your crosstab suggestion won't work, becuase they will add up for every record, and the whole reason for using a crosstab is that we need the total be summed less often.

Bruce Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[kenhamady]

Bruce,

The technique is to increment a variable inside a conditional formatting formula in a cross-tab cell. This causes the formula to execute once for each instance of that cell in the cross-tab. You can't see the values in the cross-tab, but you can use them OUTSIDE the cross-tab. I actually discovered this technique in a KB article and wrote about it in my December newsletter. It works in versions all the way back to v7.

Read the following article and see what you think:

http://www.kenhamady.com/news0212.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com