Creating Excel Reports for Microsoft Project Server

Private Group Training | 8 Hours
This one-day course teaches students how to use Microsoft Excel to create powerful reports using the data from Microsoft Project Server.

The course begins with students connecting to the Microsoft Project Server OData feeds to gather the data, then create relationships between the selected tables. Next Power Pivot and Power Query will be explored, which provide the ability to manipulate the data contained within with tables by creating new columns via queries. Then, students will use a PivotTable and its options to begin building a report and laying data out on a worksheet. Filtering mechanisms such as slicers will also be introduced, which provide users the ability to isolate specific subsets of information. And finally, publishing and refresh options will be reviewed.

Through lecture, demonstrations, and hands-on exercises, you’ll learn the skills and techniques necessary to immediately create and deploy reports upon conclusion of this training course.

Target Audience

Project managers, report developers, and anyone else who needs to create, deploy and maintain reports using Microsoft Excel and Microsoft Project Server.

Provided with the Course

An electronic copy of our detailed exercise booklet, as well as access to a fully configured training environment for the duration of the class.

What You’ll Learn:

MORE INFORMATION

Excel Introduction

  • Why use Excel for Reporting?
  • Excel Terminology

Connect to OData

  • Using Get Data
  • Adding Additional Tables
  • Project Online Table Structure
  • Commonly Used Table
  • Tasks table work vs. Assignments table work
  • Timephased Data
  • Table Relationships
  • Creating relationships between tables
  • Primary and Foreign Keys

Power Pivot

  • Understanding Power Pivot Ribbons and Commands
  • Power Pivot Interface
  • Viewing Data using Power Pivot
  • Manipulating Data
  • Creating new columns

Power Query

  • Connect
  • Transform
  • Query Editor
  • Go to Column
  • Filtering
  • Replacing values

PivotTable

  • Create a PivotTable
  • Building out your PivotTable
  • Formatting Reports

PivotTable Options

  • Layout
  • Repeating Item Labels
  • Displaying Totals
  • Items with No Data
  • Styles

Slicers

  • Inserting a Slicer
  • Slicer Settings
  • Using Slicers
  • Formatting Slicers

Sharing the Report

  • Publishing
  • Refresh
  • Refresh Options
  • AutoSave

Exercises

  • Exercise – Deliverables Report
  • Exercise – Resources on Projects
  • Exercise – Assignment Work on Projects