Annual Report of Vendor Payments by vendor by expense type

Sage 300

How would I get a .csv file of all vendor payments made during a calendar year by vendor by expense type(account)? 

  • What is the business case for doing this by Vendor Payment instead of Vendor Invoice?  Is there a Cash Accounting reporting requirement?  If you can just do this by Invoice instead of Payment then the task will be substantially easier.  For example in the Sage Intelligence module, from memory, there are already Top Vendors pre-built report available in the Report Manager where after running the report for a fiscal year, you could modify the Pivot Table to get what you need.  Alternatively you could add a subreport to APVTRN01.RPT to add tables APIBH->APIBD to include the Invoice lines expense codes and line totals then group and sort the report on Vendor -> Expense/GL Code and get a GL Code line total summary for each Vendor.  You will need to format the report so it goes cleanly to Excel then save it to a CSV.  This will be easier then trying to format the report directly into a CSV.  I think though using Sage Intelligence to edit the pivot table to sort on Vendor then group on G/L Expense account will be your best bet (remember to unhide I think sheet 2 to see all raw data you have available)...unless you really do need to report on Payments which will then add several more tables, joins and will require strong SQL and Crystal Reports knowledge.  All the best!  Cheers...Tim

  • By vendor invoice would also work. The goal is to analyze all spending by GL account classification to gain insight into where we are spending to help negotiate with vendors for volume discounts and potential bulk purchasing.

Reply Children
No Data