Analysis and Reporting with the Excel 2010 Client 03 – Creating Excel 2010 PowerPivot Reports

1. Analysis and Reporting with the Excel 2010 Client 03 – Creating Excel 2010 PowerPivot Reports SQL10R2UPD02-DECK-03 [Presenter Name] [Presenter Title] [Company Name]

2. Module Overview • What’s New in the Excel 2010 Client • Loading and Preparing Data in the Excel 2010 PowerPivot Client • Creating Excel 2010 PowerPivot Reports • Defining DAX Calculations with Excel 2010 PowerPivot

3. Agenda • Creating PowerPivot Reports • Designing PivotTable Reports • Designing PivotChart Reports • Working with Slicers • Creating Free-Form Reports • Formatting Reports • Demo: Creating Excel 2010 PowerPivot Reports • Lab: Creating Excel 2010 PowerPivot Reports

4. PowerPivot Workflow

5. Creating PowerPivot Reports • Reports based on PowerPivot models can be produced with: • PivotTables • PivotCharts • CUBE functions • PowerPivot models can be embedded in the workbook or can be accessed via a data connection to a PowerPivot workbook published in SharePoint In this topic, we will produce reports only from embedded PowerPivot models

6. Creating PowerPivot ReportsContinued • PowerPivot, PivotTable, or PivotChart reports can be created from: • Within the PowerPivot Window, or • On the Excel PowerPivot ribbon tab • Combinations of tables and charts are supported • Data in multiple tables and charts are independent, but slicers will apply to both • The Flattened PivotTable is configured to show in tabular form and to repeat all item labels

7. PowerPivot Field List • Use the PowerPivot Field List to: • Search for tables and columns in the PowerPivot model • Define slicers • Design the report layout • Create and modify measures • Read messages and notifications • Model updated • Relationship needed Measures will be covered in Topic 04

8. Agenda • Creating PowerPivot Reports • Designing PivotTable Reports • Designing PivotChart Reports • Working with Slicers • Creating Free-Form Reports • Formatting Reports • Demo: Creating Excel 2010 PowerPivot Reports • Lab: Creating Excel 2010 PowerPivot Reports

9. Designing PivotTable Reports • Selecting numeric fields will add them to the Values drop zone using the Sum aggregate function • All numeric fields added to the Values drop zone become measures • The aggregate function can be modified to use Count, Min, Max, or Average • Selecting non-numeric fields will add them to the Row Labels drop zone • Any field can be placed in any drop zone: • Vertical and Horizontal Slicers • Report Filter • Column and Row Labels • Values

10. Designing PivotTable ReportsContinued • If they are related, multiple fields in the Slicer and Column and Row Label drop zones should be sequenced in the one-to-many order to produce a drilldown path • Note: PowerPivot models do not support multi-level or parent-child hierarchies

11. Designing PivotTable ReportsExample

12. Agenda • Creating PowerPivot Reports • Designing PivotTable Reports • Designing PivotChart Reports • Working with Slicers • Creating Free-Form Reports • Formatting Reports • Demo: Creating Excel 2010 PowerPivot Reports • Lab: Creating Excel 2010 PowerPivot Reports

13. Designing PivotChart Reports • Numerous chart types are available • Two drop zones change name and behavior: • Column Labels becomes Legend Fields • Row Labels becomes Axis Fields

14. Designing PivotChart ReportsExample

15. Agenda • Creating PowerPivot Reports • Designing PivotTable Reports • Designing PivotChart Reports • Working with Slicers • Creating Free-Form Reports • Formatting Reports • Demo: Creating Excel 2010 PowerPivot Reports • Lab: Creating Excel 2010 PowerPivot Reports

16. Working with Slicers • Slicers are introduced by dropping fields into the Horizontal and Vertical Slicer drop zones • PowerPivot adds slicer zones and arranges the layout of the slicers automatically • Slicers highlight available members in related slicers • For example, the selection of a category will highlight related members in the subcategory slicer • By default, they will appear first in the list

17. Agenda • Creating PowerPivot Reports • Designing PivotTable Reports • Designing PivotChart Reports • Working with Slicers • Creating Free-Form Reports • Formatting Reports • Demo: Creating Excel 2010 PowerPivot Reports • Lab: Creating Excel 2010 PowerPivot Reports

18. Creating Free-Form Reports • Five of the seven Excel CUBE functions deliver free-form reporting with PowerPivot models: • CUBEMEMBER • CUBEVALUE • CUBESET • CUBESETCOUNT • CUBERANKEDMEMBER • The remaining two CUBE functions are not relevant to the PowerPivot model • CUBEMEMBERPROPERTY, CUBEKPIMEMBER • The PowerPivot data source is named “PowerPivot Data” Tip: Convert a PivotTable to free-form by using the 'Convert to Formulas' command

19. Agenda • Creating PowerPivot Reports • Designing PivotTable Reports • Designing PivotChart Reports • Working with Slicers • Creating Free-Form Reports • Formatting Reports • Demo: Creating Excel 2010 PowerPivot Reports • Lab: Creating Excel 2010 PowerPivot Reports

20. PowerPivot Workflow

21. Formatting Reports • PowerPivot PivotTables and PivotCharts can be formatted using the regular styles and design options • Values can be formatted using the Value Field Settings window

22. Formatting ReportsContinued • Reports can be further enhanced with native Excel features: • Conditional formatting • Slicers • Charts • Sparklines For free-form reports

23. Creating Excel 2010 PowerPivot Reports SQL10R2UPD02-DEMO-03 Demo

24. Creating Excel 2010 PowerPivot Reports SQL10R2UPD02-HOL-02 Lab

25. Resources • Microsoft PowerPivot Website • http://www.powerpivot.com • PowerPivot and DAX Information Hub • http://www.powerpivot-info.com • PowerPivot Product Team Blog • http://blogs.msdn.com/powerpivot

26. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.