BTerrell Group Blog

Sage 300 ERP (AccPac): 1099 Report Using PowerPivot

Posted by Tony Zhang on Fri, Jan 24, 2014

The other day I was asked by one of my controller clients:

“Is there a way to run a vendor transaction report that shows the vendor name, the sum of the payments made to the vendor, and the 1099 flag?" 

He continued, "I want to be able to review a report of all vendor payments and see if they are currently flagged for 1099 reporting or not.  That way, if I see a vendor that I think should be flagged but isn’t, then I can research it.”

If you use Sage 300 ERP and report 1099, I highly recommend you to read Chris Firra’s recent blog: Common Questions Re:Generating 1099-MISC Forms from Sage 300 ERP. This will give you a good understand of how 1099 works in Sage 300 ERP.

As quoted from Chris’s blog, “Unfortunately, there is no standard report that shows the detail of invoices that make up a 1099 amount.”

This blog presents the solution using PowerPivot. In the following screenshot, the payments are filtered by year/month (time dimension), vendor’s 1099 code and the transaction’s 1099 code. In case there is a question, you can research by following the payment batch-entry number AND the invoice batch-entry number…quickly.

Sage 300 ERP: PowerPivot 1099 Report


In the following example, the payment in 1621-31 was $7,400, but only $6,900 is applicable to 1099 code 1, when checking invoice entered in 6773-13.

1099PowerPivot2 resized 600

1099PowerPivot3 resized 600

Finally, the user can compare - and update if necessary - the number in the 1099/CPRS Inquiry as shown in the screenshot below:

1099PowerPivot4 resized 600

I’d also like to point out that this report is based on the same data model used by the AP aging report, with 1099 code fields added to the Vendor and Payment tables, as show below.

1099PowerPivot5 resized 600

Like to have such a report on your hand to improve the productivity? Don't hesitate to contact the experts at BTerrell Group!


Other Related Blog Posts

PowerPivot Series

Tags: Sage 300 ERP, PowerPivot