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
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
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
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
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
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)