Unleash your SAP data with MS Excel pivot tables

by

  1. SAP Data in Pivot Tables
  1. SAP Data in Pivot Tables

Unleash your SAP data with MS Excel pivot tables

Pivot tables are a boon to business analysts and managers

Anyone ever tried to enter a value in a pivot table to simulate a change in data? Anyone working in the SAP ecosystem ever dreamt of having all SAP data available in a pivot table? My guess is we all have at one point or another – this would make life so much easier for the SAP community…

Organizing data in a multidimensional format is often represented as a cube, meaning data is arranged according to many different dimensions, individually or regrouped, sorted, dispatched on rows and columns to match the exact presentation needed, but remaining totally flexible. Be it in Finance, Supply Chain, or any other functional domain, multidimensional analysis in Excel is an invaluable tool that can be used for scenario output and reporting – analysts can better appropriate the data, answer business questions in seconds, and get closer to anticipation or even uncovering potential business opportunities.

Add the “killer” plus to your pivot tables with XSBS

Let’s consider a concrete example from SAP with Customer Statistics data. SAP is an extraordinary data reservoir, and table S001, “Sales Statistics per product”, perfectly epitomizes that (available in all ECC systems). From this table, we can pull: products, customers, product family, customer region, sales office, delivering plant, and also, actual sales, revenue, and cost, among plenty of other available data. Using an XSBS X’App, pulling this data out of SAP into Excel is matter of seconds. To this initial data set, simply add a new column locally in your Excel workbook: “Statistical plan”. Building a pivot table from this data is a one-click procedure in XSBS, just like it is in Excel – except your pivot table will let you input data directly into it!

Once your pivot table layout is in place with product family as rows, months as columns, region as a top pivot filter, and actuals and statistical as displayed data, you can directly input a manual forecast for each period by product family in your table, and it will spread the data automatically on the various data knots – simply magic.

Behind the magic

The data you input is disaggregated into each individual data knot (product, customer, sales office, plant, etc.) according to the rule you have defined as you created your pivot table using XSBS: from the simplest proportional rule breakdown to any other rule you define and set up in options, or even code in VBA for the nerdiest among you. And it’ll take another 5 seconds to achieve that.

Of course, you could also be running your own statistical algorithm using Excel features to achieve your target data transformation. Last but not least, if you want to post data back into SAP, XSBS will help you creating PIR (Planned independent requirement) back to ECC via BAPIs, directly from your Excel list.

Daniel Lellouche – CEO and Founder – XSBS-DowapSolutions
Share this article on LinkedIn
Follow us on LinkedIn

About the Author

Daniel Lellouche

Daniel has been focusing on SAP since 1997. While working for SAP, Nestlé and Danone as a senior SAP consultant specializing in Supply Chain, Daniel created DOWAP in 2001 to concentrate all the Supply Chain and SAP expertise accumulated on these projects, and offer it to many other clients in designing and implementing their ERP solution. DOWAP has since been growing steadily, and is now a recognized leader and value-generator for SAP SCM solutions in Europe. Daniel’s acute understanding of the business challenges of his clients led him to create XSBS in 2010, to complement his vision of a fully effective and leveraged ERP solution.