BTerrell Group Blog

Sage 300 ERP Payroll: Creating Dashboards Using PowerPivot

Posted by Tony Zhang on Fri, Feb 14, 2014

If you use Sage 300 ERP Payroll, you might like to have a dashboard to show how many years your employees have worked for your company,and in which place and position, like shown below. In addition, you also might like to analyze the payroll date in Excel as well, like the example in this link.

Analyze Payroll with PowerPivot1 resized 600

One day, one of my clients had an issue with the US Social Security tax calculation. With the help of the PowerPivot data model, I can quickly spot the problem across the year and all employees, as highlighted in the following screenshot.

Analyze Payroll with PowerPivot2 resized 600

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

Tags: Sage 300 ERP, PowerPivot

Comparing and Contrasting Cloud and On-Premises ERP Solutions

Posted by Brian Terrell on Wed, Feb 05, 2014

BTerrell celebrated our 1 year anniversary as an Intacct value added reseller last month when the calendar rolled over from 2013 to 2014.  In 23 years, the only ERP software we have supported is Sage 300 ERP, so adding Intacct in 2013 required substantial investment, retraining and effort on our part.  I could not be more pleased with our staff who attended all of the implementation and Intacct Platform Services training classes, with our prospects who worked with us in the software selection process and ultimately chose Intacct, and with the Intacct channel managers, technical support staff and management that helped us successfully add the product to our portfolio.  A special thanks goes to Keith Karnes, who brought with him 3 years of very heavy duty implementation experience when he joined BTerrell Group in January 2013.  Without everyone pulling together under Keith's leadership, we could not have enjoyed the level of success we have.

Early on, Keith and I spent some time together contrasting cloud and on-premises ERP offerings. Ultimately, we documented the strengths and weaknesses of each model.  Cloud vs OnPremises resized 600

In this month's BTerrell Group webinar, which is scheduled for February 12th, Keith and I will engage in a discussion reviewing the differences between on-premises ERP applications and cloud ERP application, and you are invited to listen in and join in.  If you have ever wanted to hear two experienced consultants discuss the relative features of cloud ERP and on-premises ERP, this will be an excellent opportunity.  We have thought a lot about this topic, and we look forward to a lively discussion on the merits of the cloud versus the merits of on-premises applications.  

These are just a few of the areas we should cover:

  • What differs in the technology and architecture used to develop cloud and on-premises ERP?
  • How does each model facilitate customization?
  • Does one system integrate more easily with other applications or data sources than the other?
  • How do the respective software publishers revenue models differ and what does this mean to the client?

We'll discuss these items and many more, and this should be an excellent opportunity to gain a head start on understanding and contrasting the most important emerging trend in ERP architecture today with the time-tested ERP model on which many businesses currently rely.

An added bonus is that Rob Johnson, from Avalara, has graciously agreed to moderate the discussion for us.

Please make plans now to join Keith, Rob and me as we share share why we are bullish on the future for financial automation through both the cloud and on-premises applications.

Date: Wednesday, February 12, 2014
Time: 1:30 pm CT

Register Today

Tags: Sage 300 ERP, Intacct

Sage 300 ERP in the Cloud

Posted by Chris Firra on Fri, Jan 31, 2014

An on-line version of Sage 300 ERP has now been available for over ten years.  Side-stepping the semantic question of whether Sage 300 Online is truly a "cloud application", it is certainly a true statement that the on-line application has been since its inception a hosted version of the award winning on-premises accounting software, offering all of the same functionality.  Because it is remotely accessible using a MS Windows computer and internet browser, it offers all of the benefits of a SaaS-based, cloud application.

Recently, we've received inquiries from existing Sage customers that are interested in switching from the on-premises version to Sage 300 Online.  Why?  The reasons vary, but most relate to one or more of the benefits of Sage's SaaS product, including the following:

  • Elimination of the investment in new database/application servers and of ongoing maintenance of these servers, 
  • Freedom from backup worries, as database and environment backups are handled by Sage,
  • Secure, remote accessibility without the need for a complicated VPN, 
  • Predictable, flat monthly cost under a subscription model, and 
  • Perhaps most importantly, freedom from having to undergo periodic software upgrades, which are handled entirely by Sage.  

Sage customers also know that if they decide to switch back to an on-premises version, they have the ability to do so without having to undergo an expensive data conversion.

At last summer’s Sage Summit, Sage announced that Sage 300 Online would be a undergoing a significant overhaul to modernize both its underlying architecture as well as the user experience.  The roll-out of the new product is now gaining momentum. 

The improvements began with moving Sage 300 Online to the Microsoft's Windows Azure Platform, which combined multi-tenant database platform services with private, virtualized environments for the Sage 300 ERP applications.   The latest Microsoft Remote Desktop client, RDP8, replaces the older Citrix ICA client to provide a more modern, windowed, user experience.  By leveraging Microsoft's worldwide network of Azure datacenters, Sage should realize improvements in performance, scalability and reach.

Sage Cloud 1 resized 600

After logging in with a new Sage ID, users land in an application portalthat provides access to subscribed and authorized applications, such as Sage 300 ERP.  I would not be surprised to see access to new Sage Mobile applications available here in the future.Register Now! Cloud vs On-Prem ERP Solutions Webinar

Sage 300 Online is not yet suited for all customers, especially those with customizations or 3rd party applications that are not currently provided through the platform.  The supported 3rd party solutions include popular applications from Aatrix, Avalara, Orchid Systems, Pacific Technologies, and Wellspring Software.  New applications may be expected as they are vetted by Sage. Sage 300 Online is also moving to a "continuous delivery model", which means that new features and improvements should appear in the product sooner rather than waiting for normal 18-24 month upgrade cycles.

Want to learn more about comparing and contrasting cloud and on-premises solutions? Join us on February 12, 2014 for a webinar where we'll chat about the similarities and differences...and get your burning questions answered!

If you are interested in learning more about Sage software in the cloud, feel free to contact us.

Tags: Sage 300 ERP, Sage 300 Online

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

Sage 300 ERP (AccPac): Project Analysis Using PowerPivot

Posted by Tony Zhang on Wed, Jan 08, 2014

This blog is a continuation on the Sage 300 ERP business intelligence journey using PowerPivot. I will focus on the project analysis in this blog.

If you use Sage 300 ERP Project and Job Costing module, then you might like a report like this:

PowerPivot Project Analysis Report using Sage 300 ERP data

The report replicates the Sage 300 ERP Contract Summary report, with additional information like PM Cost Left to Spend while providing the flexibility and performance improvements brought by using Excel PowerPivot. For example, this report allows you to select projects that are not consecutive by holding the CTRL key and click the projects you like from the “Project” slicer circled above, which is impossible when using the standard report as show below:

Sage 300 ERP Contract Summary Report

I highlighted the numbers in the Excel report above and the Crystal report below to show they are the same.

Sage 300 ERP Contract Summary - Crystal Reports

Sage 300 ERP Contract Summary Totals - Crystal Reports

The next report is meant to analyze the project cost. Notice that both original cost and current cost estimates are presented together with the actual cost – a more comprehensive analysis comparing with the “Detailed Costs” as show in the following screenshots, where only one of them is presented in the report.

Sage 300 ERP - Project Cost Report using PowerPivot

Sage 300 ERP: Detailed Costs Report

Again, I highlighted the numbers in the Excel report above and the Crystal report below to show they are the same.

ProjectAnalysis7 resized 600

The last example is the standard “Transaction History” report and the PowerPivot version of it. Again, the point here is the flexibility and the performance gain from using PowerPivot.

ProjectAnalysis8 resized 600

ProjectAnalysis9 resized 600

Sage 300 ERP: Transaction History Report Using PowerPivot

I hope this blog is helpful as you continue to understand PowerPivot’s position and the level of professional services we provide here at BTerrell Group. Feel free to view the report in static form here to grasp the look and feel. Please “Scroll to the first sheet” to see those three reports.

ProjectAnalysis11 resized 600


Other Related Blog Posts




Tags: Sage 300 ERP, PowerPivot