Creating simple budget KPIs from SharePoint Lists

In this post we're going to walk through how our favorite fictional company Litware uses PerformancePoint Services with SharePoint list data to do simple "under budget" KPIs.

In Litware's party planning committee we log how much we spend on events using a SharePoint list. Corporate's creating a corporate balanced scorecard using PerformancePoint Services, and they've asked us to contribute a KPI showing how we're doing. For now our KPI will be based on the data we have, and the rules will be >100% budget is warning, >120% budget is bad. Later we can swap in other sources of information, for example employee anticipation around the next golf scramble, into the KPI's definition to make it a more accurate or more predictive metric, and the changes will flow into the higher level scorecard that uses the KPI.

Here's a sample of our spending history list. Let's create the KPI and scorecard from this point.

I launch Dashboard Designer from the Business Intelligence site template homepage. First I create a new data connection using the SharePoint List template:

After the data source appears in Workspace Browser I give it a name and point it towards the specific list in the site:

Very important: based on the settings above, the unattended service account for PerformancePoint Services must have access to the list. I can find out if it does by clicking "Test Data Source."

I'm done with the data source, so I save the data source to SharePoint:

Now we're ready to build the KPI and a test scorecard. I create a scorecard in PerformancePoint Content using the SharePoint List template; this will create both our KPI and the test scorecard:

Next I select the data connection we created. Then on the next screen I add a KPI, select the columns representing Actual and Target, and select "Closer to Target is Better" for some flexibility later:

I click "Next" through the rest of the wizard to get a KPI and scorecard added to the workspace, and I see what I have so far as the scorecard updates. The exact rules haven't been set yet (remember we're after >100% budget is warning, >120% budget is bad) and I'm going to get a stern warning if I format dollars with this precision:

To change these I need to edit the KPI. I select it in the Workspace Browser, and then change the number format and thresholds:

That's better!

Although corporate probably won't care about this detail when they integrate the KPI into their scorecard, I can break the number down by the individual event titles, or any other column in the list, by dragging the field into the scorecard.

Tip: I use the "Named Sets" instead of "Dimensions" so that new rows in the SharePoint list will automatically show up in the scorecard:

After I click Update in the Edit tab of the ribbon, here's the result:

I save both the scorecard and KPI in the workspace, and now I can put it into SharePoint using the PerformancePoint Services Scorecard webpart. I think I'll touch up the test scorecard for use by our team, and I’ll tell corporate my work here is done; they can find the KPI for broader use at https://intranet.litware.com/corp/hr/partyplanning/Lists/PerformancePoint%20Content/

Wade Dorrell

Program Manager, Office BI

Comments

• Anonymous
  January 01, 2003
  The comment has been removed