In this article, I'm going to build an Excel report to query AR; that is, a pivot table aging report.
The more advanced techniques demonstrated here are
a) connecting to accpac from within an Excel macro
b) using an Accpac view to safely update the Accpac database
Pivot tables are a great reporting tool. In this case, we have our customers grouped into sales channels, so the pivot table starts by summarising per sales channel, and allows drill down to customer. Another click lets the user drill down into invoice detail. We also show credit limit, terms, last payment date. We have the same format for AP with the same drill-down capabilities.
I will assume the reader knows basic Excel macros.
Accpac does all the hard work of aging: there is a temporary table which very neatly ages all open items. However, we can't simply query this table in Excel using the SQL approach I explained in part 1, because the temporary table needs to be updated first; this aging table is not updated whenever something changes in AR. What we do is get Accpac to do the work, and this can be done from within Excel, via a macro which connects to Accpac.Accpac bundles program logic into "views"; updated AR is a certain view, and the job of our macro is to run that view.
Working out which view you want is pretty easy. I recorded a macro while running the existing AR Aging report, and in the code generated, I learnt how to invoke the view. Recording macros in Accpac is not the same as running macros in Excel; hopefully this tutorial will bridge the gap.
This was developed on Accpac 5.5A.