SageForum for UK versions of Sage 50- Help and support.

Hi

I am hoping someone might be able to help me set up a report. I am using Sage 50 Accounts Professional 2010 ver 16. We use Projects to analyse purchase costs incurred by customer, and thereby the GP margin by customer. I have been preparing an analysis of GP margin by customer, picking up the total purchase costs per customer from Projects and have noticed that the total of all purchase costs reporting to Projects is less than total purchase costs in the Profit and Loss, suggesting that a number of costs have been posted without also being analysed to a project code. What I would like to do is create a report listing all purchase invoices/credits showing the project codes those invoices/credits have been posted to enabling me to simply pick up any invoices/credits that have not been posted to a project code.

My initial thoughts were to edit the detailed supplier day book reports to include the project reference and I have tried to use Data>Joins to add the Project table into the report and then link the Project.Reference variable to the report. So far I have linked Project.reference to Audit_Split.Inv_Ref and the report is returning "No data was returned for this Report". My problem is, firstly I am not sure if my approach is correct. I have looked into other reports within Sage, but other than Projects itself, there does not seem to be anywhere else that links to Projects. Secondly, assuming my approach is correct, I am not sure which of the existing variables I need to link Project.Reference to.

Any ideas greatly welcome
Thanks
Maxf

My report is working (with a little help from IT!) . We added two tables to the Join Editor - Project and Project_Tran. First link was Project.Project_ID to Project_Tran.Project_ID and second link was from Project_Tran.Audit_Trail_ID to Audit_Split.Tran_Number. The report is now returning the Project ID for all the purchase invoices posted, and blanks where no Project ID was entered on input.

Maxf

Hi Max

Thanks for the information. I have added the tables and joined the fields but my report will only return transactions that have a value in the project field, the report excludes the blank ones.

I have copied the invoice reference field in the same report as this will still be included if blank (changing it to Project.Reference) as well as a new expression. Both attempts do not show transactions without projects, did you encounter this?

Thanks
Cyrill

Hi Cyrill

I don't recall having this problem. The report was designed specifically to return all supplier transactions within a certain Nominal Ledger code range and the Project reference or a blank where no Project code had been input. The amendments as detailed in my earlier post achieved this. Don't know if it makes any difference, but are you using the same version of Sage?

Regards

Maxf

There are two things you need to do.

1/ in the report properties you need to set "include records with no transactions" to true.

2/ in the joins tool, you need to make the join and outer join