Crystal Reports - Grouping on a Running Total

I have a running total that only updates (counts) on a certain condition. So I end up with several records where the running total is 1, several records with a running total of 2 and so on.

Now I want to group on that running total so I can print a summary of the 1 records, a summary of the 2 records, etc.  However, the running total field is not available to group by.

Is there a way to do this? PS: I don't have access to the database to put the number there, it must be done in Crystal.
LVL 1
loneieagle2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
No, you cannot group on running totals.

Crystal uses a multiple pass method to render the reports.  Groups and sorting is done in a pass before running totals are calculated.  Running totals are calculated in the last pass through the report.

What is the running total doing?

Could a summary be used?

mlmcc
0
loneieagle2Author Commented:
The data is a table with one record for a charge and then multiple records for payments and adjustments. Each transaction now prints on a separate line. I want to print all the information for one transaction on one line with the charge in one column, adjustment in another column, etc. The only way I know that I need to print a summary is the transaction type is a 1 (charge).
The relevant part of the table has:

Type   Charge  ADJ   Payment  ...
 1         10.00      0            0
 8            0         2.00        0
 8            0         1.00        0
 2            0            0         5.00
 1           5.00        0          0
 2            0            0         5.00
 1          8.00         0           0

What I need to print is:
Charge    ADJ    Payment
  10.00     3.00      5.00
    5.00     0.00      5.00
    8.00     0.00      0.00

So I need to calculate summaries and print them when the type changes to 1.
The running total using count, update when type = 1, never reset worked great for assigning a unique number to all related transactions.

If I use formulas to calculate that unique number, do you think it will work to group on that calculated number?
0
mlmccCommented:
There is no common filed between the records just a charge record followed by the related payment and adjustment records?

To group you need a common field.  You may need to use a command or stored procedure to generate the field.

mlmcc
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

loneieagle2Author Commented:
mlmcc,

That is correct, there is no common field since the original report didn't need it. There is just a sequence number to sort by so the items print in the correct order, but nothing to group on.

loneieagle2
0
mlmccCommented:
Look at this report.  I think it does basically what you want through using formulas to calculate the totals.  It fakes having groups through use of a second detail section

Rev1 - WHat you have today
Rev2 - Introduces the fake group footer
Rev3 - Shows details and totals
Rev4 - Totals only

mlmcc
Accountsrev1.rpt
Accountsrev2.rpt
Accountsrev3.rpt
Accountsrev4.rpt
Accounts.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
loneieagle2Author Commented:
mlmcc,

Thanks a lot, that should work. I do have a few tweaks to make because the data isn't quite as simple as the Excel sheet. I don't get into CR very often, but when I do it is usually something like this that is not straight-forward with just simple groups. I'll have to add this "Fake Footer" method to my arsenal. I may have eventually figured this out, but then the Last Record detail may have tripped me up.

Thanks
0
loneieagle2Author Commented:
Thanks for sticking with this problem. I really needed to have this solution today and I probably would not have without you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.