BTerrell Group Blog

Sage 300 ERP (Accpac): Sales History Reporting Using PowerPivot

Posted by Tony Zhang on Mon, Sep 30, 2013

This blog is a continuation of the Sage 300 ERP (formerly known as Accpac) business intelligence journey using PowerPivot, and covers Sales History reporting.

Why bother to re-create the Sales History out-of-the-box report from Sage 300 ERP?

The very answer also applies to questioning why I would need Business Intelligence (BI) when I already have my reports. In some sense, it is like comparing financial accounting and managerial accounting – both provide information to support business decisions, but their consumer, format, span and depth are different. Reports usually require certain formats and are created for a set of period, such as fiscal year or period. On the other hand, BI is flexible in its format. Many times it has powerful visualization ability, and can be generated for any period of time such as daily, weekly or monthly. It is important for the BI solution to provide a holistic view of the business and be able to adapt to changes quickly.

As an example, I don’t like to break my train of thought during analysis by closing the report, re-selecting filters (if they are even available), and waiting for minutes if not hours to re-run the report. In addition, in the Sales History report case, I don’t want to limit the access to the report to my ERP users, I want the right person to access the right information anywhere, anytime and on any device.  I will discuss these (data stewardship and mobility) in the coming blogs after I have thoroughly covered Power BI.

Now let’s look at how the Power Pivot delivers the right information with ultimate flexibility.

In the 1st screenshot below, the grand total highlighted in green ties to the numbers in the 2nd screenshot which is from Sage 300 ERP's out-of-the-box Sales History Total Report, the invoice data highlighted in yellow ties to the numbers in the 3rd screenshot that from the Sales History Detail Report. This shows that the PowerPivot report had the right numbers, which gives me peace of mind that my reports will show accurate information. Moreover, without re-running the report, it is delivered in one shot.

PowerPivot Sales History Report:

Sage 300 ERP - Sales History PowerPivot Report

In addition to the out-of-box report filters as shown in the screenshot below, you can see by looking at the slicers circled in the screenshot above, PowerPivot allows us to filter the sales history by week, by item segment 2 and 3, and by category. It takes me minutes -- if not seconds -- to re-group the output to analyze the sales, COGS and margin by item category, customer group, territory, sales person and other dimension attributes, thanks to the Excel data model.

Sage 300 ERP: Sales History Report Input Screen

O/E Sales History Totals Report:

 O/E Sales History Totals Report

O/E Sales History Detail Report:

O/E Sales History Details Report

When I examined the report closely, I noticed that some sales had $0 cost, leading to a 100% margin. While this would be nice, it's certainly not accurate. This happened because the I/C item is set to use “Standard Cost” but the standard cost is set to $0. Plus, the data source used for the sales history report uses standard cost. To analyze the margin using actual cost, we need to change the data source. I will cover it the next blog.

Click here to get a static version of the reportto get a sense of the look and feel. I will share the actual report later after I complete other blogs in this series, which will cover Sales Analysis and Power BI, along with Purchase Analysis.

Stay tuned...

If you would like to learn more, don't hesitate to ask for a FREE 30-minute consultation from BTerrell Group...we'd be happy to help!

Free Consultation

Related Content in this Series

Tags: Accpac, Sage 300 ERP, reporting, Sales History