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.
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.
ASKER
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?
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?
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
To group you need a common field. You may need to use a command or stored procedure to generate the field.
mlmcc
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Thanks for sticking with this problem. I really needed to have this solution today and I probably would not have without you.
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