Sorting by #, Subtotal Bal, and then Group by date 1

[BHERNANDEZ]

I am trying to sort by aply_to_no, subtotal it's balance, and the group by doc-due-dt and subtotal each group with a grandtotal on the last page of report. Using CR v7.0.

This is what I have:
doc
cust_no aply_to_no doc_no typ doc_dte doc_due_dte bal

Example:
00001056 135333 135333 I 10/31/03 11/30/03 100.00
00001056 135333 6596654 P 12/05/03 12/05/03 -50.00
50.00
00001155 135334 135334 I 11/01/03 12/01/03 25.00
00001155 135334 135335 C 11/04/03 11/04/03 -5.00
20.00

00001053 0 95864 P 09/15/03 09/15/03-263.04
-263.04

This is what I want it to look like:
00001053 95864 09/15/03 09/15/03-263.04

00001056 135333 10/31/03 11/30/03 50.00

00001155 135334 11/01/03 12/01/03 20.00

I want the doc_no to show on the report, but i need to sort first by apply to number. Then combine everything to one line and group by due date. I am okay with not being able to combine everything to one line. My major concern is getting this data grouped by apply to number with subtotals for balances and then sorted by due date with a subtotal for each group.

I have searched my help files and they refer to a group in specified order option. I do not have that option when I group. It would probably make this a lot easier! Any help would be greatly appreciated.

Thanks!
Bridget

 

[lbass]

If I'm understanding your display correctly, you could create a formula:

if {table.aply_to_no} = 0 then {table.doc_no} else {table.aply_to_no}

Group on the formula. It is unclear what criteria you are using for what date to display in the group header/footer, but it looks like you want to show the earliest {table.doc_dte} and the related {table.doc_due-dte}, so sort ascending by {table.doc_dte}. Insert a sum on {table.bal}. Then drag this field to the group header, and also copy or drag the other fields you want to display into the group header and then suppress the details.

-LB

 

[BHERNANDEZ]

LB,

THanks so much! I had forgoten about the if-then-else. That was a Duh! moment!

I did not know I could drag that information into the group header. My report now looks almost the way I need it. It was so easy! You've been a tremendous help!

The only thing I can't figure out is how to group by doc due date. This is what it looks like now:

{cus-no} {apy to frmla} {doc date} {due date} {sum of bal}
All the above shows in my group header just as you instructed and my details are suppressed.

I need to group by day of the due date showing in my group header. It should show month numerical order with each day grouped together with a subtotal for each day's group. I've tried to sort, but nothing changes in my group header data. I am trying to group a group. Is this possible?

This is what I am trying to acheive:

00001056 132335 12/02/03 01/02/04 50.00
00001056 134353 12/02/03 01/02/04 75.76
00001173 154653 01/02/04 01/02/04 -3.54
122.22
00001056 134358 12/05/03 01/05/04 75.00
00001173 134359 12/05/03 01/05/04 25.00
100.00
This is what I haveonly sorted by the aply-to frmla)
00001056 132335 12/02/03 01/02/04 50.00
00001056 134353 12/02/03 01/02/04 75.76
00001056 134358 12/05/03 01/05/04 75.00
00001173 134359 12/05/03 01/05/04 25.00
00001173 154653 01/02/04 01/02/04 -3.54

I hope this clarifies my last problem. Again, thanks so much for your help! This is a great site and has never failed to help me solve a problem.

Bridget

 

[synapsevampire]

Sounds like you need to insert another due date group outside of the formula grouping and create your summaries on that level.

-k

 

[BHERNANDEZ]

Sy,

My problem with grouping by due date first is that I have different doc due dates for the same apply to number. An invoice due 12/02/03 won't be paid until 01/05/04. The payment record will have a due date of 01/05/04 b/c that is when the transactin was entered. I have to match it up with the invoice # it was applied to and show a remaining balance on the invoice.
I have the remaining balance and all invoice info on one line. However, it is grouped by the invoice # in order to show the correct balance. And I need it by due date after the balance has been totaled. I hope this is made clear. I can live with the report the way it is now, but if I can get it grouped by due date after balances have been totaled, it would be absolutely wonderful!

Bridget

 

[lbass]

This is a little tricky. This approach does not actually insert a date group, but instead creates a fake group footer for groups with the same minimum due date.

First, instead of sorting ascending and then dragging the due date into the group header, insert a minimum on it in the details section. Then go to report->topN/group sort and select "Minimum of {table.duedate}" and choose "Descending" (this will show the earliest duedates first). Your groups are now again ordered as in your "what I have" display.

Next insert an extra Group footer section on the group based on the aply_to formula from my earlier post--let's call the formula {@aply_to_no}--so that you have GF1a and GF1b. Go to format section->GF1a->suppress and enter the following formula:

next({table.duedate}) = minimum({table.duedate}, {@aply_to_no})

Suppress GF1b.

Next create three formulas:

{@reset} to be placed in GF1b:
whileprintingrecords;
numbervar sumamt;

if next({table.duedate}) <> minimum({table.duedate}, {@aply_to_no}) then sumamt := 0;

{@detail} to be placed in the Group Header:
whileprintingrecords;
numbervar sumamt :=

sumamt + sum({table.bal},{table.aply_to_no});

{@display} to be placed in GF1a:
whileprintingrecords;
numbervar sumamt;

Format the {@detail} field to suppress.

That should give you the display you want.

-LB

 

[BHERNANDEZ]

LB,

I will try and get back to you on how it went. Thanks!

Bridget