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)? 

Parents
  • 0

    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

  • 0 in reply to Accsys Consulting AU

    Would the vendor Transactions report produce this information?

  • 0 in reply to Jay Giannantonio

    Hi Jay, it can though it will require experience with Crystal Reports and Microsoft SQL.  You will need to insert a subreport into Crystal Report APVTRN01.RPT (Vendor Transactions) and add tables APIBH and APIBD and link back to the main report (you would have to review the tables in the main report to work out the best to link on).  You can then insert the G/L Account and line amount into the Details section in the subreport.  You will need to add GLAMF (GL Account Master File) table into the subreport and join it to APIBD to get the G/L Account Description if you need it.  Now this will allow you to display the G/L Expense Account under each Vendor and Invoice on on the Vendor Transactions report - so all the data you need will be there but it won't be grouped and sorted how you need.  You could either re-work the report formatting and layout to group it how you need, you may need to use Shared Variables to track subtotals in the subreport to send back to the main report.  Alternatively you can run the report with all the data you need, export it to excel, then transform it manually or with pivot tables to arrange it how you need it (by Expense -> Vendor) then save it as CSV for analysis.  Not the easiest job but toally doable.  If you're not experienced with Crystal there are great books and tutorials out there.  Download the Sage 300 Object Model from here at Sage City to help looking up AP Tables and Fields you need.  Cheers...Tim

Reply
  • 0 in reply to Jay Giannantonio

    Hi Jay, it can though it will require experience with Crystal Reports and Microsoft SQL.  You will need to insert a subreport into Crystal Report APVTRN01.RPT (Vendor Transactions) and add tables APIBH and APIBD and link back to the main report (you would have to review the tables in the main report to work out the best to link on).  You can then insert the G/L Account and line amount into the Details section in the subreport.  You will need to add GLAMF (GL Account Master File) table into the subreport and join it to APIBD to get the G/L Account Description if you need it.  Now this will allow you to display the G/L Expense Account under each Vendor and Invoice on on the Vendor Transactions report - so all the data you need will be there but it won't be grouped and sorted how you need.  You could either re-work the report formatting and layout to group it how you need, you may need to use Shared Variables to track subtotals in the subreport to send back to the main report.  Alternatively you can run the report with all the data you need, export it to excel, then transform it manually or with pivot tables to arrange it how you need it (by Expense -> Vendor) then save it as CSV for analysis.  Not the easiest job but toally doable.  If you're not experienced with Crystal there are great books and tutorials out there.  Download the Sage 300 Object Model from here at Sage City to help looking up AP Tables and Fields you need.  Cheers...Tim

Children
No Data