Incorrect automatic Table Join in Sage Line 50 v12 Report

[calamari]

I have been asked to make a report for my client in Sage Line 50 v12. The report they need is basically the Aged Debtors report except they want the Department field added as a column and sorted by it.

I edited the existing Aged Debtors (detailed) report and simply added the new column, but the problem I am getting is it is duplicating some rows.

I made the same report on v16 and it produced the same error, so I looked at the table joins and saw that it was joining at the HEADER_NUMBER. I changed it to join at TRAN_NUMBER and it solved the problem for v16. However, they do not wish to upgrade to v16 and there is no way that I know of to manually edit the joins.

Does anybody know of a solution to this problem please?

[brucedenney]

I don't think there is one, the joins can not be changed in the old report designer.

[calamari]

So is the only option to upgrade or is there another possible solution do you think?

[brucedenney]

You should be able to write a suitable report in V12.

The issue is that the Department is not on the header it is in the split.

You need to alter the layout so that instead of using the audit header aged fields it uses the audit split fields.

If you had a transaction that was split between 2 different departments then you would need to see the 2 splits.

So to do a departmental aged debtors, you will need to run it at the split level not the header level.

I suggest that you add a section for the Header level as well and only show the subtotal of splits for each header and hide the splits.

It is possible for one transaction to be split across 2 departments and for it to appear twice (in two parts) in the same report, the two parts adding up to the total for the invoice but each of the splits not corresponding to the total of the invoice. This way most of the time assuming most invoices are coded to one department, you would just see the header as normal, on the odd occasion if an invoice was split between 2 departments then the item would appear twice each part representing the splits for that department and not the whole invoice.

The structure would be something like Account Ref>Dept>Header>Split(Details)

You would need to strip out the date flag section as a bought forward figure would not make sense unless you want to have a brought forward for each department.

Have you tried running your V16 report against a transaction that has splits that go to more than one department?