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.



What is the best training medium for EPM?

The training method is never a one size fits all solution.  End user adoption of enterprise software should be a high priority for your organization and it's important for IT departments to address the training requirements by role to deliver the most cost effective solution that will increase end user adoption. There are a number of options for driving awareness and education to end users but not all are effective or cost-effective and this can vary across roles. When thinking of end user education, you should evaluate just-in-time learning solutions as an effective tool that delivers affordable and practical training for end users while they work. Read more...


Why Project Server for Portfolio Management?

Microsoft's Enterprise Project Management (EPM) solution has grown to a complete end to end Enterprise Project and Portfolio management (EPPM) solution.  There are many advantages to combining active projects and proposed projects or ideas onto one system.  Especially when that system has analytic tools to help you advance the proposals or ideas that will best serve the business drivers to your organization and help evaluate whether you have the resources to complete those projects.



'All-in' on Project Online

By Paul Estabrooks, Agora Consulting.  We believe that Project Online offers our clients the best option for project management solutions going forward. More broadly, we believe that Office 365 is the way forward for nearly all organizations. Cloud computing’s momentum is undeniable.



Maximize your ROI from Project Online

With the onset of Project Online and the ease with which you can subscribe and start using this tool, it’s important that you don’t overlook the requirement to do what’s necessary to ensure you get a return investment for that commitment of funds. This also applies to the deployment on an on premise implementation of Project Server.



What's involved in supporting Project Server?

I'm often asked this question, so I decided it would be a worthwhile exercise to list the activities by their frequency to help put a framework around what's involved. The time required, of course, can vary widely depending upon the number of users and the complexity of the implementation. Read more...


Quick & Easy Status Reports in Project 2013

With Project 2013, you can create and customize graphical reports of whatever project data you want.  As an Administrator, you can distribute this report to all your Project Managers by adding the report to the Enterprise Global Template.