Project Server Business Intelligence Miscellanea

Introduction

Some miscellaneous thoughts and findings while exploring the business intelligence functionality of Project Server 2013

Excel 2013

In Excel 2010 it was possible to format a series in a chart separately from the other series to create a mixed column and line chart. This option to format each series separately has been removed from Excel 2013. However, it is still possible to replicate this effect by changing the whole chart type to Combo.

To change to a Combo chart type:

1. Select your chart
2. Click on the Design tab and then click on the Change Chart Type button

   

3. This will open the Chart Types dialogue box. At the bottom of the list of options on the left hand side, click on Combo to select it.

   

4. For each series in the chart, use the drop down lists to specify the chart type to use.
5. Check the box on the right to display a secondary axis (for example if there is a big difference between the values for different series).
6. Click OK

 

Creating Reports that use different data sources

I would like to create a report, using excel services that shows top level information about projects within selected programmes. To that end I create a report using the OlapPortfolioAnalyzer template and slice Baseline Work, Actual Work and Remaining Work by Programme and Project. I have also added slicers to enable me to filter by Year and Quarter if required. This works fine.

I can also create a data connection using oData to connect to the Project Web App database directly and pull back data related to calculated project fields, such as RAG indicators. I can add a slice to this to also filter by Programme.

There is a slicer in each case that enables the user to filter by the project level custom field: Programme.

However, presumably because one set of data comes from the OLAP cube and one set comes from oData I have not been able to find a way to link them together, so that both charts can be updated using the same slicer.

I need to investigate whether I can combine the data sources using PowerPivot.