analysis and reporting with the excel 2010 client 03 creating excel 2010 powerpivot reports l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Analysis and Reporting with the Excel 2010 Client 03 – Creating Excel 2010 PowerPivot Reports PowerPoint Presentation
Download Presentation
Analysis and Reporting with the Excel 2010 Client 03 – Creating Excel 2010 PowerPivot Reports

Loading in 2 Seconds...

play fullscreen
1 / 26

Analysis and Reporting with the Excel 2010 Client 03 – Creating Excel 2010 PowerPivot Reports - PowerPoint PPT Presentation


  • 295 Views
  • Uploaded on

Analysis and Reporting with the Excel 2010 Client 03 – Creating Excel 2010 PowerPivot Reports . SQL10R2UPD02-DECK-03 [Presenter Name] [Presenter Title] [Company Name]. Module Overview. What’s New in the Excel 2010 Client Loading and Preparing Data in the Excel 2010 PowerPivot Client

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

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


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
analysis and reporting with the excel 2010 client 03 creating excel 2010 powerpivot reports

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

SQL10R2UPD02-DECK-03

[Presenter Name]

[Presenter Title]

[Company Name]

module overview
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
agenda
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
creating powerpivot reports
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

creating powerpivot reports continued
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
powerpivot field list
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

agenda8
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
designing pivottable reports
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
designing pivottable reports continued
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
agenda12
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
designing pivotchart reports
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
agenda15
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
working with slicers
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
agenda17
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
creating free form reports
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

agenda19
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
formatting reports
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
formatting reports continued
Formatting ReportsContinued
  • Reports can be further enhanced with native Excel features:
    • Conditional formatting
    • Slicers
    • Charts
    • Sparklines

For free-form reports

resources
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
slide26

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