通过

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.

clip_image001

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

clip_image002

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

clip_image003

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:

clip_image004

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:

clip_image005

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:

clip_image007

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:

clip_image008

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

clip_image010

That's better!

clip_image011

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:

clip_image012

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

clip_image013

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