BTerrell Group Blog

Sage 300 ERP: AR Aged Trial Balance Reporting using PowerPivot

Posted by Tony Zhang on Wed, Jun 26, 2013

This blog introduces the Sage 300 ERP A/R Aged Trial Balance Report, and presents solutions to address challenges like adding more Aging Periods, grouping by National Account, filtering by the Account Set (AR control account) at transaction level, and performance issue - using PowerPivot. PowerPivot is a free add-in to Excel 2010 and is a built-in functionality of Excel 2013.

The Sage 300 ERP A/R Aged Trial Balance Report is a very sophisticated and flexible report that provides quite a few parameters. There is a dedicated aging dll, a.k.a the processing view to processing all types of transactions and documents and finally populated the ARAGED table which serves as the data source for the report.

A/R Aged Trial Balance Report

Here is a sample output of the Sample Company Inc. in Sage 300 ERP 2012.

Sample output - AR Trial Balance Report

Sample output - AR Trial Balance Report 2

It is possible to use PowerPivot to reproduce the same result.

Powerpoint AR Trial Balance Report

In addition, the PowerPivot report allows users to easily add a new aging period, group by national account, filter by account asset at transaction level and much more. You might want to try it out here.

PowerPivot report
In terms of performance, it can take a long time for the report to process data. I tested for a Sage 300 ERP company that has over 600K invoice documents and 1.4m payments. It took about eight minutes for the Crystal report to complete. Using PowerPivot in 64-bit Excel 2013, it takes five to six seconds to respond to a selection in slicers. To make a fair comparison, we need to add the time needed to load the data into the Excel data model, which takes about two minutes and depends on things like network speed. Once the data is loaded, PowerPivot allows you to quickly slice the same data. Using Crystal reports, you have to wait another eight minutes for each change of the parameters.

In summary, PowerPivot can help answer hard questions in a timely fashion.

Your company might have an ERP system that has accumulated very large data over the years; you might know your most valuable customers and bestselling products very well. But what if you were asked these questions by week, by population of the city, by pay grade of the sales representative, and by color of the product, together with the corresponding product cost and marketing expenses? One single PowerPivot report to help you stay on top of the game by helping you answer these questions -- and quickly.

Want more? Read my blog post on creating an AP Aging Report.

Tags: Sage 300 ERP, A/R, Accounts Receivable, AR aged trial balance report

AR Customer List: Little Known/Little-Used Function in Sage 300 ERP

Posted by Anthony Cox on Wed, Jun 19, 2013

As a Sage client, you may be familiar with the benefits of building and working from call lists in Sage CRM. Often, this functionality provides an efficient methodology for a workflow involving identification of a subset of clients or prospects you may want to call on for a specific purpose (i.e. overdue invoices, recent orders, etc.). And as a salesperson, this concept is often helpful for following up with opportunities that were recently discovered, as in new prospects!

As helpful as this function is within Sage CRM, a close resemblance to it exists in Sage 300 ERP, although many users have been unaware of it – yours truly included. Thankfully, I benefit from a top-shelf team of consultants that often reveal to me new tricks of the trade. I thought you might want to see how this tool functions, so check out the attached video demonstration.

The demo shows how the little-used A/R Customer List, along with Customer Inquiry, Optional Fields and a telephone, can provide customer relationship management functions, although not on the same scale as Sage CRM.  The Customer List simply allows a user to construct an actionable list of customers using criteria from the customer master. 

Note in the video example this could include creating a call list for:

  • customers over their credit limit,
  • a list of key customers to invite to an event,
  • a list of customers with follow-up dates, etc. 

This list of factors which can be targeted includes standard fields in the customer record and can also incorporate Optional fields you have defined. This makes for a pretty powerful and flexible search tool. 

In the attached video, my consultant created a NEW CUSTOMER optional field that is automatically created when adding a customer.  The AR Customer list tool can create a follow-up call list to find out if they're satisfied with the product received and record any comments.  You can then take the customer off of the new customer list, or perhaps schedule a follow-up activity and move on to the next customer identified in the list.

By the way, once you have clicked on a customer in your list, the A/R Customer Inquiry tool allows you to drill down and get even more useful information before you make your phone call. Here you can see information about invoice dates and statuses, what items were recently ordered, and even contract pricing information.

(note: there's no audio; it's just video)


Let me know if you find this helpful, or if you would like some more "tricks of the trade" from the BTerrell Group.

Tags: Sage 300 ERP, A/R, Accounts Receivable, AR customer list

Announcing Accpac Back to School Training

Posted by Meredith Gooch on Mon, Aug 10, 2009

Learn how to make the most of Sage Accpac ERP and join us for training this fall.

Sage Accpac ERP Back to School Training Schedule
Bank Services and Financial Reporter - Tuesday, September 15, 2009
Sage FAS Fixed Assets Part I - Wednesday, September 16, 2009
Sage FAS Fixed Assets Part II - Thursday, September 17, 2009
Accounts Payable - Tuesday, September 22, 2009
Accounts Receivable - Wednesday, September 23, 2009
General Ledger - Thursday, September 24, 2009

Check out our training schedule for additional details.

- Meredith Gooch, Marketing Manager

Tags: Sage Accpac ERP, Accpac, A/R, AP, accounts payable, Accounts Receivable, bank services, training, GL, general ledger, FAS Fixed Assets, Financial Reporter

Sage Accpac ERP Tip: Save Settings as Defaults FAQ

Posted by Meredith Gooch on Thu, May 28, 2009

Q: When others log on, will they see my custom settings if I have saved them?
A: No, it is your own personal screen. Each user can have his/her own custom screen saved to their own preferences without having to change settings between users.

Q: Does every Sage Accpac ERP report have this function?
A: No. For example, in the A/R module, only 5 reports have this function:
1. A/R Trial Balance Report
2. A/R Customer Report
3. A/R Statement/Letters/Labels
4. A/R Customer Transaction Report
5. A/R Retainage Report

Q: How do I restore back to the default settings?
A: Under the “Settings” menu, select “Clear Saved Settings.”

- Sheila Zhang, Project Manager

Tags: Sage Accpac ERP, A/R, Accounts Receivable, Trial Balance Report, default settings, customer report, customer transaction report, retainage report, AR Troubleshooting

Sage Accpac ERP Tip: Save Settings as Defaults

Posted by Meredith Gooch on Mon, May 25, 2009

Did you know that when you open the A/R Trial Balance Report in Sage Accpac ERP, you do not need to manually select the aging periods or customer ranges every time? Accpac can remember them for you.

After opening the A/R Trial Balance Report, you will see the default screen. No matter which fields you change, you end up losing your specifications after you have closed the A/R Trial Balance Report. The next time you open it again, all the criteria are restored to the original default values.

There is a way around this!

All you have to do is click on “Settings” at the top of the default screen and select “Save Settings As Defaults.” All the changes you have already made to the default screen will be recorded by Accpac so that the next time you open the A/R Trial Balance Report, you will see your criteria as you have saved them.

Check back later this week for FAQ’s about the this topic.

- Sheila Zhang, Project Manager

Tags: Sage Accpac ERP, A/R, Accounts Receivable, save settings as default, Trial Balance Report