Excel Power Pivot - Aesthetic Reports
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.
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.
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.
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.
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.
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.
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.
As you can observe, the symbol 
appears next to the objects in the Selection Pane. This means those objects are visible.
Close the Selection Pane.
You can see that the two Slicers are not visible in the Report.
for both. This means the visibility for the two Slicers is off.