Excel Power Pivot - Aesthetic Reports


Advertisements


You can create aesthetic reports of your data analysis with Power Pivot Data that is in Data Model.

The important features are −

  • You can use PivotCharts to produce visual reports of your data. You can use Report Layouts to structure your PivotTables to make them easily readable.

  • You can insert Slicers for filtering data in the report.

  • You can use a common Slicer for both the PivotChart and the PivotTable that are in the same report.

  • Once your final report is ready, you can choose to hide the Slicers form the display.

You will learn how to get reports with the options that are available in Power Pivot in this chapter.

Consider the following Data Model for illustrations in this chapter.

Following Data Model

Reports based on Power PivotChart

Create a Power PivotChart as follows −

  • Click the Home tab on the Ribbon in PowerPivot window.

  • Click PivotTable.

  • Select PivotChart from the dropdown list.

  • Click New Worksheet in the Create PivotChart dialog box.

An empty PivotChart is created in a new worksheet in Excel window.

  • Drag Sport from Medals table to Axis area.
  • Drag Medal from Medals Table to ∑ VALUES area.
Drag Medal
  • Click the ANALYZE tab in PIVOTTABLE TOOLS on the Ribbon.

  • Click Insert Slicer in the Filter Group. The Inset Slicers dialog box appears.

  • Click the field NOC_CountryRegion in the Medals table.

  • Click OK.

Analyze

The Slicer NOC_CountryRegion appears.

  • Select USA.
  • Drag Gender from Medals table to GENDER area.
GENDER Area
  • Right click on the PivotChart.
  • Select Change Chart Type from the dropdown list.
Chart Type

The Change Chart Type dialog box appears.

Click on Stacked Column.

Stacked Column
  • Insert Slicer for Sport field.
  • Drag Discipline from Disciplines table to AXIS area.
  • Remove the field Sport from AXIS area.
  • Select Aquatics in the Slicer – Sport.
Select Aquatics

Report Layout

Create PivotTable as follows −

  • Click on Home tab on the Ribbon in PowerPivot window.

  • Click on PivotTable.

  • Click on PivotTable in the dropdown list. The Create PivotTable dialog box appears.

  • Click on New Worksheet and click Ok. An empty PivotTable gets created in a new worksheet.

  • Drag NOC_CountryRegion from Medals table to AXIS area.

  • Drag Sport from Medals table to COLUMNS area.

  • Drag Discipline from Disciplines table to COLUMNS area.

  • Drag Medal to ∑ VALUES area.

Report Layout

Click on the arrow button next to Column Labels and select Aquatics.

Labels Column
  • Click on the arrow button next to Row Labels.
  • Select Value Filters from the dropdown list.
  • Select Greater Than Or Equal To from the second dropdown list.
Select Greater

Type 80 in the box next to Count of Medal is greater than or equal to in the Value Filter dialog box.

Type 80
  • Click the DESIGN tab in PIVOTTABLE TOOLS on the Ribbon.

  • Click on Subtotals.

  • Select Do Not Show Subtotals fromn the dropdown list.

Not Show Subtotals

The Subtotals column – Aquatics Total gets removed.

Subtotals Column

Click Report Layout and select Show in Outline Form from the dropdown list.

Outline Form

Check the box Banded Rows.

Box Banded

The field names appear in place of Row Labels and Column Labels and the report looks self-explanatory.

Field Names

Using a Common Slicer

Create a PivotChart and PivotTable next to each other.

  • Click the Home tab on the Ribbon in PowerPivot tab.
  • Click PivotTable.
  • Select Chart and Table (Horizontal) from the dropdown list.
Home Tab

The Create PivotChart and PivotTable (Horizontal) dialog box appears.

Select New Worksheet and click OK. An Empty PivotChart and an empty PivotTable appear next to each other in a new worksheet.

OK
  • Click PivotChart.
  • Drag Discipline from Disciplines table to AXIS area.
  • Drag Medal from Medals table to ∑ VALUES area.
  • Click PivotTable.
  • Drag Discipline from Disciplines table to ROWS area.
  • Drag Medal from Medals table to ∑ VALUES area.
Values Area
  • Click the ANALYZE tab in PIVOTTABLE TOOLS on the Ribbon.
  • Click Insert Slicer. The Insert Slicers dialog box appears.
  • Click on NOC_CountryRegion and Sport in Medals table.
  • Click OK.
Medals Table

Two Slicers – NOC_CountryRegion and Sport appear. Arrange and size them to align properly next to the PivotTable.

Two Slicers
  • Select USA in the NOC_CountryRegion Slicer.

  • Select Aquatics in the Sport Slicer. The PivotTable is filtered to the selected values.

Selected Values

As you can observe, the PivotChart is not filtered. To filter PivotChart with the same filters, you need not insert Slicers again for PivotChart. You can use the same Slicers that you have used for the PivotTable.

  • Click on NOC_CountryRegion Slicer.

  • Click the OPTIONS tab in SLICER TOOLS on the Ribbon.

  • Click Report Connections in the Slicer group. The Report Connections dialog box appears for the NOC_CountryRegion Slicer.

Options Tab

You can see that all the PivotTables and PivotCharts in the workbook are listed in the dialog box.

  • Click on the PivotChart that is in the same worksheet as the selected PivotTable and click OK.

  • Repeat for Sport Slicer.

Workbook

The PivotChart is also filtered to the values selected in the two Slicers.

Filtered

Next, you can add details to the PivotChart and PivotTable.

  • Click the PivotChart.
  • Drag Gender to LEGEND area.
  • Right click on the PivotChart.
  • Select Change Chart Type.
  • Select Stacked Column in the Change Chart Type dialog box.
  • Click on the PivotTable.
  • Drag Event to ROWS area.
  • Click the DESIGN tab in PIVOTTABLE TOOLS on the Ribbon.
  • Click Report Layout.
  • Select Outline Form from the dropdown list.
Legend Area

Selecting Objects for Display in the Report

You can choose not to display the Slicers on the final Report.

  • Click the OPTIONS tab in SLICER TOOLS on the Ribbon.

  • Click Selection Pane in Arrange group. The Selection Pane appears on the right side of the window.

Selection Pane

As you can observe, the symbol Eye appears next to the objects in the Selection Pane. This means those objects are visible.

  • Click on Eye symbol next to NOC_CountryRegion.

  • Click on Eye symbol next to Sport. The Eye symbol is changed to Line for both. This means the visibility for the two Slicers is off.

Symbol

Close the Selection Pane.

Close Selection Pane

You can see that the two Slicers are not visible in the Report.



Advertisements