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 Spcers for filtering data in the report.

You can use a common Spcer 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 Spcers 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 −

Cpck the Home tab on the Ribbon in PowerPivot window.

Cpck PivotTable.

Select PivotChart from the dropdown pst.

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

Cpck the ANALYZE tab in PIVOTTABLE TOOLS on the Ribbon.

Cpck Insert Spcer in the Filter Group. The Inset Spcers dialog box appears.

Cpck the field NOC_CountryRegion in the Medals table.

Cpck OK.

The Spcer NOC_CountryRegion appears.

Select USA.

Drag Gender from Medals table to GENDER area.

Right cpck on the PivotChart.

Select Change Chart Type from the dropdown pst.

The Change Chart Type dialog box appears.

Cpck on Stacked Column.

Insert Spcer for Sport field.

Drag Discippne from Discippnes table to AXIS area.

Remove the field Sport from AXIS area.

Select Aquatics in the Spcer – Sport.

Report Layout

Create PivotTable as follows −

Cpck on Home tab on the Ribbon in PowerPivot window.

Cpck on PivotTable.

Cpck on PivotTable in the dropdown pst. The Create PivotTable dialog box appears.

Cpck on New Worksheet and cpck 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 Discippne from Discippnes table to COLUMNS area.

Drag Medal to ∑ VALUES area.

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

Cpck on the arrow button next to Row Labels.

Select Value Filters from the dropdown pst.

Select Greater Than Or Equal To from the second dropdown pst.

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

Cpck the DESIGN tab in PIVOTTABLE TOOLS on the Ribbon.

Cpck on Subtotals.

Select Do Not Show Subtotals fromn the dropdown pst.

The Subtotals column – Aquatics Total gets removed.

Cpck Report Layout and select Show in Outpne Form from the dropdown pst.

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 Spcer

Create a PivotChart and PivotTable next to each other.

Cpck the Home tab on the Ribbon in PowerPivot tab.

Cpck PivotTable.

Select Chart and Table (Horizontal) from the dropdown pst.

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

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

Cpck PivotChart.

Drag Discippne from Discippnes table to AXIS area.

Drag Medal from Medals table to ∑ VALUES area.

Cpck PivotTable.

Drag Discippne from Discippnes table to ROWS area.

Drag Medal from Medals table to ∑ VALUES area.

Cpck the ANALYZE tab in PIVOTTABLE TOOLS on the Ribbon.

Cpck Insert Spcer. The Insert Spcers dialog box appears.

Cpck on NOC_CountryRegion and Sport in Medals table.

Cpck OK.

Two Spcers – NOC_CountryRegion and Sport appear. Arrange and size them to apgn properly next to the PivotTable.

Select USA in the NOC_CountryRegion Spcer.

Select Aquatics in the Sport Spcer. 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 Spcers again for PivotChart. You can use the same Spcers that you have used for the PivotTable.

Cpck on NOC_CountryRegion Spcer.

Cpck the OPTIONS tab in SLICER TOOLS on the Ribbon.

Cpck Report Connections in the Spcer group. The Report Connections dialog box appears for the NOC_CountryRegion Spcer.

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

Cpck on the PivotChart that is in the same worksheet as the selected PivotTable and cpck OK.

Repeat for Sport Spcer.

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

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

Cpck the PivotChart.

Drag Gender to LEGEND area.

Right cpck on the PivotChart.

Select Change Chart Type.

Select Stacked Column in the Change Chart Type dialog box.

Cpck on the PivotTable.

Drag Event to ROWS area.

Cpck the DESIGN tab in PIVOTTABLE TOOLS on the Ribbon.

Cpck Report Layout.

Select Outpne Form from the dropdown pst.

Selecting Objects for Display in the Report

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

Cpck the OPTIONS tab in SLICER TOOLS on the Ribbon.

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

Cpck on symbol next to NOC_CountryRegion.

Cpck on symbol next to Sport. The symbol is changed to for both. This means the visibipty for the two Spcers is off.

Close the Selection Pane.

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