excel PivotTables

Multi-Dimensional Reporting

Product Microsoft Excel

Level Undergraduate/Graduate Beginner Focus BI Reporting Author Klaus Freyburger Version 2.0

MOTIVATION You are a manager of a several

sales organisations and one of

your responsibilities is

monitoring and managing sales

activities. You have a number of

OLTP systems to assist with the

recording of day-today

transactions.

At the end of each month, you

are provided with a report which

displays each sale. The format

of the report is illustrated below.

Although this report provides a

lot of information, the

information is not in a format

that can easily assist in the type

of decisions you are required to

make. You have decided to

investigate PivotTables as means

of producing more useful

reports.

PREREQUISITES

None

HINTS No special hints

Case Study: Multi-dimensional Reporting with PivotTables

© 2010 SAP AG Page 2

MULTI-DIMENSIONAL REPORTING

C1.1

Open the Microsoft Excel File, BicycleForPivotV01.xls.

C1.2

Create a PivotTable to determine the total revenue for material groups in each sales

organisation.

C1.3

Slice: restrict to internet sales.

C1.4

Drill-Down: show all products for mountain bikes.

Task 1: Perform multi-dimensional reporting in Microsoft Excel. Short Description: The purpose of this exercise is to create a PivotTable in Microsoft Excel to analyse the data using multi-dimensional reporting. A number of multi- dimensional navigational techniques will be introduced.

Multi-Dimensional Reporting with PivotTables

© 2010 SAP AG Page 3

MULTI-DIMENSIONAL REPORTING

C1.5

Remove Drill-Down for material.

Dice: restrict to first quarter of 2007.

Hint: Add field Date to rows first and use grouping.

C1.6

Remove all filters.

Which product provided the most Contribution Margin Ratio?

Hint: Contribution Margin Ratio is defined as Percentage of Contribution Margin

over Net Sales, where Net Sales is Revenue – Discount and Contribution Margin is

Net Sales – Cost Of Good Sold.