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.

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.

• 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.

The Slicer NOC_CountryRegion appears.

• Select USA.

• Drag Gender from Medals table to GENDER area.

• Right click on the PivotChart.

• Select Change Chart Type from the dropdown list.

The Change Chart Type dialog box appears.

Click on 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.

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.

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

• 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.

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

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

• Click on Subtotals.

• Select Do Not Show Subtotals fromn the dropdown list.

The Subtotals column – Aquatics Total gets removed.

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

Check the box Banded Rows.

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

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.

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.

• 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.

• 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.

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

• Select USA in the NOC_CountryRegion Slicer.

• Select Aquatics in the Sport Slicer. The PivotTable is filtered to the 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.

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.

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

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.

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.

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

• Click on symbol next to NOC_CountryRegion.

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

Close the Selection Pane.

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