Help with Totals, groups and duplicate records 1

[lamunta]

Hi,

I have a bit of a tricky problem that I would appriciate some help with.

I am writing a report to calculate aged outstanding payments on invoices. This needs to be able to report on the details for a historical period. This is achived by taking the remaining amount (ie outstanding) and adding back historical payments already made.

The problem that I have is that due to the historical payments being held in multiple lines by date on a second table, I have a one to many join structure. This therefore duplicates the actual remaining amounts.

ie

Document Code Remaining Payments Made
1 1000.00 100.00
2 1500.00 50.00
2 1500.00 500.00
3 450.00 100.00
3 450.00 150.00
3 450.00 200.00

So the values I wish to retrieve are
1 - 1000.00 100.00
2 - 1500.00 550.00
3 - 450.00 450.00

To complicate matters further, I need to sum the remaining amounts by document, supplier, currency, Company and age these by due date.

I have tried many ways to get this data out but when I create the totals, I get 5,350 rather than 2,950.

Is there a way to do this or is it just too complex.

Oh - and I am unable to change the tables, add new ones or modify the database in any way.

Thanks

 

[dgillz]

Create a running total to get the remaining column, and set it to evaluate based on formula, make it a maximum summary, reset on change of group. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[lamunta]

Thanks seems to be working.

 

[lamunta]

Having spent a while using this method, I have hit a brickwall. Although it works to exclude the dupplicate records, I am unable to sum the details into other group footers.

I need the following

Detail (hidden)
1 - 1000
2 - 1500
2 - 1500
Group 1
1 - 1000
2 - 1500
Group 2
Total - 2500

So although I can get the data out into group 1, I am unable to perform a sum on sum and therefore cannot calculate my total for group 2.

I have tried;
summing the details - giving 4000.
Summing the details (maximum) - giving 1500
Sum (details/count) - not allowed
All other sums over the group 1 values are not allowed.

Is there a solution to this?

Thanks

 

[dgillz]

Yes, you need to create two more running total fields. The first is just a counter, evaluate on every record, reset on change of group. The second would be a sum running total, evaluate based on a formula, the formula would be {#Counter}=1, and the reset is never.

Let me know if you are still having problems with this.

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[lamunta]

Thanks - I managed to get it working using a similar method. I'm not too hot on using variables so I tried the following.

1 - I created my aged calculations and put these in the detail lines (Hidden - no drill)

2 - 1st grouping on Document number

3 - running totals created - 1st on the duplicate values (maximum details - reset on document number). 2nd on the non duplicated (sum details - reset on document number)

4 - 2nd grouping on Supplier

5 - running totals created - 1st (sum details - evaluate on change of document number, reset on change of supplier). 2nd (sum details - reset on change of supplier)

6 - 3rd grouping on currency

7 - running totals created - 1st (sum details - evaluate on change of document number, reset on change of currency). 2nd (sum details - reset on change of currency)

The thing I was missing until I tried this method was summing the details each time.

This may not be the most eligant or fastest method and it does require lots of calculations but it is one that I could do without learning all about variables.

Thanks for your help again.

 

[dgillz]

Nothing I mentioned requires the use of variables. I avoid variables if at all possible. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com