INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Running Totals

What are the different ways to do running totals? by MalcolmW
Posted: 15 Aug 00

There are several ways to do a running total.  Your options on which to use depend on your Seagate Crystal Reports (SCR) version number, and database type.

1) The easiest is to use the InsertªRunning Total wizard.  This is not always accurate in SCR v7 and below.  Easy, but use at your own risk.

2) Use the three formula approach.
This is reliable, but more time consuming to write.
In this example, the running total calculates the number of employees in a dept.

//goes in group header for dept, resets count to zero
WhilePrintingRecords ;
NumberVar DeptEmpCount := 0

//goes in the detail section, increments employee count
WhilePrintingRecords ;
NumberVar DeptEmpCount := DeptEmpCount + 1

//goes in the group footer for dept, shows dept emp count
WhilePrintingRecords ;
NumberVar DeptEmpCount

3) Lastly, writing the RT in SQL will give you the best performance, but requires some experience with SQL.  These running totals examples are for a bank account, where the running total is the balance of the account after each transaction.

SELECT B0.transaction, B0.transdate, SUM(B1.amount) AS balance
FROM BankAccount AS B0, BankAccount AS B1
WHERE B1.transdate <= B0.transdate

or using a scalar subquery (great terminology to confuse and intimidate)

SELECT B0.transaction, B0.transdate,
  (SELECT SUM(B1.amount)
   FROM BankAccount AS B1
   WHERE B1.transdate <= B0.transdate) AS balance
FROM BankAccount AS B0

Crystal gurus will note that both of these SQL solutions require modification of the SELECT portion of the SQL statement, which isn't permitted in Crystal Reports.  Thus, you will have to use a view, stored procedure, temporary table, or the SQL Query tool to circumvent this limitation.


Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close