Introduction
One of the most common requests from we get from executives is for the ability to view the entire portfolio of projects and how these projects progress through a fiscal year. The play axis feature in Power View solves this problem nicely with a time-phased, visual overview of the entire portfolio. By animating the progress of projects, executives can see what time of the year they should be paying extra attention to specific projects. The color-coding shows how projects align with departments, enterprise project types, project owner, etc. The size of the bubble can show total costs, total work or other pertinent information. As the animation plays, outlier projects in the portfolio will quickly become visible. Filters show sub-portfolios or draw attention to projects with health status of ‘Red’ or ‘Yellow’.
Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting. Power View is now available in Microsoft Excel 2013. Power View can interact with data in the same Excel workbook as the Power View sheet, in data models within PowerPivot, or tabular models within SQL Server 2012 Analysis Services (SSAS) instances.
Project Online allows for the use of the Open Data Protocol (OData) to query for data in your reports. You can also use OData for your Project Server 2013 instances on-premises or hosted. When you connect to an OData feed within Excel 2013, it will build the PowerPivot data model for you and you can use this to manipulate the data model if desired.
Solution
In this post, we will show how to set up a Power View report showing a bubble chart of projects that are time phased each month to show cost and work values using the play axis per month to show the changes visually over time. To see the end result, click on the screenshot below and watch the video of the animated report, then follow the instructions to build your own.
Initial Setup
The first step is you need to have Excel 2013 and you need to have Power View and PowerPivot enabled. If they are not enabled, go to File > Options > Add-Ins. From the Manage dropdown box, select COM Add-ins and select the Go button. From there you can select the checkboxes for “Power View” and “Microsoft Office PowerPivot for Excel 2013” and select OK. You will need to exit Excel and restart the application for these add-ins to load.
You will also need permissions to access these OData feeds to author and view this report. If you are using Project Server permissions mode, you will need the Allow permission for the global permission called “Access Project Server Reporting Service”. If you are running SharePoint Permissions mode, you will need to be a member of one of the following groups: Portfolio Viewers, Portfolio Managers, or Administrators.
If you are running Project Online, you will also need to enable Excel Web App to refresh the OData feeds in your report.
Setup Data Connections
In this example, we want to get assignments for each day but only for the year 2013. We are doing this mainly for performance as there could be hundreds of thousands of daily assignments and that will affect performance in obtaining and refreshing the data. Since we are filtering the data results we are going to need to set up unique data connections into the Excel 2013 workbook. The data sets we need are AssignmentTimephasedDataSet, Assignments, and Projects.
AssignmentTimephasedDataSet
The first data connection will be to the AssignmentTimephasedDataSet data set and we want to filter it to just days within 2013 and just select the columns we need for this report. The URL string path to use for the OData feed would look like below.
<URL to PWA>/_api/ProjectData/AssignmentTimephasedDataSet()?$filter=TimeByDay ge datetime’2013-01-01T00:00:00′ and TimeByDay lt datetime’2014-01-01T00:00:00’&$select=TimeByDay,AssignmentId,AssignmentWork,AssignmentCost
Note: Edit the <URL to PWA> section to match the URL to your PWA instance, such as
Once Power View and PowerPivot are enabled, launch Excel 2013, open and blank workbook and go to the Data tab and choose From Other Sources > From OData Data Feed. In the Location of the data feed section paste in your full URL for the AssignmentTimephasedDataSet, such as:
ge datetime’2013-01-01T00:00:00′ and TimeByDay lt datetime’2014-01-01T00:00:00’&$select=TimeByDay,AssignmentId,AssignmentWork,AssignmentCost
Keep the defaults and click Next and then click the checkbox to select the AssignmentTimephasedDataSet() table and click Next. For the File Name, change it to AssignmentTimephasedDataSet.odc and change the Friendly Name to AssignmentTimephasedDataSet.
Click Finish and then choose to Only Create Connection. Click OK.
You will notice in the bottom status bar that it is Retrieving Data and shows the number of rows processed. Wait for this to complete before proceeding. Since we limited the scope to only 2013 this should help with performance with this data collection.
You will now need to change the data source connection type so it isn’t using a locally stored Office Data Connection file. You can choose to export it to SharePoint or have it embedded in the report. I prefer to have it embedded in the report and strictly use SharePoint to store data connection files if they will be reused for multiple reports. To embed the query as a Data Feed you need to make any change to the connection string. Go to the Data tab and choose Connections and highlight the connection name and choose Properties, then select the Definition tab and go into the Connection String box. I’ve chosen to change the value of the Max Received Message Size and changed the number to have a “0” at the end instead of a “4”. Note the change highlighted in blue below. If you know of a better way to change the Connection Type easily, please provide an answer in the comments.
Click OK and you should get a message saying the connection is no longer identical and click Yes to break the connection.
Highlight the connection again and click Properties. In the Usage tab, check the box for “Refresh data when opening the file”.
Note: If you have a lot of assignments by day records, you might want to uncheck this and only save in Excel say each month and then have Excel Services just use that last saved cached values. Another option would be to get less data, say 6 months instead of a full year.
On the Definition tab, notice the connection type got changed to Data Feed when you broke the connection from the original office data file. This will keep the query embedded in the Excel file.
Assignments
Add another data connection for Assignments by repeating the same steps above but substitute the URL for:
<URL to PWA>/_api/ProjectData/Assignments()?$select=AssignmentId,ProjectId
Note: We are not using any filters on the Assignments. You could use Finish Date or Start Date filters if desired. We are however limiting the number of columns returned to decrease the size of the file transferred to help with performance.
Projects
Repeat the same steps above but substitute the URL for:
<URL to PWA>/_api/ProjectData/Projects
Note: We are not using any filters on Projects. You could use Finish Date, Start Date, or Percent Completed filters if desired. I also choose to return all columns to give some flexibility on which columns you can use on your report.
Data Model Changes in PowerPivot
Once all three data connections have been added to the workbook, you can manage the data model within PowerPivot. To do this, click on the POWERPIVOT tab and then click the Manage button form the ribbon.
You should notice each data set has a separate tab and it shows the data to you in rows and columns.
There are a few changes we want to make to the data model while we are here in PowerPivot. The first is let’s create a new Month column within the AssignmentTimephasedDataSet tab that shows it in the format of the first day of the month so it sorts correctly. To add the new Month column, go to the far right of the AssignmentTimephasedDataSet tab and double-click where it says “Add Column” and type in the word Month and press Enter. It should now highlight the entire column and then type in the formula bar the DAX expression of =[TimeByDay]-Day([TimeByDay])+1 and press Enter. Then with this column still highlighted, go to the Format section in the ribbon and choose the short date format (*3/14/2001). Ok, now we have a new column for Month that will look nicely in the play axis.
The other cosmetic things you can do are renaming columns by double-clicking on the column header. From the Projects dataset you can rename columns such as “EnterpriseProjectTypeName” to “Enterprise Project Type”, “ProjectName” to “Project name”, “ProjectOwnerName” to “Project Owner”, “ProjectHealth” to “Project Health” (you might not have this custom field in your environment), “ProjectCost” to “Total Project Cost”, and “ProjectDepartments” to “Project Departments”. In the AssignmentTimesphasedDataSet tab, I’ve also renamed “AssignmentCost” to “Project Cost” and “AssignmentWork” to “Project Work”.
The final thing we need to do with our data model is create relationships between these datasets. To do this, go to the Design tab within PowerPivot and click the Create Relationship button from the ribbon. Create the three relationships as follows:
- Table: AssignmentTimephasedDataSet Column: AssignmentId with Related Lookup Table: Assignments Related Lookup Column: AssignmentId
- Table: Assignments Column: ProjectId with Related Lookup Table: Projects Related Lookup Column: ProjectId
If you click on the Manage Relationships button it should look like this below. Click Close after verifying the information is the same.
You can now switch back to the workbook by clicking on the “Switch to Workbook” button in the top left corner.
Power View Report
To add a Power View report to your workbook, go to the Insert tab and click the Power View report button. It then opens up a new Power View sheet and shows the Power View Fields available from our data model. To start the design process, expand the Projects data set in the Power View Fields section and check Project name. Then expand the AssignmentTimephasedDataSet and check Project Cost and Project Work. You will notice Power View by defaults adds it as a table. You can also format the Project Cost to currency by clicking on one of the Project Cost values in the table and then selecting Currency in the dropdown from the Design tab. We want to change it to a bubble chart, which is a form of a scatter chart. To do this, from the Design tab click Other Chart and choose Scatter. Go ahead and expand the corners to make the chart take up the entire page width and most of the height (up to the title box). Click the title box and rename it to “2013 Project Work and Cost per Month”.
The other items we want to add to the report are to color the bubbles by Enterprise Project Type, add the Month to the play axis, and size the bubbles by total project cost. To do this, click on the chart to show the Power View Fields and then drag the Enterprise Project Type from the Projects data set to the Color section, the Month from the AssignmentTimephasedDataSet data set to the Play Axis, and the Total Project Cost from the Projects data set to Size.
The fields in your chart design should look like the following below.
Next, go to the Layout tab and choose Title > None to remove the top title from the chart.
The final step is to add some filters. Drag the following fields to the Filters section. All of these are from the Projects data set.
- Enterprise Project Type
- Project Departments
- Project Health (you might not have this custom field in your environment)
- Project Owner
Your report should look something like this now:
Press the Play button on the Month play axis to watch the report in action.
The final step is to delete Sheet1 or hide it and then save it to your Business Intelligence site within SharePoint 2013. The report will run and interact within the browser and the user will not need to have Excel 2013 to view it.
Conclusion
Using some new technologies in Excel 2013 such as Power View and PowerPivot shows new and exciting ways to expose your data visually in Project Online or Project Server 2013 on-premises installations.
About Sensei Project Solutions
Sensei Project Solutions is a Microsoft Partner specializing in Project and Portfolio Management (PPM) deployments with Microsoft Project and Project Server on the SharePoint platform. With extensive experience on hundreds of PPM deployments and with thousands of users trained, Sensei Project Solutions brings a process-focused approach; and support for industry standards and best practices to all engagements. We offer a complete set of services to help an organization make their Microsoft PPM deployment successful, including full implementation and support services, training, as well as pre-configured solutions and report packs. info@senseiprojectsolutions.com
