So recently I started working on a new DashBoard project in SharePoint 2010. One of the requirements was to have KPI’s with trends driven of data stored in SharePoint lists.
While SharePoint provides some nice of the box KPI’s for data stored in Lists, it is not possible to create complex trend based metrics. fortunately PerformancePoint enables you to create KPI’s using SharePoint List data.
Below is an example of how this can be accomplished. This post assumes you have access to the enterprise license and have PerformancePoint and publishing features enabled, plus the PerformancePoint Service application has been created and setup.
- The first step is the create the lists with the required columns and some data. In order to have a trend like metric this means you will need two lists – one for the current data and one with older data to trend against.
- Next you need to have a Business Intelligence site created – so you can get to the DashBoard designer for PerformancePoint – I’ve not found a way yet to start this app from outside of this site. UPDATE: You can get to the designer from the Start Menu. It’s in the SharePoint folder. A BI site is still has to be created so there is a place to store all the PerformancePoint Content created by the designer.
- When the designer is running the following steps are required:
- Create a new Data Connection, selecting a Tabular List and the SharePoint List template:
- Next you have provide the Url of the site containing your list, select “All Lists” and then the list name itself. NOTE: PerformancePoint will only pull data from the default (All Items) view of the list. Any data filtered on that view will not be available.
- Next select the “Properites” tab – give you new dataconnection a meaniful name!
- Now go to the “View” tab. Here you will see all of the available columns part of the view of the list. By default all non date fields are Fact Columns and all dates fields are Time Dimension. Data in the fact type columns are by default summed together; you can change this by selecting a new value from the Aggregation dropdown box to the right, to eg. Average.
- Save you chnages – click to little save button on the top left of the application – and you have your first SharePoint List Data Connection.
In my next post I will cover creating a KPI from this Data Connection.