BTerrell Group Blog

Sage 300 ERP (Accpac): Sales Analysis Overview Using PowerPivot

Posted by Tony Zhang on Fri, Oct 11, 2013

Designing reports which deliver insight into your business to help you control and improve business processes are monumental to improving your sales analysis.  Creating these reports and recommendations requires expertise in the following areas:

  • Business Process and Requirements
  • Data Modeling, understanding the current form of your data and the steps required to transition it into a usable format
  • Designing the Reporting Tool

In a perfect enviroment, having all three of these components is a luxury.  Often times, however, only having two still produces good results.  Even with creating accurate reports, developed by the end users using Crystal Reports or Excel, our clients find that from time to time they still need a little assistance in locating the right data table and fields in the ERP database or in the advanced reporting tool.  I often find many reporting tool vendors view the problem from the technical side and claim the tool is powerful and easy to use, which is true if  the data source structure is relatively simple.  Unfortunately, this is not the case in the ERP world, given the comlexity of the ERP data.  Hundreds, if not, thousands of tables and numorus data fields are designed for transactional processing and not for reporting and analysis.  More than a decade ago, data modeling techniques, such as Cube, were created to adddress challenges in reporting and analysis (one example is: SQL server analysis service (SSAS)). The problem is the SSAS data cubes are not easy to build and maintain, which translate to higher cost of ownership.  Below, I believe you will see why using the Excel Data Model, PowerPivot – the silbling of SSAS, is a viable option for you and your business.

In this blog, I demonstrate how to gain insight into your sales numbers in Sage 300 ERP using the Excel Data Model, and also make the point why working with a professional services member makes an impact in your workflow.

In my previous blog, I raised the question about the margin calucated in the Sales History Report.  Here, I have listed the report data source for the Sage 300 ERP Sales History Tables, OESHHD and OESHDT.  Since we do not have the actual cost information from the Sales History Table, we need to find it from a separate data table.  In this case, I used the Sale Posting Journal Tables, OEAUDH and OEAUDD, which, have 113 and 114 fields seperately.  As you can see, this information is right in front of us to match it to the right data fields.  To include more information  in my report, I developed the below query to add to the data model.

Query for matching fields

Once I added the query, “OE Transactions” highlighted below, into the data model, also shown below, I created relationships between the “OE Transactions” table to other dimensions.  Relationship is like a super VLOOKUP function.  For example, after I linked “OE Transactions” to the “Customers” dimension using the “Customer Number” field, as hightlight below, I can run the sales analysis margin by customer group, geography and etc.  After I linked the table to the time dimension, I can run the sales analysis margin by week or holiday.  Also, once I linked the table to the Item demension, I can run the sales analysis margin by item segements, category and etc. You can continue this as needed.

Sage 300 ERP Sales Analysis Reporting using PowerPivot: Creating relationships

With the data model in place, I created the Sales Analysis report to analyze the gross profit using actual cost by dragging and dropping the cells I choose.   Highlighted in red, is the actual cost where the COGS is 0.  Then, I can add additional calucations to the data model to continue improving the report.  For example, I calcuated the average selling price (ASP) by taking the price before the discount, as shown in the last column in the screenshot below.

Sage 300 ERP using PowerPivot: Sales Analytics Report

As you can see, the Sales History Report below produces the same results as compared to the Sales Analysis Report shown above.

Sage 300 ERP and PowerPivot: Sales History Report

I hope you found this blog 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 and grasp the look and feel.  

After completing additional blogs in this series, I will re-visit this report once again.  Be on the lookout for an overview of purchasing.

Related Content in this Series

Tags: Sage 300 ERP, PowerPivot, Sales Analysis