Microsoft offers a myriad of BI Report authoring tools

Since the onset of BI Reporting for Project Server 2010 and 2013, Microsoft has continued to enhance the availability of desktop tools to help ‘non-programmers’ develop custom reports. If you’ve been hearing a lot of buzz about some of these tools, I’d like to help clear the fog on what the strengths are of each tool and provide some examples of Project Server reports that are easy to create.

As usual, there’s a crossover of capabilities but each tool does have its strengths and some tools are just easier to work with for certain reporting requirements. And if you happen to be an Excel guru, your skills will serve you well. In the next several paragraphs I’ll describe each tool and highlight one or two differentiating features of that tool along with a sample report.

Microsoft Excel 2010 or 2013

Building reports in Excel is all about Pivot tables and Pivot charts. So if you know those features well, you’ll be surprised how many great reports you can build using Excel which allows you to take advantage of all the rich formatting capabilities Excel has to offer. One particularly nice feature in Excel is the interactive slicers to filter your data and in 2013 there’s a Timeline slicer which is great for slicing your project data for a particular month, quarter or year.

The following is an example of an Excel pivot table and pivot chart to present milestones due in any given month across all projects and a chart of projects running late:

Excel Report

Excel Report

Microsoft PowerPivot for Excel 2010 & 2013

PowerPivot is a free Excel add-in that extends the pivot chart and pivot table capabilities of Excel by allowing you to add more complex formulas in your pivot tables than native Excel allows and you can create Key Performance Indicators (KPIs) mapped to a variety of graphical symbols. PowerPivot also allows you to build your own data model from multiple Project Server tables or views and link related tables. This makes it much easier to report on project information along with issue or risk logs all in the same report.

The following is an example of a PowerPivot table used to present a scorecard on project portfolios:

PowerPivot Report

PowerPivot Report


Microsoft PowerView for Excel 2013

PowerView is another free add-in for Excel 2013 which provides interactive charts and visualizations for your reports. The strength of this tool lies in the ability to quickly put together a report with multiple charts that are linked through the common data source so if you click on a section of one chart the others are filtered automatically for that data element.

The following is an example of a Power View report showing a visualization of outstanding issues by priority, by category and by project:

PowerView Report

PowerView Report

Microsoft PerformancePoint Dashboard Designer

SharePoint 2010 and 2013 both include the Dashboard Designer to build interactive dashboards with analytic charts and scorecards. The formatting options are not as robust as Excel, but with Performance Point reports you can drill down to get more detail using a decomposition tree. And if you don’t need the drill down capability, you can import Excel reports to include on dashboard pages.

PerformancePoint Dashboard

PerformancePoint Dashboard


Microsoft PowerQuery for Excel 2010 & 2013

PowerQuery is another free add-in for Excel 2013 this tool is used to create data models and has a vast number of features related to formatting your data, parsing data, changing field types, adding formulas, removing errors, etc. This is not a report creation tool but this can make the report creation much easier if you import your data using Power Query and apply filters and formats on the data to prepare it for the report. You can also combine data tables in your data model.

Microsoft Power BI

And on top of all the desktop tools, Microsoft has recently come out with their cloud based tool Power BI. With Power BI Desktop, you can connect to your Project Server database and build reports and dashboards that can be published back to the cloud. Power BI is much like Power View in that it’s designed to help you visually explore your data using a free-form drag-and-drop canvas and all the objects on the canvas can be filtered based on a single data element. There’s a broader range of data visualizations available and it’s a very easy-to-use report authoring experience.

The following is an example of a dashboard created using Power BI:

Power BI Dashboard

Power BI Dashboard

Keep in mind, regardless of the tool, the most challenging aspect of building custom reports is having an understanding of the data structures. When I teach BI Reporting for Project Server, I not only teach the tools, but I spend quite a bit of time teaching users the data structures they will need to know to build reports using the Project Server database.

To learn more about my BI Reporting course, follow this link.


Patricia Davidson, MBA, CMC, MCT
Microsoft Certified Technology Specialist in all versions of both Project Professional and Project Server
Avalon Training Group (a division of Agora Consulting)




Are your task updates protected?

If you’re requesting team members to report their hours worked on project tasks, the last thing you want is for Project Managers to inadvertently change the actual hours on a task that a team member submitted via Project Web App. Getting accurate data for reporting is challenging enough as it is, so why add another complication to this.

Whether you’re using Project Online or an on premise version of Project Server, you can protect actual hours submitted from Project Web App to Microsoft Project. On the Server Settings page, there’s a link to the Task Settings and Display page which contains an administrative setting ‘ONLY ALLOW TASK UPDATES VIA TASKS AND TIMESHEETS’. This option can be toggled on to protect actuals entered via Project Web App from team members (enterprise resources).

When you toggle this setting on, there’s an impact on how Microsoft Project 2013, and all earlier versions, will behave. The change in behavior is to protect Project Managers from inadvertently doing things that would impact the actual hours submitted by the team member from Project Web App. The following describes the specifics on how Project 2013 behaves.

If a Project Manager tries to delete a task or assignment, the following dialog box appears and the action is not allowed.

If a Project Manager tries to do any of the following in Microsoft Project on a task or assignment that has actual hours submitted by an enterprise resource:

  • change the % complete
  • change the % work complete on an assignment
  • change actual work
  • convert a detail task to a summary task by adding a new task below and indenting
  • change the start date

The following dialog appears and the action is not allowed.


If a Project Manager mistakenly assigns a resource to a summary task and actuals are entered against the summary, this doesn’t cause issues like it did in earlier versions. The time is accepted to the summary task and the work on that task will show the rollup of all the subtasks plus the hours entered against the summary task. A Project Manager will not be able to remove the resource from the summary task to prevent further time posted but they task can be closed from receiving further updates (this is done in Project Web App using the Project Details view 'Close Tasks to Update' which Microsoft doesn't conveniently display by default, so an administrator will need to include this view in the My Projects category)

If you recall the field ‘Actual Work Protected’ from Project Server 2003, this is no longer available for display in Microsoft Project Professional, but there is a field ACWP (Actual Cost of Work Performed) and if you’re trying to do some troubleshooting to see what the Actual Work Protected value is, you can open the resource sheet and set all resources to a rate of $1 (for enterprise resources, the value will hold for your working session and then reset back to the rate on their profile when you close and reopen the project file).



Who’s authoring your Business Intelligence reports?

Business Intelligence (BI) reporting provides a vehicle for analyzing data and presenting information to help corporate executives, business managers and users make more informed business decisions. Initially, BI tools were primarily used by data analysts and other IT professionals who ran analyses and produced reports with query results for business users. Increasingly, however, executives and managers are using BI reporting tools themselves, thanks partly to the development of self-service BI and simpler data authoring tools.